Oracle分割槽之五：建立分割槽索引總結
分割槽索引總結：
一，分割槽索引分為2類：
1、global，它必定是Prefix的。不存在non-prefix的
2、local，它又分成2類：
2.1、prefix：索引的第一個列等於表的分割槽列。
2.2、non-prefix：索引的第一個列不等於表的分割槽列。
LOCAL的索引只能是表的分割槽方式，不能自己寫分割槽方式。他們是EQUI-Partition的。
GLOBAL索引可以不分割槽，這個時候就是普通的一個索引。同一個列只能只有一個索引，這個列可以是GLOBAL或者是LOCAL的索引。如果唯一索引所在的列不是表的分割槽列，只能建立GLOBAL索引。
例如：分割槽表
create table test (id number,data varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (10000) ,
partition p2 values less than (20000) ,
partition p3 values less than (maxvalue)
);
--在ID列上建立一個LOCAL的索引
SQL>create index id_local on test(id) local;
Index created.
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
ID_LOCAL P1 10000 USABLE
ID_LOCAL P2 20000 USABLE
ID_LOCAL P3 MAXVALUE USABLE
從上面可以看出索引的分割槽和表一樣，即是EQUI-PARTITION
--如果我在表上增加個分割槽，則會自動維護分割槽的索引,注意此時加分割槽必須是用split,直接加會出錯的。例如：
SQL> alter table test add partition p4 values less than (30000);
alter table test add partition p4 values less than (30000)
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> alter table test split partition p3 at (30000) into (partition p3, partition p4);
Table altered.
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
ID_LOCAL P1 10000 USABLE
ID_LOCAL P2 20000 USABLE
ID_LOCAL P3 30000 USABLE
ID_LOCAL P4 MAXVALUE USABLE
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_LOCAL';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
ID_LOCAL NORMAL TEST
--刪除id_local索引
SQL> drop index id_local;
Index dropped.
--重新在ID列上建立一個GLOBAL的索引
SQL> create index id_global on test(id) global;
Index created.
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL';
no rows selected
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
ID_GLOBAL NORMAL TEST
從上面可以看出，它此時是個普通索引。dba_ind_partitions里根本就沒有記錄。
---刪除索引
SQL> drop index id_global;
Index dropped.
注意：不刪會報：ORA-01408: such column list already indexed
--建立全域性索引
SQL> create index i_id_global on test(data) global
partition by range(id)
( partition p1 values less than (10000) ,
partition p2 values less than (MAXVALUE)
);
partition by range(id)
*
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed
此錯誤表示GLOBAL的索引必須是prefixed，即索引分割槽的列，必須是其基表的分割槽列。
SQL>create index id_global on test(id) global
partition by range(id)
( partition p1 values less than (10000) ,
partition p2 values less than (MAXVALUE)
);
Index created.
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
ID_GLOBAL P1 10000 USABLE
ID_GLOBAL P2 MAXVALUE USABLE
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
ID_GLOBAL NORMAL TEST
從上面可以看出，它此時是個GLOBAL的索引了。dba_ind_partitions裡有記錄。請和上面的做個比較，加深印象。
二，到底如何判斷建立怎樣的分割槽索引(GLOBAL 還是LOCAL)
我將用下面的例子來分析到底需要建立什麼型別索引好。
create table TT(id number,createdate date)
partition by range(createdate)
(
partition Q1 VALUES LESS THAN (TO_DATE('2012-03-30','YYYY-MM-DD')),
partition Q2 VALUES LESS THAN (TO_DATE('2012-06-30','YYYY-MM-DD')),
partition Q3 VALUES LESS THAN (TO_DATE('2012-09-30','YYYY-MM-DD')),
partition Q4 VALUES LESS THAN (TO_DATE('2012-12-31','YYYY-MM-DD')),
partition Q_OTHERS VALUES LESS THAN (MAXVALUE)
);
注意：只能是to_date,其他的任何函式都不行,maxvalue必須在最後，他可以包括NULL值。
第一種情況：
如果查詢的語句的條件是where createdate='2012-10-19' and id>100，則此時查詢的是4號分割槽，假設他有10萬條記錄。在掃描這10萬條記錄的時候，
可以使用id列上的索引。這個時候可以在ID列上建立個local nonprofiex索引
create index index_tt1_local on TT(id) local
( partition p1,
partition p2,
partition p3,
partition p4,
partition p5
);
注意：索引分割槽的數量和其基本的分割槽數量要一樣。
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT1_LOCAL';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
INDEX_TT1_LOCAL P1 TO_DATE(' 2012-03-30 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT1_LOCAL P2 TO_DATE(' 2012-06-30 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT1_LOCAL P3 TO_DATE(' 2012-09-30 USABLE
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT1_LOCAL P4 TO_DATE(' 2012-12-31 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT1_LOCAL P5 MAXVALUE USABLE
第二種情況:
如果查詢的語句條件只有一個createdate,如where createdate='2010-10-19'，則這種情況就在createdate上建立一個local profiex索引
SQL> create index index_TT2_local on TT(createdate) local;
Index created.
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT2_LOCAL';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
INDEX_TT2_LOCAL Q1 TO_DATE(' 2012-03-30 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT2_LOCAL Q2 TO_DATE(' 2012-06-30 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT2_LOCAL Q3 TO_DATE(' 2012-09-30 USABLE
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT2_LOCAL Q4 TO_DATE(' 2012-12-31 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT2_LOCAL Q_OTHERS MAXVALUE USABLE
從上面查詢可以看出他和表是equi-partitioned.
第三種情況:
如果查詢根本就沒有createdate，而是有像where id>100的條件，則就只能在ID列上建立GLOBAL索引了
SQL> drop index index_tt1_local;
Index dropped.
注意：不刪報ORA-01408: such column list already indexed
SQL> create index index_tt3_global on TT(id)
global partition by range(id)
(
partition p1 values less than (100000),
partition p2 values less than (200000),
partition p3 values less than (MAXVALUE)
);
從上面可以看出，GLOBAL的索引的分割槽數和其基表是沒有關係的。他甚至可以像如下建立索引，即一個普通索引。但是LOCAL的必須和其基本分割槽數一致。
-建立需先刪索引index_tt3_global
SQL> create index index_tt3_global on TT(id) global;
Index created.
總之，一般建議建立LOCAL的索引，因為GLOBAL的容易所有的都失效，而LOCAL的最多隻在某個分割槽上失效。索引失效必須一個分割槽的一個分割槽的REBUILD。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2143158/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 建立所有分割槽索引Oracle索引
- 分割槽表、分割槽索引和全域性索引部分總結索引
- 分割槽表及分割槽索引建立示例索引
- Oracle分割槽表及分割槽索引Oracle索引
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- Oracle索引分割槽Oracle索引
- oracle 分割槽表總結Oracle
- Oracle 分割槽表 總結Oracle
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- oracle分割槽索引（二）Oracle索引
- oracle分割槽索引（一）Oracle索引
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- 全面學習分割槽表及分割槽索引(6)--建立range-list組合分割槽索引
- oracle分割槽及分割槽索引partition_partition index_維護(五)_快捷方法Oracle索引Index
- oracle分割槽表總結(轉)Oracle
- Oracle 建立分割槽表Oracle
- oracle索引詳解 分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- rebuild分割槽表分割槽索引的方法Rebuild索引
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- 深入學習分割槽表及分割槽索引(5)--建立range-hash組合分割槽(續)索引
- 分割槽表並行建立索引並行索引
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle本地分割槽索引跨分割槽對成本的影響Oracle索引
- 分割槽表總結
- 表分割槽總結
- 分割槽表 總結
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- 全域性分割槽索引和區域性分割槽索引索引
- 分割槽表分割槽索引查詢效率探究索引
- Oracle Partition 分割槽詳細總結Oracle
- Oracle12c：建立主分割槽、子分割槽，實現自動分割槽插入效果Oracle
- Oracle 分割槽表的建立Oracle
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引