首页 > 代码库 > Oracle 分区索引

Oracle 分区索引

    分区索引(或索引分区)主要是针对分区表而言的。

随着数据量的不断增长,普通的堆表须要转换到分区表,其索引呢,则相应的转换到分区索引。

分区索引的优点是显而易见的。

就是简单地把一个索引分成多个片断,在获取所需数据时,仅仅须要訪问更小的索引片断(块)就可以实现。同一时候把分区放在不同的表空间能够提高分区的可用性和可靠性。

本文主要描写叙述了分区索引的相关特性并给出演示演示样例。

 

1、分区索引的相关概念
a、分区索引的几种方式:表被分区而索引未被分区。表未被分区,而索引被分区。表和索引都被分区
b、分区索引能够分为本地分区索引以及全局分区索引
本地分区索引:
   本地分区索引信息的存放依赖于父表分区。

也就是说对于本地索引一定是基于分区表创建的。
   缺省情况下,创建本地索引时,如未指定索引存放表空间。会自己主动将本地索引存放到数据所在分区定义时的表空间。
   本地索引的分区机制和表的分区机制一样,本地索引能够是是B树索引或位图索引。
   本地索引是对单个分区的,每一个分区索引仅仅指向一个表分区,为对等分区。
   本地索引支持分区独立性,因此对于这些单独的分区添加,截取,删除,切割,脱机等处理无需同一时候删除或重建。
   本地索引多应用于数据仓库环境中。
      
全局分区索引:
   全局分区索引时分区表和全局索引的分区机制不一样,在创建时必须定义分区键的范围和值。
   全局分区索引在创建时应指定Globalkeyword且全局分区索引仅仅能是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 - 64biSQL> 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.csdn.net/leshami--从以下的查询可知,索引直接存放到分表表相应的表空间SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME------------------------------ ---------------------------------------- ------------------------------BIG_TABLE_2014                 MAXVALUE                                 TBS3BIG_TABLE_2013                 TO_DATE(‘ 2014-01-01 00:00:00‘, ‘SYYYY-M TBS2                               M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIABIG_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                                 IDX3IDX_2013                       TO_DATE(‘ 2014-01-01 00:00:00‘, ‘SYYYY-M IDX2                               M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIAIDX_2012                       TO_DATE(‘ 2013-01-01 00:00:00‘, ‘SYYYY-M IDX1                               M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIASQL> 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 NOSQL> 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 GLOBALSQL> select partition_name, high_value, tablespace_name from user_ind_partitions;PARTITION_NAME                 HIGH_VALUE             TABLESPACE_NAME------------------------------ --------------------- ------------------------------IDX_1                          TO_DATE(‘ 2013-01-01  IDX1IDX_2                          TO_DATE(‘ 2014-01-01  IDX2IDX_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 |------------------------------------------------------------------------------------------------------


相关參考:

使用DBMS_REDEFINITION在线切换普通表到分区表
使用exchange方式切换普通表到分区表
Oracle 分区表
导入导出 Oracle 分区表数据

使用导出导入(datapump)方式将普通表切换为分区表

Oracle 分区索引