Oracle 分区索引

3121321321

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



1、分区索引的相关概念
a、分区索引的几种方式：表被分区而索引未被分区；表未被分区，而索引被分区；表和索引都被分区
b、分区索引可以分为本地分区索引以及全局分区索引
本地分区索引：
   本地分区索引信息的存放依赖于父表分区。也就是说对于本地索引一定是基于分区表创建的。
   缺省情况下，创建本地索引时，如未指定索引存放表空间，会自动将本地索引存放到数据所在分区定义时的表空间。
   本地索引的分区机制和表的分区机制一样，本地索引可以是是B树索引或位图索引。
   本地索引是对单个分区的，每个分区索引只指向一个表分区，为对等分区。
   本地索引支持分区独立性，因此对于这些单独的分区增加，截取，删除，分割，脱机等处理无需同时删除或重建。
   本地索引多应用于数据仓库环境中。
      
全局分区索引：
   全局分区索引时分区表和全局索引的分区机制不一样，在创建时必须定义分区键的范围和值。
   全局分区索引在创建时应指定Global关键字且全局分区索引只能是B树索引。
   全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即索引列必须包含分区键。
   全局索引分区中，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区。
   默认情况下全局索引对于分区增加，截取，删除，分割等都必须重建或修改时指定update global indexs。
   全局分区索引只按范围或者散列hash分区。
   全局分区索引多应用于oltp系统中。
  
c、有前缀索引和无前缀索引
本地和全局分区索引又分为两个子类型即有前缀索引和无前缀索引。
前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
有前缀索引:
   有前缀索引包含了分区键，即分区键列被包含在索引中。
   有前缀索引支持本地分区索引以及全局分区索引。

无前缀索引：
   无前缀索引即没有把分区键的前导列作为索引的前导列。
   无前缀索引仅仅支持本地分区索引。  



2、本地分区索引演示
在CODE上查看代码片派生到我的代码片

    --环境  
    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  
    --Blog   : http://blog.iyunv.com/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、全局分区索引演示
在CODE上查看代码片派生到我的代码片

    --为表添加主键  
    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 |  
    ------------------------------------------------------------------------------------------------------