Oracle 分區索引，oracle索引

Oracle 分區索引，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 - 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 建立組合分區索引create index idx_ename on T_JC_CF(欄位)
local
(
partition p0810 tablespace USERS,
partition p0901 tablespace USERS,
partition p0904 tablespace USERS,
partition p0907 tablespace USERS,
partition p0910 tablespace USERS,
partition pmax tablespace USERS
)
Oracle中怎麼做分區索引？create index dinya_idx_t on dinya_test(item_id)
　　 global partition by range(item_id)
　　 (
　　 partition idx_1 values less than (1000) tablespace dinya_space01,
　　 partition idx_2 values less than (10000) tablespace dinya_space02,
　　 partition idx_3 values less than (maxvalue) tablespace dinya_space03
　　);