Oracle分区索引

概述

　　索引就是通过事先排好序，从而在查找时可以应用二分查找等高效率的算法，索引也可以简单理解成字典的目录，通过目录定位到详细的内容页。索引可分为分区索引和非分区索引。分区索引一般用在分区表中，关联表中的某个或者某些字段，来提高表数据的查询效率。分区索引通过使用结构可以分为有前缀(prefix)的分区索引和无前缀(nonprefix)的分区索引。通过使用范围又可以分为分为本地(local index)索引和全局索引(global index)。局部索引比全局索引容易管理, 而全局索引比较快。

与索引信息有关的系统表：

　　　　dba_part_indexes 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)。

　　　　dba_ind_partitions 每个分区索引的分区级统计信息，记录分区索引分区信息；

　　　　dba_indexes 可以得到每个表上有哪些索引信息包括分区索引和非分区索引；

　　　　Local索引肯定是分区索引，Global索引可以选择是否分区，如果分区，只能是有前缀的分区索引。

创建基本的分区表

create table T_TESTPDBA (id number, time date,statue number) 
partition by range (time)
(
    partition p1 values less than (to_date('2017-01-1', 'yyyy-mm-dd')),
    partition p2 values less than (to_date('2018-01-1', 'yyyy-mm-dd')),
    partition p3 values less than (to_date('2019-01-1', 'yyyy-mm-dd')),
    partition p4 values less than (maxvalue)
)

查询是否创建成功

---记录表分区信息
select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='T_TESTPDBA';

---记录表信息
select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='T_TESTPDBA';

 

局部索引（Local Index）

　　对于局部索引，oracle会自动维护分区索引的信息，局部索引默认是有前缀的分区索引；

创建局部索引

create index IX_TESTPDBA_ID on t_testpdba(id) local;

由于Oracle自动维护局部索引信息，所以上面语句和下面语句同等效果

create index IX_TESTPDBA_ID on T_TESTPDBA(id) local 
(
   partition p1，
   partition p2，
   partition p3，
   partition p4
);

查询索引信息

select t.owner,t.index_name,t.table_name,t.locality,t.alignment,t.table_name,t.index_name from dba_part_indexes  t where t.table_name='T_TESTPDBA';

查询索引分区详细

select * from dba_ind_partitions  t where t.index_name='IX_TESTPDBA_ID';

通过查询，可以看见系统维护了4个索引分区信息。由于Oracle自动维护局部索引分区信息，不论表分区发生CUD操作，Oracle都会自动维护

表分区删除，Oracle自动维护索引分区

alter table T_TESTPDBA drop partition p3;

表分区拆分，Oracle自动维护索引分区

ALTER TABLE T_TESTPDBA SPLIT PARTITION P4 AT(TO_DATE('2020-01-01','YYYY-MM-DD')) INTO (PARTITION P41,PARTITION P5); 

表分区合并，Oracle自动维护索引分区

ALTER TABLE T_TESTPDBA merge partitions P41,P5 into partition P4;

表分区表空间转移，Oracle自动维护索引分区

--移动分区到特定表空间
alter table T_TESTPDBA move partition p3 tablespace EINVOICEDATA;

全局索引（global index）

　　　　Global索引可以选择是否分区，如果分区，只能是有前缀的分区索引。Oracle不自动维护表的索引信息；

有前缀(prefix)的分区索引和无前缀(nonprefix)的分区索引的区别

（1）有前缀的分区索引指包含了分区键，并且将其作为引导列的索引。

create index IX_TESTPDBA_ID_GLOBAL on t_Testpdba(time) global --time是引导列
partition by range(time)　　　　　　　　　　　　　　　　　　　　　　--time分区列
(
  partition p1 values less than(to_date('2019-01-1', 'yyyy-mm-dd')),
  partition p2 values less than(maxvalue)
);

（2）无前缀的分区索引的列不是以分区键开头，或者不包含分区键列。

create index IX_TESTPDBA_ID_GLOBAL on t_Testpdba(id) global  --id是索引列
partition by range(time)　　　　　　　　　　　　　　　　　　　　　　--time分区列
(
  partition p1 values less than(to_date('2019-01-1', 'yyyy-mm-dd')),
  partition p2 values less than(maxvalue)
);

     全局分区索引不支持非前缀的分区索引，如果创建，系统会提示GLOBAL 分区索引必须加上前缀

全局索引注意事项

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

（2）全局索引可以依附于分区表；也可以依附于非分区表。

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

（4）全局分区索引只按范围或者散列分区。

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

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

全局索引重构

　　Oracle 会自动维护分区索引，对于全局索引，如果在对分区表操作时，没有指定update index，则会导致全局索引失效，需要重建。对于分区索引，不能整体进行重建，只能对单个分区进行重建。

语法如下：

Alter index idx_name rebuild partition index_partition_name [online nologging]

语法说明：

　　（1）online:表示重建的时候不会锁表。

　　（2）nologging：表示建立索引的时候不生成日志，加快速度。

使用举例：

Alter index IX_TESTPDBA_ID_GLOBAL rebuild partition p3 online nologging;

索引查询使用

　　正常情况下，我们只需要在查询条件中带上索引字段，该查询语句便可以走索引查询而不是全表扫描，使用事例如下：

SELECT COUNT(0)
FROM "T_TESTPDBA" "T0"
WHERE "T0"."TIME" >= to_date('2016/11/1 0:00:00', 'yyyy-MM-dd hh24:mi:ss') 
       AND "T0"."TIME" <= to_date('2017/1/13 23:59:59', 'yyyy-MM-dd hh24:mi:ss') AND "T0"."STATUE" = 12

索引失效　　

　　可是在Oracle中，如果查询的结果内容过多，Oracle便不会走索引查询，也就是索引失效，一般索引失效的情况如下：

(1) 没有查询条件，或者查询条件没有建立索引 ；
(2) 在查询条件上没有使用引导列 ；
(3) 查询的数量是大表的大部分，应该是30％以上；
(4) 索引本身失效；
(5) 查询条件使用函数在索引列上；
(6) 统计数据不真实 ；
(7) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小；
(8) 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20), 但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
　　错误的例子：select * from test where tu_mdn=13333333333;
　　正确的例子：select * from test where tu_mdn='13333333333';
(9) 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
　　错误的例子：select * from test where id-1=9;
　　正确的例子：select * from test where id=10;
(10)使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.
　　错误的例子：select * from test where round(id)=10; 说明此时id的索引已经不起作用了。
　　正确的例子：首先建立函数索引，create index test_id_fbi_idx on test(round(id));
　　然后 select * from test where round(id)=10; 这时函数索引起作用了；
(11)查询中使用<> 、单独的>、<、like "%_" 百分号在前；
(12)B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走；
(13)单独引用复合索引里非第一位置的索引列；

强制使用索引查询

　　可是有时候，我们查询希望走索引，那么就需要使用+INDEX，使用举例如下

SELECT /*+INDEX(T0 IX_TESTPDBA_TIME_LOCAL)*/  COUNT(0)
FROM "T_TESTPDBA" "T0"
WHERE "T0"."TIME" >= to_date('2017/1/1 0:00:00', 'yyyy-MM-dd hh24:mi:ss') 
         AND "T0"."TIME" <= to_date('2017/1/13 23:59:59', 'yyyy-MM-dd hh24:mi:ss') AND "T0"."STATUE" = 12

注意/*+INDEX(T0 IX_TESTPDBA_TIME_LOCAL)*/  前后有空格，T0是表的别名，IX_TESTPDBA_TIME_LOCAL指定查询要走的索引名称。