Oracle之索引三大特征

索引黄金三大特征:

1.索引的高度较低

索引高度较低的学习：

随着数据的不断增多，从底层的block块开始，如果存储满了会向上保存目录，但是向上增长缓慢，查询数据的以后从最上级开始向下查询，一层一个io，大量数据主要在吞吐量缓慢。

测试：可以建立表插入数据，然后建立索引，然后用hitns进行全表扫描，查看执行计划看consistent gets数量。

例子：

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
drop table t4 purge;
drop table t5 purge;
drop table t6 purge;
drop table t7 purge;


create table t1 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1;
create table t2 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10;
create table t3 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100;
create table t4 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000;
create table t5 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10000;
create table t6 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100000;
create table t7 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000000;




create index idx_id_t1 on t1(id);
create index idx_id_t2 on t2(id);
create index idx_id_t3 on t3(id);
create index idx_id_t4 on t4(id);
create index idx_id_t5 on t5(id);
create index idx_id_t6 on t6(id);
create index idx_id_t7 on t7(id);


set linesize 1000
set autotrace off
select index_name,
          blevel,
          leaf_blocks,
          num_rows,
          distinct_keys,
          clustering_factor
     from user_ind_statistics
    where table_name in( 'T1','T2','T3','T4','T5','T6','T7');

2.存储列值：列值+rowid

3.本身有序

例子：优化count(*),如果列值存在null那么索引将失效。在列值上加上索引，count(*)会走索引。

补充：sum等也会走索引

例子：select id from t order by id; 查看执行计划不会产生sorts(memory)，因为索引本身就是排序的

例子：select max(id) from t;速度异常快，因为不论表的数据如何增加，max min是在固定位置的，所以查询很快。

例子：union无法优化，索引无法消除union排序，一般来说使用union存在必要性，在数据不会重复时候用union all

例子：索引之排序

drop table t purge;
create table t as select * from dba_objects ;
set autotrace traceonly
--oracle还算智能，不会傻到这里都去排序，做了查询转换，忽略了这个排序
select count(*) from t order by object_id;

 ---以下语句说明排序
set autotrace traceonly
set linesize 1000
drop table t purge;
create table t as select * from dba_objects;

--以下语句没有索引又有order by ，必然产生排序
select * from t where object_id>2 order by object_id;

---新增索引后，Oracle就有可能利用索引本身就有序的特点，利用索引来避免排序，如下：
create index idx_t_object_id on t(object_id);
set autotrace traceonly


select * from t where object_id>2 order by object_id;

--如下情况Oracle肯定毫不犹豫的选择用索引,因为回表取消了 !      
select  object_id from t where object_id>2 order by object_id;

例子：索引值max/min查询

--MAX/MIN 的索引优化
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
alter table t add constraint pk_object_id primary key (OBJECT_ID);
set autotrace on
set linesize 1000


select max(object_id) from t;

--最小值老师的试验就无需展现执行计划结果了，必然和最大值的执行计划一样！          
select min(object_id) from t;


--如果没用到索引的情况是如下,请看看执行计划有何不同，请看看代价和逻辑读的差异！
select /*+full(t)*/ max(object_id) from t;

---另外，可以做如下试验观察在有索引的情况下，随这记录数增加，性能差异是否明显？
set autotrace off
drop table t_max purge;
create table t_max as select * from dba_objects;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
select count(*) from t_max;
create index idx_t_max_obj on t_max(object_id);
set autotrace on 
select max(object_id) from t_max;

补充知识：

INDEX FULL SCAN (MIN/MAX)

TABLE ACCESS BY INDEX ROWID（回表）