Oracle 本地索引(local)与全局索引(global)的区别

内容整理来自网络
分区索引分为本地索引(local index)和全局索引(global index)。
对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。
对于global索引，可以选择是否分区，而且索引的分区可以不和表分区相对应。
当对分进行维护操作时，通常会导致全局索引的INVALID,必须在执行完操作后REBULID。
其中本地索引分为有前缀(PREFIX)的本地索引和无前缀(NOPREFIX)的本地索引。而全局索引目前只支持有前缀的索引。

本地索引（LOCAL INDEX）
 

1. 本地索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区数，一句话，本地索引的分区机制和表的分区机制一样。
2.如果本地索引的索引列以分区键开头，则成为前缀局部索引。
3.如果本地索引的索引列不是以分区键开头，或者不包含分区键列，则成为非前缀索引。
4.前缀和非前缀索引都可以支持索引分区的消除，前提是查询条件中包含索引分区键。
5.本地索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用班呢第索引去给表做唯一约束，则约束中必须包含有分区键列。
6.本地索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区所引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区TRUNCATE或者MOVE、SHRINK等，可能会影响到n个全局索引分区，正因为这一点，本地索引具有更高的可用性。
7.位图索引只能为本地分区索引。
8.本地索引应用于数据仓库环境当中。

本地索引：创建了一个分区表后，如果需要在表上面创建索引，并且索引的分区机制和表的分区机制一样，那么这样的索引叫做本地分区索引。本地索引是由ORACLE自动管理，它分为有前缀的本地索引和无前缀的本地索引。有前缀的本地索引包含了分区键，并且将其作为引导列的索引。无前缀的本地索引就是没有将分区键的前导列作为索引的前导列的索引。

下面举例说明：

create  table student1 (id number,stu_no varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (100) tablespace users1,
partition p2 values less than (200) tablespace users2,
partition p3 values less than (maxvalue) tablespace users3
);
create index stu_prefix_index on student1(id) local;--因为id是分区键，所以这样就创建了一个有前缀的本地索引
create  index stu_noprefix_index on student1(stu_no) local;--因为stu_no不是分区键，所以这样就创建了一个无前缀的本地索引

全局索引（GLOBAL INDEX）

1.全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。
2.全局索引可以分区，也可是不分区索引，全局索引必须是前缀索引，既全局索引的索引列必须是以索引分区键作为其前几列。
3.全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebuild若干个分区甚至整个索引。
4.全局索引多应用于OLTP(在线分析系统)系统中。
5.全局索引只能按范围或者散列hash分区，hash分区是以10g以后才支持。
6.oracle9i以后对分区表做move或者truncate的时候用update global indexes语句来同步更新去哪聚分区索引，用消耗一定资源来换取高度的可用性。

下面举例说明：

CREATE  TABLE ORDERS(
       ORDER_NO   NUMBER,
       PART_NO    VARCHAR2(40),
       ORD_DATE   DATE
)
PARTITION BY RANGE(ORD_DATE)
(PARTITION Q1 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD'))
,PARTITION Q2 VALUES LESS THAN (TO_DATE('2014-02-01','YYYY-MM-DD'))
,PARTITION Q3 VALUES LESS THAN (TO_DATE('2014-03-01','YYYY-MM-DD'))
,PARTITION Q4 VALUES LESS THAN (TO_DATE('2014-04-01','YYYY-MM-DD'))
,PARTITION Q5 VALUES LESS THAN (TO_DATE('2014-05-01','YYYY-MM-DD'))
,PARTITION Q6 VALUES LESS THAN (TO_DATE('2014-06-01','YYYY-MM-DD'))
,PARTITION Q7 VALUES LESS THAN (TO_DATE('2014-07-01','YYYY-MM-DD'))
);

--创建全局索引，且索引分区键和表分区键相同

CREATE INDEX ORDERS_GLOBAL_1_IDX ON ORDERS(ORD_DATE) GLOBAL
PARTITION BY RANGE(ORD_DATE)
(PARTITION GLOBAL1 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD'))
,PARTITION GLOBAL2 VALUES LESS THAN (TO_DATE('2014-02-01','YYYY-MM-DD'))
,PARTITION GLOBAL3 VALUES LESS THAN (TO_DATE('2014-03-01','YYYY-MM-DD'))
,PARTITION GLOBAL4 VALUES LESS THAN (TO_DATE('2014-04-01','YYYY-MM-DD'))
,PARTITION GLOBAL5 VALUES LESS THAN (TO_DATE('2014-05-01','YYYY-MM-DD'))
,PARTITION GLOBAL6 VALUES LESS THAN (TO_DATE('2014-06-01','YYYY-MM-DD'))
,PARTITION GLOBAL7 VALUES LESS THAN (TO_DATE('2014-07-01','YYYY-MM-DD'))
,PARTITION GLOBAL8 VALUES LESS THAN (MAXVALUE)
);

