Oracle全局索引和局部索引

Oracle数据库中，分区表有两种类型的分区索引，全局索引和本地索引，其中本地索引又可以分为本地前缀索引和本地非前缀索引

全局索引以整个表的数据为对象建立索引，
索引分区中的索引条目既可能是基于相同的键值但是来自不同的分区，也可能是多个不同键值的组合。
所以，全局索引既允许索引分区的键值和表分区键值相同，也可以不相同.

举例 先建一个分区表：

 create table t_orders (
     orderno      number,
     partno       varchar2(50),
     datatime     date,
     primary key (orderno)
     )
     partition by range (datatime)
      (partition Q1 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
       partition Q2 values less than (TO_DATE('2019-07-01','YYYY-MM-DD')),
       partition Q3 values less than (TO_DATE('2019-10-01','YYYY-MM-DD')),
       partition Q4 values less than (TO_DATE('2020-01-01','YYYY-MM-DD'))
      );

全局分区：

create index orders_global_idx1 on t_orders(partno,datatime) [global]; -- global可以省略，默认及全局
create index orders_global_idx2 on t_orders(partno) [global]; -- global可以省略，默认及全局
-- 或者这样，分区范围跟表默认分区不一致
create index orders_global_idx3 on t_orders(datatime,partno)
       global partition by range (datatime)
       (partition GLOBAL1 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
        partition GLOBAL2 values less than (TO_DATE('2019-07-01','YYYY-MM-DD')),
        partition GLOBAL3 values less than (TO_DATE('2019-10-01','YYYY-MM-DD')),
        partition GLOBAL4 values less than (MAXVALUE)
        );
-- 不能创建非前缀的全局分区索引，会提示ORA-14038 global partitioned index must be prefixed：
create index orders_global_idx3 on t_orders(partno,datatime)
       global partition by range (datatime)
       (partition GLOBAL1 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
        partition GLOBAL2 values less than (TO_DATE('2019-07-01','YYYY-MM-DD')),
        partition GLOBAL3 values less than (TO_DATE('2019-10-01','YYYY-MM-DD')),
        partition GLOBAL4 values less than (MAXVALUE)
        );

本地索引，必须显示指定local：

create index orders_local_idx1  on t_orders(partno)   local;
create index orders_local_idx2  on t_orders(datatime,partno)   local;
也可以指定分区名：
create index orders_local_idx3 on t_orders(datatime)  local
  (partition LOCAL1,
   partition LOCAL2,
   partition LOCAL3,
   partition LOCAL4
  );
  
-- 不过不建议，必须数目对应，否则报错,不是自找麻烦嘛！
create index orders_local_idx3 on t_orders(datatime)  local
  (partition LOCAL1,
   partition LOCAL2,
   partition LOCAL3,
   partition LOCAL4,
   partition LOCAL5
  );
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

总结:
全局索引：
优点：通过索引检索，没有限定分区的谓词、或跨分区时，性能好点，
缺点：分区维护的时候麻烦，drop分区等维护会失效，dml的时候索引维护成本高，数据大了rebuild也难；位图索引只能为局部分区索引
 
local 索引：
优点：通过索引检索，有限定分区的谓词、不跨分区时，性能好，分区维护容易，dml的索引维护底，rebuild也方便；多应用于数据仓库环境中
缺点：通过索引检索，又没有限定分区的谓词、或跨分区时，性能不如全局索引

表上建主键，两点注意的地方：
1.应用上drop 分区表时显示指定update global indexes 就可以自动维护分区表t_orders上的所有全局索引的有效性
    alter table t_orders drop partition Q1  update indexes; 
2.将主键上的索引建成本地索引

当然创建索引的时候也可以指定其他属性，比如compress-压缩，reverse-倒序(在RAC中用的比较多，防止热点)