Oracle 分区索引介绍和实例演示

分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长,普通的堆表需要转换到分区表,其索引呢,则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断,在获取所需数据时,只需要访问更小的索引片断(块)即可实现。同时把分区放在不同的表空间可以提高分区的可用性和可靠性。本文主要描述了分区索引的相关特性并给出演示示例。

1、分区索引的相关概念

a、分区索引的几种方式:表被分区而索引未被分区;表未被分区,而索引被分区;表和索引都被分区
b、分区索引可以分为本地分区索引以及全局分区索引

本地分区索引:

本地分区索引信息的存放依赖于父表分区。也就是说对于本地索引一定是基于分区表创建的。
   缺省情况下,创建本地索引时,如未指定索引存放表空间,会自动将本地索引存放到数据所在分区定义时的表空间。
   本地索引的分区机制和表的分区机制一样,本地索引可以是是B树索引或位图索引。
   本地索引是对单个分区的,每个分区索引只指向一个表分区,为对等分区。
   本地索引支持分区独立性,因此对于这些单独的分区增加,截取,删除,分割,脱机等处理无需同时删除或重建。
   本地索引多应用于数据仓库环境中。
     
全局分区索引:

全局分区索引时分区表和全局索引的分区机制不一样,在创建时必须定义分区键的范围和值。
   全局分区索引在创建时应指定Global关键字且全局分区索引只能是B树索引。
   全局索引可以分区,也可以是不分区索引,全局索引必须是前缀索引,即索引列必须包含分区键。
   全局索引分区中,一个分区索引能指向n个表分区,同时,一个表分区,也可能指向n个索引分区。
   默认情况下全局索引对于分区增加,截取,删除,分割等都必须重建或修改时指定update global indexs。
   全局分区索引只按范围或者散列hash分区。
   全局分区索引多应用于oltp系统中。
 
c、有前缀索引和无前缀索引

本地和全局分区索引又分为两个子类型即有前缀索引和无前缀索引。
前缀和非前缀索引都可以支持索引分区消除,前提是查询的条件中包含索引分区键。
有前缀索引:
   有前缀索引包含了分区键,即分区键列被包含在索引中。
   有前缀索引支持本地分区索引以及全局分区索引。
无前缀索引:
   无前缀索引即没有把分区键的前导列作为索引的前导列。
   无前缀索引仅仅支持本地分区索引。

2、本地分区索引演示

代码如下:

--环境
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

SQL> create user leshami identified by xxx;

SQL> grant dba to leshami;

--创建演示需要用到的表空间
SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;

SQL> alter user leshami default tablespace tbs_tmp;

SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;

SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;

SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;

SQL> create tablespace idx1 datafile '/u02/database/SYBO2/oradata/idx1.dbf' size 10m autoextend on;

SQL> create tablespace idx2 datafile '/u02/database/SYBO2/oradata/idx2.dbf' size 10m autoextend on;

SQL> create tablespace idx3 datafile '/u02/database/SYBO2/oradata/idx3.dbf' size 10m autoextend on;

SQL> conn leshami/xxx

-- 创建一个lookup表
CREATE TABLE lookup (
  id            NUMBER(10),
  description   VARCHAR2(50)
);

--添加主键约束
ALTER TABLE lookup ADD (
  CONSTRAINT lookup_pk PRIMARY KEY (id)
);

--插入数据
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;

CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) tablespace tbs1,
 PARTITION big_table_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) tablespace tbs2,
 PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE)tablespace tbs3 ) ;
 
--填充数据到分区表
DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 10000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;
   
    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/

--未指定索引分区及存储表空间情形下创建索引
SQL> CREATE INDEX bita_created_date_i ON big_table(created_date) LOCAL;

Index created.

SQL> select index_name, partitioning_type, partition_count from user_part_indexes;

INDEX_NAME                     PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
BITA_CREATED_DATE_I            RANGE                 3

--Author : Leshami

--从下面的查询可知,索引直接存放到分表表对应的表空间
SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
BIG_TABLE_2014                 MAXVALUE                                 TBS3
BIG_TABLE_2013                 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M TBS2
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

BIG_TABLE_2012                 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M TBS1
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--删除索引
SQL> drop index bita_created_date_i;

--指定索引分区名表空间名创建索引
SQL> CREATE INDEX bita_created_date_i
  2     ON big_table (created_date)
  3     LOCAL (
  4        PARTITION idx_2012 TABLESPACE idx1,
  5        PARTITION idx_2013 TABLESPACE idx2,
  6        PARTITION idx_2014 TABLESPACE idx3)
  7     PARALLEL 3;

