oracle 索引的几种方式

一、查询索引的高度

select index_name,
blevel,
leaf_blocks,
num_rows,
distinct_keys,
clustering_factor
from user_ind_statistics
where table_name in( 'T1','T2','T3');

 2. 索引存储列值（可优化聚合）

2.1索引特性之存列值优化count

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create index idx1_object_id on t(object_id);
set autotrace on
select count(*) from t;

--count无法用到

修改代码让count用到索引
select count(*) from t where object_id is not null;
修改代码让count用到索引

修改代码让count用到索引
alter table t modify OBJECT_ID not null;
select count(*) from t;

2.2主键让count用到索引
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
alter table t add constraint pk1_object_id primary key (OBJECT_ID);
set autotrace on
select count(*) from t;

2.3索引特性之存列值优化sum avg

drop table t purge;
create table t as select * from dba_objects;
create index idx1_object_id on t(object_id);
set autotrace on
set linesize 1000
set timing on
select sum(object_id) from t;

2.4sum avg不走索引的代价

select /*+full(t)*/ sum(object_id) from t;

3 索引本身有序（可优化排序）

3.1索引特性之有序优化order by

set autotrace traceonly
set linesize 1000
drop table t purge;
create table t as select * from dba_objects;
select * from t where object_id>2 order by object_id;

--无索引的order by 语句必然会排序

索引让order by 语句排序消失
create index idx_t_object_id on t(object_id);
set autotrace traceonly
select * from t where object_id>2 order by object_id;

3.2 索引特性之有序优化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;

MAX/MIN 语句用不到索引性能低下
select /*+full(t)*/ max(object_id) from t;

 

3.3 MAX/MIN 用索引与数据量增加的影响

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;

4 组合索引选用

4.1 仅等值无范围查询时，组合的顺序不影响性能

drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
update t set object_id=rownum ;
commit;
create index idx_id_type on t(object_id,object_type);
create index idx_type_id on t(object_type,object_id);
set autotrace off
alter session set statistics_level=all ;
set linesize 366
type_id，id顺序组合索引
select /*+index(t,idx_id_type)*/ * from  t  where object_id=20  and object_type='TABLE';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--id、type_id顺序组合索引
select /*+index(t,idx_type_id)*/ * from  t  where object_id=20  and object_type='TABLE';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

4.2 组合索引最佳顺序一般是将等值查询的列置前

将等值查询的列置前
select /*+index(t,idx_id_type)*/ *  from   t where object_id>=20 and object_id<2000 and object_type='TABLE';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

将等值查询的列置后
select /*+index(t,idx_type_id)*/ *  from  t  where object_id>=20 and object_id<2000   and object_type='TABLE';