Classification and construction of Oracle Index Scan type

1. INDEX RANGE SCAN
--Please remember this index RANGE scan scanning method
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
--Please note that this index unique scan method is used in the case of a unique index.
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
--Please note that this table access by USER rowID scan method, directly according to rowID to access, the fastest way to access!
drop table T Purge;
CREATE TABLE T as select * from Dba_objects;
Update T set object_id=rownum;
Commit
--Note that the index is not even built here!
--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
--Remember this index full scan mode and experience the difference from 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
---Remember this index FAST full scan mode and experience the difference from 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)
--Please note that this INDEX full scan (Min/max) Scan method
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-
select Max (object_id) from T;

7. Index Skip Scan
--Please remember this index skip scan mode
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-
select * from T where object_id=8;

8. TABLE ACCESS by INDEX ROWID
--Have a good understanding of the two experiments before and after, remember this 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 ';
In the next experiment, you'll see, wow, TABLE ACCESS by INDEX rowID disappears.
CREATE index Idx_id_type on T (Object_id,object_type);
Select object_id from T where object_id=2 and object_type= ' TABLE ';

Classification and construction of Oracle Index Scan type