Oracle索引

1、B-Tree索引（NORMAL）

Oracle会自动为表的主键列建立索引，这个默认的索引是普通的B-Tree索引。索引默认按照升序存储列值。普通索引适合检索区分度高的数据列。

create index IDX_T_USER_NAME on T_USER (USER_NAME);
ALTER INDEX idx_t_user_name REBUILD TABLESPACE indexdata;
create index IDX_T_USER_NAME on T_USER (USER_NAME)  TABLESPACE indexdata;

2、位图索引（BITMAP）

适合低区分度的列（即列的唯一值除以行数为一个很小的值，接近0）

CREATE BITMAP INDEX idx_t_user_sex ON t_user(user_sex);

3、函数索引（FUNCTION-BASED NORMAL）

CREATE INDEX idx_t_user_titleid ON t_user(SUBSTR(titleid,9,6)) TABLESPACE indexdata;

4、反向索引（NORMAL/REV）

使用数据时（常见于批量插入操作）都比较集中在一个连续的数据范围内，那么在使用正常的索引时就很容易发生索引叶子块过热的现象。反向索引可以降低索引叶子块的争用，减少热点块。

CREATE INDEX idx_t_user_id ON t_user(user_id) REVERSE TABLESPACE indexdata;

5、降序索引（FUNCTION-BASED NORMAL）

是B-Tree的一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序。在oracle索引视图中查看到索引类型是FUNCTION-BASED NORMAL。

CREATE INDEX idx_emp1_sal ON emp(sal DESC) TABLESPACE indexdata;

6、分区索引

分区索引包括全局索引(global index)和本地索引(local index)。全局索引包括全局分区索引(global partitioned index)、全局非分区索引(global non-partitioned index，即普通索引)。

全局索引只有一类，即全局前缀索引（prefixed global index），索引分区键必须作为索引定义的第一列。
一般情况下，大多数分区操作（如删除一个旧分区）都会使全局索引无效，除非重建全局索引，否则无法使用。

本地索引可以有两类，即Local prefixed index（局部前缀索引）、Local nonprefixed index（局部非前缀索引）。
局部前缀索引：以分区键作为索引定义的第一列。
局部非前缀索引：分区键没有作为索引定义的第一列。
局部分区索引随表对索引完成相应的分区（即索引会使用与底层表相同的机制分区），每个表分区都有一个索引分区，并且只索引该表分区。
局部索引的唯一性，Oracle只保证索引分区内部的唯一性，跨分区的唯一性无法保证。

--本地索引
create index IDX_PART_TAB_AREA on RANGE_PART_TAB (AREA_CODE) LOCAL;

--全局索引
create index IDX_PART_TAB_DATE on RANGE_PART_TAB (DEAL_DATE);

总结：

1、索引可以像表一样能够被分区，分区或非分区索引都能够使用在分区或非分区表上。

2、查看索引

SELECT * FROM user_segments WHERE segment_type='INDEX';
SELECT * FROM User_Ind_Columns WHERE table_name='T_USER';
--查看索引信息
SELECT * FROM user_indexes WHERE index_name IN ('IDX_PART_TAB_DATE','IDX_PART_TAB_AREA');
--查看分区索引，比如查看本地索引
SELECT * FROM user_part_indexes WHERE index_name='IDX_PART_TAB_AREA';
--查看索引的分区信息
SELECT * FROM User_Ind_Partitions WHERE index_name='IDX_PART_TAB_AREA';

3、查看统计信息

--查看T_USER表的统计信息
select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t WHERE t.table_name='T_USER';
--查看T_USER表上索引的统计信息
select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from user_indexes t WHERE t.TABLE_NAME='T_USER';
--收集表和索引统计信息
exec dbms_stats.gather_table_stats(ownname => 'TEST04',tabname => 'T_USER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);