Oracle 分区索引

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

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

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

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

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

2、本地分区索引演示

  1. –环境  
  2. SQL> select * from v$version where rownum<2;  
  3.   
  4. BANNER  
  5. —————————————————————-  
  6. Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bi  
  7.   
  8. SQL> create user leshami identified by xxx;  
  9.   
  10. SQL> grant dba to leshami;  
  11.   
  12. –创建演示需要用到的表空间  
  13. SQL> create tablespace tbs_tmp datafile ‘/u02/database/SYBO2/oradata/tbs_tmp.dbf’ size 10m autoextend on;  
  14.   
  15. SQL> alter user leshami default tablespace tbs_tmp;  
  16.   
  17. SQL> create tablespace tbs1 datafile ‘/u02/database/SYBO2/oradata/tbs1.dbf’ size 10m autoextend on;  
  18.   
  19. SQL> create tablespace tbs2 datafile ‘/u02/database/SYBO2/oradata/tbs2.dbf’ size 10m autoextend on;  
  20.   
  21. SQL> create tablespace tbs3 datafile ‘/u02/database/SYBO2/oradata/tbs3.dbf’ size 10m autoextend on;  
  22.   
  23. SQL> create tablespace idx1 datafile ‘/u02/database/SYBO2/oradata/idx1.dbf’ size 10m autoextend on;  
  24.   
  25. SQL> create tablespace idx2 datafile ‘/u02/database/SYBO2/oradata/idx2.dbf’ size 10m autoextend on;  
  26.   
  27. SQL> create tablespace idx3 datafile ‘/u02/database/SYBO2/oradata/idx3.dbf’ size 10m autoextend on;  
  28.   
  29. SQL> conn leshami/xxx  
  30.   
  31. — 创建一个lookup表  
  32. CREATE TABLE lookup (  
  33.   id            NUMBER(10),  
  34.   description   VARCHAR2(50)  
  35. );  
  36.   
  37. –添加主键约束  
  38. ALTER TABLE lookup ADD (  
  39.   CONSTRAINT lookup_pk PRIMARY KEY (id)  
  40. );  
  41.   
  42. –插入数据  
  43. INSERT INTO lookup (id, description) VALUES (1, ‘ONE’);  
  44. INSERT INTO lookup (id, description) VALUES (2, ‘TWO’);  
  45. INSERT INTO lookup (id, description) VALUES (3, ‘THREE’);  
  46. COMMIT;  
  47.   
  48. CREATE TABLE big_table (  
  49.   id            NUMBER(10),  
  50.   created_date  DATE,  
  51.   lookup_id     NUMBER(10),  
  52.   data          VARCHAR2(50)  
  53. )  
  54. PARTITION BY RANGE (created_date)  
  55. (PARTITION big_table_2012 VALUES LESS THAN (TO_DATE(’01/01/2013′‘DD/MM/YYYY’)) tablespace tbs1,  
  56.  PARTITION big_table_2013 VALUES LESS THAN (TO_DATE(’01/01/2014′‘DD/MM/YYYY’)) tablespace tbs2,  
  57.  PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE)tablespace tbs3 ) ;  
  58.    
  59. –填充数据到分区表  
  60. DECLARE  
  61.   l_lookup_id    lookup.id%TYPE;  
  62.   l_create_date  DATE;  
  63. BEGIN  
  64.   FOR i IN 1 .. 10000 LOOP  
  65.     IF MOD(i, 3) = 0 THEN  
  66.       l_create_date := ADD_MONTHS(SYSDATE, -24);  
  67.       l_lookup_id   := 2;  
  68.     ELSIF MOD(i, 2) = 0 THEN  
  69.       l_create_date := ADD_MONTHS(SYSDATE, -12);  
  70.       l_lookup_id   := 1;  
  71.     ELSE  
  72.       l_create_date := SYSDATE;  
  73.       l_lookup_id   := 3;  
  74.     END IF;  
  75.       
  76.     INSERT INTO big_table (id, created_date, lookup_id, data)  
  77.     VALUES (i, l_create_date, l_lookup_id, ‘This is some data for ‘ || i);  
  78.   END LOOP;  
  79.   COMMIT;  
  80. END;  
  81. /   
  82.   
  83. –未指定索引分区及存储表空间情形下创建索引  
  84. SQL> CREATE INDEX bita_created_date_i ON big_table(created_date) LOCAL;  
  85.   
  86. Index created.  
  87.   
  88. SQL> select index_name, partitioning_type, partition_count from user_part_indexes;  
  89.   
  90. INDEX_NAME                     PARTITI PARTITION_COUNT  
  91. —————————— ——- —————  
  92. BITA_CREATED_DATE_I            RANGE                 3  
  93.   
  94. –Author : Leshami  
  95. –Blog   : http://blog.csdn.net/leshami  
  96.   
  97. –从下面的查询可知,索引直接存放到分表表对应的表空间  
  98. SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;  
  99.   
  100. PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME  
  101. —————————— —————————————- ——————————  
  102. BIG_TABLE_2014                 MAXVALUE                                 TBS3  
  103. BIG_TABLE_2013                 TO_DATE(‘ 2014-01-01 00:00:00’, ‘SYYYY-M TBS2  
  104.                                M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIA  
  105.   
  106. BIG_TABLE_2012                 TO_DATE(‘ 2013-01-01 00:00:00’, ‘SYYYY-M TBS1  
  107.                                M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIA  
  108.   
  109. –删除索引  
  110. SQL> drop index bita_created_date_i;  
  111.   
  112. –指定索引分区名表空间名创建索引  
  113. SQL> CREATE INDEX bita_created_date_i  
  114.   2     ON big_table (created_date)  
  115.   3     LOCAL (  
  116.   4        PARTITION idx_2012 TABLESPACE idx1,  
  117.   5        PARTITION idx_2013 TABLESPACE idx2,  
  118.   6        PARTITION idx_2014 TABLESPACE idx3)  
  119.   7     PARALLEL 3;  
  120.   
  121. Index created.  
  122.   
  123. SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;  
  124.   
  125. PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME  
  126. —————————— —————————————- ——————————  
  127. IDX_2014                       MAXVALUE                                 IDX3  
  128. IDX_2013                       TO_DATE(‘ 2014-01-01 00:00:00’, ‘SYYYY-M IDX2  
  129.                                M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIA  
  130.   
  131. IDX_2012                       TO_DATE(‘ 2013-01-01 00:00:00’, ‘SYYYY-M IDX1  
  132.                                M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIA  
  133.   
  134. SQL> select * from big_table where rownum<2;  
  135.   
  136.         ID CREATED_  LOOKUP_ID DATA  
  137. ———- ——– ———- ————————————————–  
  138.       1413 20120625          2 This is some data for 1413  
  139.   
  140. –查看local index是否被使用,从下面的执行计划中可知,索引被使用,支持分区消除        
  141. SQL> set autot trace exp;  
  142. SQL> select * from big_table where created_date=to_date(‘20120625’,‘yyyymmdd’);  
  143.   
  144. Execution Plan  
  145. ———————————————————-  
  146. Plan hash value: 2556877094  
  147.   
  148. ————————————————————————————————————————–  
  149. | Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  150. ————————————————————————————————————————–  
  151. |   0 | SELECT STATEMENT                   |                     |     1 |    41 |     2   (0)| 00:00:01 |       |       |  
  152. |   1 |  PARTITION RANGE SINGLE            |                     |     1 |    41 |     2   (0)| 00:00:01 |     1 |     1 |  
  153. |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE           |     1 |    41 |     2   (0)| 00:00:01 |     1 |     1 |  
  154. |*  3 |    INDEX RANGE SCAN                | BITA_CREATED_DATE_I |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |  
  155. ————————————————————————————————————————–  

3、全局分区索引演示

  1. –为表添加主键  
  2. SQL> ALTER TABLE big_table ADD (  
  3.   2    CONSTRAINT big_table_pk PRIMARY KEY (id)  
  4.   3  );  
  5.   
  6. Table altered.         
  7.   
  8. SQL> select index_name,index_type,tablespace_name,global_stats,partitioned  
  9.   2  from user_indexes where index_name=‘BIG_TABLE_PK’;  
  10.   
  11. INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                GLO PAR  
  12. —————————— ————————— —————————— — —  
  13. BIG_TABLE_PK                   NORMAL                      TBS_TMP                        YES NO  
  14.   
  15. SQL> set autot trace exp;                                                                                            
  16. SQL> select * from big_table where id=1412;                                                                          
  17.                                                                                                                      
  18. Execution Plan                                                                                                       
  19. ———————————————————-                                                           
  20. Plan hash value: 2662411593                                                                                          
  21.                                                                                                                      
  22. ——————————————————————————————————————-  
  23. | Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  24. ——————————————————————————————————————-  
  25. |   0 | SELECT STATEMENT                   |              |     1 |    62 |     2   (0)| 00:00:01 |       |       |  
  26. |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE    |     1 |    62 |     2   (0)| 00:00:01 | ROWID | ROWID |  
  27. |*  2 |   INDEX UNIQUE SCAN                | BIG_TABLE_PK |     1 |       |     1   (0)| 00:00:01 |       |       |  
  28. ——————————————————————————————————————-  
  29. –如上,在其执行计划中,Pstart与Pstop都为ROWID  
  30. –出现了GLOBAL INDEX ROWID,我们添加主键时并未指定Global,但其执行计划表明执行了全局索引访问  
  31. –这个地方有待证实,对于分区表,非分区键上的主键或唯一索引是否一定是全局索引  
  32.   
  33. SQL> drop index bita_created_date_i;  
  34.   
  35. –下面创建全局索引,创建时需要指定分区键的范围和值  
  36. SQL> CREATE INDEX bita_created_date_i  
  37.    ON big_table (created_date)  
  38.    GLOBAL PARTITION BY RANGE (created_date)  
  39.       (  
  40.          PARTITION  
  41.             idx_1 VALUES LESS THAN (TO_DATE (’01/01/2013′‘DD/MM/YYYY’))  
  42.             TABLESPACE idx1,  
  43.          PARTITION  
  44.             idx_2 VALUES LESS THAN (TO_DATE (’01/01/2014′‘DD/MM/YYYY’))  
  45.             TABLESPACE idx2,  
  46.          PARTITION idx_3 VALUES LESS THAN (maxvalue) TABLESPACE idx3);  
  47.   
  48. SQL> select index_name, partitioning_type, partition_count,locality from user_part_indexes;  
  49.   
  50. INDEX_NAME                     PARTITI PARTITION_COUNT LOCALI  
  51. —————————— ——- ————— ——  
  52. BITA_CREATED_DATE_I_G          RANGE                 3 GLOBAL  
  53.   
  54. SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;  
  55.   
  56. PARTITION_NAME                 HIGH_VALUE             TABLESPACE_NAME  
  57. —————————— ——————— ——————————  
  58. IDX_1                          TO_DATE(‘ 2013-01-01  IDX1  
  59. IDX_2                          TO_DATE(‘ 2014-01-01  IDX2  
  60. IDX_3                          MAXVALUE              IDX3    
  61.   
  62. –下面是其执行计划,可以看出支持分区消除  
  63. SQL> set autot trace exp;  
  64. SQL> select * from big_table where created_date=to_date(‘20130625’,‘yyyymmdd’);  
  65.   
  66. Execution Plan  
  67. ———————————————————-  
  68. Plan hash value: 1378264218  
  69.   
  70. —————————————————————————————————————————  
  71. | Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  72. —————————————————————————————————————————  
  73. |   0 | SELECT STATEMENT                    |                     |     1 |    41 |     2   (0)| 00:00:01 |       |       |  
  74. |   1 |  PARTITION RANGE SINGLE             |                     |     1 |    41 |     2   (0)| 00:00:01 |     2 |     2 |  
  75. |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE           |     1 |    41 |     2   (0)| 00:00:01 |     2 |     2 |  
  76. |*  3 |    INDEX RANGE SCAN                 | BITA_CREATED_DATE_I |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |  
  77. —————————————————————————————————————————  
  78.   
  79. –以下为范围查询,Pstart为1,Pstop为2,同样支持分区消除  
  80. SQL> select * from big_table                                                                 
  81.   2  where created_date>=to_date(‘20120625’,‘yyyymmdd’and created_date<=to_date(‘20130625’,‘yyyymmdd’);  
  82.   
  83. Execution Plan  
  84. ———————————————————-  
  85. Plan hash value: 213633793  
  86.   
  87. ——————————————————————————————————  
  88. | Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  89. ——————————————————————————————————  
  90. |   0 | SELECT STATEMENT         |           |  3334 |   133K|    14   (0)| 00:00:01 |       |       |  
  91. |   1 |  PARTITION RANGE ITERATOR|           |  3334 |   133K|    14   (0)| 00:00:01 |     1 |     2 |  
  92. |*  2 |   TABLE ACCESS FULL      | BIG_TABLE |  3334 |   133K|    14   (0)| 00:00:01 |     1 |     2 |  
  93. ——————————————————————————————————  

标签