Oracle如何創建分區索引

153次閱讀

共計 6457 個字符，預計需要花費 17 分鐘才能閱讀完成。

這篇文章主要介紹了 Oracle 如何創建分區索引，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

分區索引總結：一，分區索引分為 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

– 如果我在表上增加個分區，則 Oracle 會自動維護分區的索引, 注意此時加分區必須是用 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.

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“Oracle 如何創建分區索引”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!

正文完