oracle 索引掃描類型的分類與構造

標籤：stat   comm   create   size   _id   trace   exe   aaa   order by   

1. INDEX RANGE SCAN
--請記住這個INDEX RANGE SCAN掃描方式
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
exec dbms_stats.gather_table_stats(ownname => ‘LJB‘,tabname => ‘T‘,estimate_percent => 10,method_opt=> ‘for all indexed
columns‘,cascade=>TRUE) ;
select * from t where object_id=8;

2. INDEX UNIQUE SCAN
--請注意這個INDEX UNIQUE SCAN掃描方式,在唯一索引情況下使用。
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create unique index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;

3. TABLE ACCESS BY USER ROWID
--請注意這個TABLE ACCESS BY USER ROWID掃描方式,直接根據rowid來訪問，最快的訪問方式！
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
--注意，這裡連索引都沒建!
--create  index idx_object_id on t(object_id);
set autotrace off
select rowid from t where object_id=8;
ROWID
-----
AAAZxiAAGAAAB07AAH
set autotrace traceonly
set linesize 1000
select * from t where object_id=8 and rowid=‘AAAZxiAAGAAAB07AAH‘;

4. INDEX FULL SCAN
--請記住這個INDEX FULL SCAN掃描方式，並體會與INDEX FAST FULL SCAN的區別
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select * from t  order by object_id;

5. INDEX FAST FULL SCAN
---請記住這個INDEX FAST FULL SCAN掃描方式，並體會與INDEX FULL SCAN的區別
drop table t purge;
create table t as select * from dba_objects ;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select count(*) from t;

6. INDEX FULL SCAN (MINMAX)
--請注意這個INDEX FULL SCAN (MIN/MAX)掃描方式
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select max(object_id) from t;

7. INDEX SKIP SCAN
--請記住這個INDEX SKIP SCAN掃描方式
drop table t purge;
create table t as select * from dba_objects;
update t set object_type=‘TABLE‘ ;
commit;
update t set object_type=‘VIEW‘ where rownum<=30000;
commit;
create  index idx_type_id on t(object_type,object_id);
exec dbms_stats.gather_table_stats(ownname => ‘LJB‘,tabname => ‘T‘,estimate_percent => 10,method_opt=> ‘for all indexed
columns‘,cascade=>TRUE) ;
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;

8. TABLE ACCESS BY INDEX ROWID
--好好地體會前後兩個實驗，記住這個TABLE ACCESS BY INDEX ROWID
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);
set autotrace traceonly explain
set linesize 1000
select object_id from t where object_id=2 and object_type=‘TABLE‘;
--在接下來的實驗中，你會看到，哇塞，TABLE ACCESS BY INDEX ROWID消失了。
create  index idx_id_type on t(object_id,object_type);
select object_id from t where object_id=2 and object_type=‘TABLE‘;

 

oracle 索引掃描類型的分類與構造