分区索引--本地索引和全局索引比较

 分区索引分为本地(local index)索引和全局索引(global index)。

其中本地索引又可以分为有前缀(prefix)的索引和无前缀(nonprefix)的索引。而全局索引目前只支持有前缀的索引。B树索引和位图索引都可以分区，但是HASH索引不可以被分区。位图索引必须是本地索引。下面就介绍本地索引以及全局索引各自的特点来说明区别；

l 全局索引(global index)：即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。

l 本地索引(local index)：其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是说你add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。

 

Oracle建议如果单个表超过2G就最好对其进行分区，对于大表创建分区的好处是显而易见的

 

　在Oracle中，索引和表一样也可以分区。有两种类型的分区索引，本地分区索引(Local)和全局分区索引(Global)。

　　1、本地索引(Local)

　　本地分区索引使用LOCAL关键字创建，其分区边界与表相同(即与每个表分区相关联都有一个索引分区)，下面是一个本地分区索引的例子：

　　[sql]

　　create table sales_par

　　partitioned by range (year)

　　( partition p_2009 values less than (2010)

　　partition p_2010 values less than (2011),

　　partition p_2011 values less than (2012),

　　partition p_2012 values less than (2013)

　　)

　　as select * from sales;

　　--创建本地分区索引

　　create index sales_idx1 on sales_par (product,year) local;

　　可以看出，创建本地分区索引的语句非常简单，不需要指定分区边界，因为它的分区边界和表的一样。其示意图如下：

　　本地分区索引有如下基本特征：

　　1. 本地索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，总之，本地索引的分区机制和表的分区机制一模一样。

　　2. 如果本地索引的索引列以分区键开头，则称为前缀局部索引。

　　3. 如果本地索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。

　　4. 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

　　5. 本地索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用本地索引去给表做唯一性约束，则约束中必须要包括分区键列。

　　6. 本地分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，本地分区索引具有更高的可用性。

　　7. 位图索引只能为本地分区索引。

　　8. 本地索引多应用于OLAP环境中。

　　索引分区消除

　　如果本地分区索引包含分区键并且SQL语句中的谓词条件包含分区键，执行计划通常仅需要访问一个或很少的索引分区，这种特性叫分区消除(Partition Elimination)，分区消除可以有效地减少扫描数据块，提高查询性能，如：

　　[sql]

　　--查询1：

　　select * from sales_par where product = 'CPU' and year = 2011;

　　--查询2：

　　select * from sales_par where product = 'CPU';

　　上例中，查询1的谓词条件包含分区键，因此可以利用分区消除减少扫描的分区数(该例中只需要扫描分区p_2011);而查询2的谓词条件不包含分区键，因此无法利用分区消除。

　　本地分区索引除了分区消除，还具有表可用性更好这个优点，当对某个表分区进行DROP或MERGE操作后，Oracle会自动对所对应的索引分区进行相同的操作，不需要rebuild，即维护操作可以在独立分区进行。

　　2、全局索引(Global)

　　全局索引使用GLOBAL关键字创建，索引的分区边界与表的分区边界不一定匹配，且表和索引的分区键也可以不一样。下面是一个全局分区索引的例子：

　　[sql]

　　create index sales_idx2 on sales (year)

　　global partition by range (year)

　　( partition p_2010 values less than (2011),

　　partition p_2012 values less than (2013)

　　);

　　在上例中，虽然表和索引的分区键是一样的，但是它们的分区边界不一样，所以属于全局分区索引。下面是全局索引的特征

　　1.全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。

　　2.全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。

　　3.全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。

　　4.全局索引多应用于OLTP系统中。

　　5.全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。

　　6.oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

　　7.表用a列作分区，索引用b列作为局部分区索引，若where条件中用b来查询，那么oracle会扫描表和索引的所有分区，成本很高高，此时可以考虑用b做全局分区索引。

全局索引：与本地分区索引不同的是，全局分区索引的分区机制与表的分区机制不一样。全局分区索引全局分区索引只能是B树索引，到目前为止 (10gR2)，oracle只支持有前缀的全局索引。另外oracle不会自动的维护全局分区索引，当我们在对表的分区做修改之后，如果执行修改的语句不加上update global indexes的话，那么索引将不可用。

　　下面是全局索引的一个示意图：

   

三、分区索引不能够将其作为整体重建，必须对每个分区重建

 

SQL> alter index i_id_global rebuild online nologging;

alter index i_id_global rebuild online nologging

ORA-14086: 不能将分区索引作为整体重建

这个时候可以查询dba_ind_partitions，或者user_ind_partitions，找到partition_name，然后对每个分区重建

SQL> select index_name,partition_name from user_ind_partitions where index_name='I_ID_GLOBAL';

INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
I_ID_GLOBAL P1
I_ID_GLOBAL P2

SQL> alter index i_id_global rebuild partition p1 online nologging;

Index altered

SQL> alter index i_id_global rebuild partition p2 online nologging;

Index altered

分区索引字典
DBA_PART_INDEXES 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类新(local/global,)
Dba_ind_partitions每个分区索引的分区级统计信息
Dba_indexes minus dba_part_indexes，可以得到每个表上有哪些非分区索引

索引重建
Alter index idx_name rebuild partition index_partition_name [online nologging]
需要对每个分区索引做rebuild，重建的时候可以选择online(不会锁定表),或者nologging建立索引的时候不生成日志，加快速度。
Alter index rebuild idx_name [online nologging]
对非分区索引，只能整个index重建

相关链接：
Oracle全局索引和本地索引

深入学习Oracle分区表及分区索引