Index created.

SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
IDX_2014                       MAXVALUE                                 IDX3
IDX_2013                       TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M IDX2
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

IDX_2012                       TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M IDX1
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select * from big_table where rownum<2;

ID CREATED_  LOOKUP_ID DATA
---------- -------- ---------- --------------------------------------------------
      1413 20120625          2 This is some data for 1413

--查看local index是否被使用,从下面的执行计划中可知,索引被使用,支持分区消除     
SQL> set autot trace exp;
SQL> select * from big_table where created_date=to_date('20120625','yyyymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 2556877094

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |     1 |    41 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                     |     1 |    41 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE           |     1 |    41 |     2   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | BITA_CREATED_DATE_I |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------------------

3、全局分区索引演示

代码如下:

--为表添加主键
SQL> ALTER TABLE big_table ADD (
  2    CONSTRAINT big_table_pk PRIMARY KEY (id)
  3  );

Table altered.

SQL> select index_name,index_type,tablespace_name,global_stats,partitioned
  2  from user_indexes where index_name='BIG_TABLE_PK';

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                GLO PAR
------------------------------ --------------------------- ------------------------------ --- ---
BIG_TABLE_PK                   NORMAL                      TBS_TMP                        YES NO

SQL> set autot trace exp;                                                                                         
SQL> select * from big_table where id=1412;                                                                       
                                                                                                                  
Execution Plan                                                                                                    
----------------------------------------------------------                                                        
Plan hash value: 2662411593                                                                                       
                                                                                                                  
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |     1 |    62 |     2   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE    |     1 |    62 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                | BIG_TABLE_PK |     1 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------
--如上,在其执行计划中,Pstart与Pstop都为ROWID
--出现了GLOBAL INDEX ROWID,我们添加主键时并未指定Global,但其执行计划表明执行了全局索引访问
--这个地方有待证实,对于分区表,非分区键上的主键或唯一索引是否一定是全局索引

SQL> drop index bita_created_date_i;

--下面创建全局索引,创建时需要指定分区键的范围和值
SQL> CREATE INDEX bita_created_date_i
   ON big_table (created_date)
   GLOBAL PARTITION BY RANGE (created_date)
      (
         PARTITION
            idx_1 VALUES LESS THAN (TO_DATE ('01/01/2013', 'DD/MM/YYYY'))
            TABLESPACE idx1,
         PARTITION
            idx_2 VALUES LESS THAN (TO_DATE ('01/01/2014', 'DD/MM/YYYY'))
            TABLESPACE idx2,
         PARTITION idx_3 VALUES LESS THAN (maxvalue) TABLESPACE idx3);

SQL> select index_name, partitioning_type, partition_count,locality from user_part_indexes;

INDEX_NAME                     PARTITI PARTITION_COUNT LOCALI
------------------------------ ------- --------------- ------
BITA_CREATED_DATE_I_G          RANGE                 3 GLOBAL

SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME                 HIGH_VALUE             TABLESPACE_NAME
------------------------------ --------------------- ------------------------------
IDX_1                          TO_DATE(' 2013-01-01  IDX1
IDX_2                          TO_DATE(' 2014-01-01  IDX2
IDX_3                          MAXVALUE              IDX3

--下面是其执行计划,可以看出支持分区消除
SQL> set autot trace exp;
SQL> select * from big_table where created_date=to_date('20130625','yyyymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 1378264218

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     1 |    41 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |                     |     1 |    41 |     2   (0)| 00:00:01 |     2 |     2 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE           |     1 |    41 |     2   (0)| 00:00:01 |     2 |     2 |
|*  3 |    INDEX RANGE SCAN                 | BITA_CREATED_DATE_I |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------------------------------

--以下为范围查询,Pstart为1,Pstop为2,同样支持分区消除
SQL> select * from big_table                                                              
  2  where created_date>=to_date('20120625','yyyymmdd') and created_date<=to_date('20130625','yyyymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 213633793

------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |  3334 |   133K|    14   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|           |  3334 |   133K|    14   (0)| 00:00:01 |     1 |     2 |
|*  2 |   TABLE ACCESS FULL      | BIG_TABLE |  3334 |   133K|    14   (0)| 00:00:01 |     1 |     2 |
------------------------------------------------------------------------------------------------------

(0)

相关推荐

  • 深入oracle分区索引的详解

    表可以按range.hash.list分区,表分区后,其上的索引和普通表上的索引有所不同,oracle对于分区表上的索引分为2类,即局部索引和全局索引,下面分别对这2种索引的特点和局限性做个总结.局部索引local index1.局部索引一定是分区索引,分区键等同于表的分区键,分区数等同于表的分区数,一句话,局部索引的分区机制和表的分区机制一样.2.如果局部索引的索引列以分区键开头,则称为前缀局部索引.3.如果局部索引的列不是以分区键开头,或者不包含分区键列,则称为非前缀索引.4.局部索引只能依

  • Oracle 分区索引介绍和实例演示

    分区索引(或索引分区)主要是针对分区表而言的.随着数据量的不断增长,普通的堆表需要转换到分区表,其索引呢,则对应的转换到分区索引.分区索引的好处是显而易见的.就是简单地把一个索引分成多个片断,在获取所需数据时,只需要访问更小的索引片断(块)即可实现.同时把分区放在不同的表空间可以提高分区的可用性和可靠性.本文主要描述了分区索引的相关特性并给出演示示例. 1.分区索引的相关概念 a.分区索引的几种方式:表被分区而索引未被分区:表未被分区,而索引被分区:表和索引都被分区 b.分区索引可以分为本地分区

  • oracle分区索引的失效和重建代码示例

    上一篇文章中我们了解了oracle普通表转化为分区表的方法的相关内容,接下来的这篇文章,我们将探讨oracle分区索引的失效和重建问题,提供了相关代码示例供大家参考,具体如下. --创建测试表 SQL> create table t as select object_id,object_name from dba_objects; 表已创建. SQL> select min(object_id),max(object_id) from t; MIN(OBJECT_ID) MAX(OBJECT_

  • Oracle RAC环境下的阻塞(blocking blocked)介绍和实例演示

    RAC环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session.也就是说之前查询的v$session,v$lock相应的应变化为全局范围来查找.本文提供了2个查询脚本,并给出实例演示那些session为阻塞者,哪些为被阻塞者.有关阻塞的概念以及单实例环境下的阻塞请参考:Oracle 阻塞(blocking blocked) 1.演示环境 scott@DEVDB> select * from v$version where rownum<2; BANNER ---------

  • Oracle 阻塞(blocking blocked)介绍和实例演示

    阻塞是DBA经常碰到的情形,尤其是不良的应用程序设计的阻塞将导致性能严重下降直至数据库崩溃.对DBA而言,有必要知道如何定位到当前系统有哪些阻塞,到底谁是阻塞者,谁是被阻塞者.本文对此给出了描述并做了相关演示. 1.阻塞及其类型 a.什么是阻塞    一个会话持有某个资源的锁,而另一个会话在请求这个资源,就会出现阻塞(blocking).也就是说新的会话会被挂起,直到持有锁的会话放弃锁定的资源.大多数情况下,在一个交互式应用中被严重阻塞,即可表明应用逻辑有问题,这才是阻塞的根源. b.阻塞得类型

  • MySql分表、分库、分片和分区知识点介绍

    一.前言 数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以

  • MyBatis配置文件解析与MyBatis实例演示

    MyBatis介绍 MyBatis是一个持久层的ORM框架,使用简单,学习成本较低.可以执行自己手写的SQL语句,比较灵活.但是MyBatis的自动化程度不高,移植性也不高,有时从一个数据库迁移到另外一个数据库的时候需要自己修改配置,所以称只为半自动ORM框架 传统JDBC和Mybatis相比的弊病 传统JDBC @Test public void test() throws SQLException { Connection conn=null; PreparedStatement pstmt

  • oracle横向纵向求和代码实例

    有一张工资表SALARY如下, (NO 员工编号 ,MONEY 工资) NO    NAME     ITEM       MONEY 001    张三        工资        80 001    张三        补贴        86 001    张三        奖金        75 002    李四        工资        78 002    李四        补贴        85 002    李四        奖金        78 求每

  • Linux下重启oracle服务及监听器和实例详解

    一.在Linux下重启Oracle数据库及监听器: 方法1: 用root以ssh登录到linux,打开终端输入以下命令: cd $ORACLE_HOME #进入到oracle的安装目录 dbstart #重启服务器 lsnrctl start #重启监听器 cd $ORACLE_HOME #进入到oracle的安装目录 dbstart #重启服务器 lsnrctl start #重启监听器 ----------------------------------- 方法2: Sql代码 cd $OR

  • JS图片轮播与索引变色功能实例详解

    废话不多说了,直接给大家贴代码了,具体代码如下所示: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <met

随机推荐