Friday, 27 September 2013

Index range scan vs index skip scan vs index fast full scan

Index range scan vs index skip scan vs index fast full scan

I have table test_A(id1 number,id2 number,id3 number,name
varchar2(10),create_dt date), I have two indexes one composite index indx1
on (id1,id2) and indx2(id3). Now when I query this table test_A as
select * from test_A where id2=123 and create_dt=(select max(create_dt)
from test_A where test_A.id2=id2);
I ran explain plan for this above SQL and it is using "index skip scan".
If I create another index on create_dt then it using index fast full scan
and over all cost and %cpu is showing higher than plan with Index skip
scan. It is also using Index range scan after creating index on create_dt.
I could not come to conclusion which should be could? Do I need to create
another Index on Create_dt or is Index skip scan good? I believe Index
skip is a feature of Oracle to run multiple index range scan?

No comments:

Post a Comment