共计 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 行业资讯频道，更多相关知识等着你来学习!
