See
</code>
http://www.oracle.com/pls/db901/db901.to_toc?pathname=server.901/a87503/optimops.htm#51553 <code>
it works well if and only if the leading edge of the index (a in the example above) has very few discrete values and the optimizer understands that. For example and index on (GENDER,EMPNO) where gender has the values 'M', 'F' and EMPNO is unique. A query such as:
select * from t where empno = 5;
might consider (in 9i) using that index on t to satisfy the query in a "skip scan" method -- meaning the query will be processed conceptually like this:
select * from t where GENDER='M' and empno = 5
UNION ALL
select * from t where GENDER='F' and empno = 5;
In order to demonstrate this, I used the following script:
rop table t;
create table t
as
select decode(mod(rownum,2), 0, 'M', 'F' ) gender, all_objects.*
from all_objects
/
create index t_idx on t(gender,object_id)
/
analyze table t compute statistics
for table
for all indexes
for all indexed columns
/
alter session set sql_trace=true;
select * from t t1 where object_id = 55;
alter session set sql_trace=false;
drop table t;
create table t
as
select chr(mod(rownum,256)) gender, all_objects.*
from all_objects
/
create index t_idx on t(gender,object_id)
/
analyze table t compute statistics
for table
for all indexes
for all indexed columns
/
alter session set sql_trace=true;
select * from t t2 where object_id = 55;
alter session set sql_trace=false;
When the optimizer understands there are a few "sub indexes" by gender (as in the first case), it will use a skip scan:
select *
from
t t1 where object_id = 55
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 7 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T
1 INDEX SKIP SCAN (object id 19845)
In the second case, there were 256 "sub indexes" and the optimizer properly adjusted its query plan:
select *
from
t t2 where object_id = 55
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.03 1 266 9 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.04 1 266 9 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T
As you can see -- in this case -- the skip scan did lots less work...