Oracle 分区索引介绍和实例演示
分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长，普通的堆表需要转换到分区表，其索引呢，则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断，在获取所需数据时，只需要访问更小的索引片断(块)即可实现。同时把分区放在不同的表空间可以提高分区的可用性和可靠性。本文主要描述了分区索引的相关特性并给出演示示例。
1、分区索引的相关概念
a、分区索引的几种方式：表被分区而索引未被分区；表未被分区，而索引被分区；表和索引都被分区
b、分区索引可以分为本地分区索引以及全局分区索引
本地分区索引：
本地分区索引信息的存放依赖于父表分区。也就是说对于本地索引一定是基于分区表创建的。
缺省情况下，创建本地索引时，如未指定索引存放表空间，会自动将本地索引存放到数据所在分区定义时的表空间。
本地索引的分区机制和表的分区机制一样，本地索引可以是是B树索引或位图索引。
本地索引是对单个分区的，每个分区索引只指向一个表分区，为对等分区。
本地索引支持分区独立性，因此对于这些单独的分区增加，截取，删除，分割，脱机等处理无需同时删除或重建。
本地索引多应用于数据仓库环境中。
全局分区索引：
全局分区索引时分区表和全局索引的分区机制不一样，在创建时必须定义分区键的范围和值。
全局分区索引在创建时应指定Global关键字且全局分区索引只能是B树索引。
全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即索引列必须包含分区键。
全局索引分区中，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区。
默认情况下全局索引对于分区增加，截取，删除，分割等都必须重建或修改时指定updateglobalindexs。
全局分区索引只按范围或者散列hash分区。
全局分区索引多应用于oltp系统中。
c、有前缀索引和无前缀索引
本地和全局分区索引又分为两个子类型即有前缀索引和无前缀索引。
前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
有前缀索引:
有前缀索引包含了分区键，即分区键列被包含在索引中。
有前缀索引支持本地分区索引以及全局分区索引。
无前缀索引：
无前缀索引即没有把分区键的前导列作为索引的前导列。
无前缀索引仅仅支持本地分区索引。
2、本地分区索引演示
--环境 SQL>select*fromv$versionwhererownum<2;
BANNER ---------------------------------------------------------------- OracleDatabase10gEnterpriseEditionRelease10.2.0.3.0-64bi
SQL>createuserleshamiidentifiedbyxxx;
SQL>grantdbatoleshami;
--创建演示需要用到的表空间 SQL>createtablespacetbs_tmpdatafile'/u02/database/SYBO2/oradata/tbs_tmp.dbf'size10mautoextendon;
SQL>alteruserleshamidefaulttablespacetbs_tmp;
SQL>createtablespacetbs1datafile'/u02/database/SYBO2/oradata/tbs1.dbf'size10mautoextendon;
SQL>createtablespacetbs2datafile'/u02/database/SYBO2/oradata/tbs2.dbf'size10mautoextendon;
SQL>createtablespacetbs3datafile'/u02/database/SYBO2/oradata/tbs3.dbf'size10mautoextendon;
SQL>createtablespaceidx1datafile'/u02/database/SYBO2/oradata/idx1.dbf'size10mautoextendon;
SQL>createtablespaceidx2datafile'/u02/database/SYBO2/oradata/idx2.dbf'size10mautoextendon;
SQL>createtablespaceidx3datafile'/u02/database/SYBO2/oradata/idx3.dbf'size10mautoextendon;
SQL>connleshami/xxx
--创建一个lookup表 CREATETABLElookup( id NUMBER(10), description VARCHAR2(50) );
--添加主键约束 ALTERTABLElookupADD( CONSTRAINTlookup_pkPRIMARYKEY(id) );
--插入数据 INSERTINTOlookup(id,description)VALUES(1,'ONE'); INSERTINTOlookup(id,description)VALUES(2,'TWO'); INSERTINTOlookup(id,description)VALUES(3,'THREE'); COMMIT;
CREATETABLEbig_table( id NUMBER(10), created_date DATE, lookup_id NUMBER(10), data VARCHAR2(50) ) PARTITIONBYRANGE(created_date) (PARTITIONbig_table_2012VALUESLESSTHAN(TO_DATE('01/01/2013','DD/MM/YYYY'))tablespacetbs1, PARTITIONbig_table_2013VALUESLESSTHAN(TO_DATE('01/01/2014','DD/MM/YYYY'))tablespacetbs2, PARTITIONbig_table_2014VALUESLESSTHAN(MAXVALUE)tablespacetbs3); --填充数据到分区表 DECLARE l_lookup_id lookup.id%TYPE; l_create_date DATE; BEGIN FORiIN1..10000LOOP IFMOD(i,3)=0THEN l_create_date:=ADD_MONTHS(SYSDATE,-24); l_lookup_id :=2; ELSIFMOD(i,2)=0THEN l_create_date:=ADD_MONTHS(SYSDATE,-12); l_lookup_id :=1; ELSE l_create_date:=SYSDATE; l_lookup_id :=3; ENDIF; INSERTINTObig_table(id,created_date,lookup_id,data) VALUES(i,l_create_date,l_lookup_id,'Thisissomedatafor'||i); ENDLOOP; COMMIT; END; /
--未指定索引分区及存储表空间情形下创建索引 SQL>CREATEINDEXbita_created_date_iONbig_table(created_date)LOCAL;
Indexcreated.
SQL>selectindex_name,partitioning_type,partition_countfromuser_part_indexes;
INDEX_NAME PARTITIPARTITION_COUNT ---------------------------------------------------- BITA_CREATED_DATE_I RANGE 3
--Author:Leshami
--从下面的查询可知，索引直接存放到分表表对应的表空间 SQL>selectpartition_name,high_value,tablespace_namefromuser_ind_partitions;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME ---------------------------------------------------------------------------------------------------- BIG_TABLE_2014 MAXVALUE TBS3 BIG_TABLE_2013 TO_DATE('2014-01-0100:00:00','SYYYY-MTBS2 M-DDHH24:MI:SS','NLS_CALENDAR=GREGORIA
BIG_TABLE_2012 TO_DATE('2013-01-0100:00:00','SYYYY-MTBS1 M-DDHH24:MI:SS','NLS_CALENDAR=GREGORIA
--删除索引 SQL>dropindexbita_created_date_i;
--指定索引分区名表空间名创建索引 SQL>CREATEINDEXbita_created_date_i 2 ONbig_table(created_date) 3 LOCAL( 4 PARTITIONidx_2012TABLESPACEidx1, 5 PARTITIONidx_2013TABLESPACEidx2, 6 PARTITIONidx_2014TABLESPACEidx3) 7 PARALLEL3;
Indexcreated.
SQL>selectpartition_name,high_value,tablespace_namefromuser_ind_partitions;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME ---------------------------------------------------------------------------------------------------- IDX_2014 MAXVALUE IDX3 IDX_2013 TO_DATE('2014-01-0100:00:00','SYYYY-MIDX2 M-DDHH24:MI:SS','NLS_CALENDAR=GREGORIA
IDX_2012 TO_DATE('2013-01-0100:00:00','SYYYY-MIDX1 M-DDHH24:MI:SS','NLS_CALENDAR=GREGORIA
SQL>select*frombig_tablewhererownum<2;
IDCREATED_ LOOKUP_IDDATA ------------------------------------------------------------------------------ 141320120625 2Thisissomedatafor1413
--查看localindex是否被使用，从下面的执行计划中可知，索引被使用，支持分区消除 SQL>setautottraceexp; SQL>select*frombig_tablewherecreated_date=to_date('20120625','yyyymmdd');
ExecutionPlan ---------------------------------------------------------- Planhashvalue:2556877094
-------------------------------------------------------------------------------------------------------------------------- |Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time |Pstart|Pstop| -------------------------------------------------------------------------------------------------------------------------- | 0|SELECTSTATEMENT | | 1| 41| 2 (0)|00:00:01| | | | 1| PARTITIONRANGESINGLE | | 1| 41| 2 (0)|00:00:01| 1| 1| | 2| TABLEACCESSBYLOCALINDEXROWID|BIG_TABLE | 1| 41| 2 (0)|00:00:01| 1| 1| |* 3| INDEXRANGESCAN |BITA_CREATED_DATE_I| 1| | 1 (0)|00:00:01| 1| 1| --------------------------------------------------------------------------------------------------------------------------