--创建全局索引，索引的分区键和表分区键不相同

CREATE INDEX ORDERS_GLOBAL_2_IDX 
       ON ORDERS(PART_NO) 
       GLOBAL 
       PARTITION BY RANGE(PART_NO)
       (PARTITION IND1 VALUES LESS THAN (555555)
       ,PARTITION IND2 VALUES LESS THAN (MAXVALUE)
       );

总结：全局索引和表没有直接的关联，必须显示的指定maxvalue值。假如表中新加了分区，不会在全局索引中自动增加新的分区，必须手工添加相应的分区
--查询表名、分区名

SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='ORDERS';

--增加分区

ALTER TABLE ORDERS ADD PARTITION Q8 VALUES LESS THAN(TO_DATE('2014-08-01','YYYY-MM-DD'));

--查询全局索引、索引分区名称

SELECT INDEX_NAME,PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_NAME=UPPER('ORDERS_GLOBAL_1_IDX');

--使用全局索引，索引键值必须和分区键值相同，这就是所谓的前缀索引。Oracle不支持非前缀的全局分区索引，
--如果要建立非前缀分区索引，索引必须建成本地索引。

CREATE INDEX ORDERS_GLOBAL_2_IDX 
       ON ORDERS(PART_NO)
       GLOBAL PARTITION BY RANGE(ORDER_NO)
       (PARTITION IND1 VALUES LESS THAN (555555),
       PARTITION IND2 VALUES LESS THAN(MAXVALUE)
       )
       ;--执行报错

--执行报错
--本地分区
--本地索引的分区和其对应的表分区数量相等，因此每个表分区都对应着相等的索引分区。
--使用本地索引，不需要指定分区范围因为索引对于表而言是本地的，当本地索引创建时，Oracle
--会自动为表中的每个分区创建独立的索引分区。
--创建本地索引不必显示指定maxvalue值，因为为表新添加表分区时，会自动添加相应的索引分区。
--删除全局索引

DROP INDEX ORDERS_GLOBAL_1_IDX;
CREATE INDEX ORDERS_LOCAL_1_IDX
       ON ORDERS(ORD_DATE)
       LOCAL
       (PARTITION LOCAL1
       ,PARTITION LOCAL2
       ,PARTITION LOCAL3
       ,PARTITION LOCAL4
       ,PARTITION LOCAL5
       ,PARTITION LOCAL6
       ,PARTITION LOCAL7
       ,PARTITION LOCAL8);

--查询本地索引

SELECT INDEX_NAME,PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_NAME=UPPER('ORDERS_LOCAL_1_IDX');

--增加本地索引分区

ALTER TABLE ORDERS ADD PARTITION Q9 VALUES LESS THAN(TO_DATE('2014-09-01','YYYY-MM-DD'));

--查询本地索引

SELECT INDEX_NAME,PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_NAME=UPPER('ORDERS_LOCAL_1_IDX');

--由此可以看出，系统已经自动以和表分区相同的名字自动创建了一个索引分区。同理，删除表分区时相对应的索引分区也自动被删除。
--本地索引和全局索引还有一个显著的差别，本地索引可以创建非前缀索引，而全局索引只能是前缀型。
--删除全局索引

DROP INDEX ORDERS_GLOBAL_2_IDX;
CREATE INDEX ORDERS_LOCAL_2_IDX
       ON ORDERS(PART_NO)
       LOCAL
       (PARTITION LOCAL1
       ,PARTITION LOCAL2
       ,PARTITION LOCAL3
       ,PARTITION LOCAL4
       ,PARTITION LOCAL5
       ,PARTITION LOCAL6
       ,PARTITION LOCAL7
       ,PARTITION LOCAL8
       ,PARTITION LOCAL9
       );

--查询非前缀本地索引

SELECT INDEX_NAME
       ,PARTITION_NAME
       ,HIGH_VALUE 
FROM DBA_IND_PARTITIONS
WHERE INDEX_NAME=UPPER('orders_local_2_idx');

--从上面的输出结果可以看出，虽然索引的键值是part_no，但索引分区的键值仍然和表的分区键值相同，即ord_date,
--也即是所谓的非前缀索引

 

^  - ^ ......