Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

 

Oracle Index skip scans

 

The index skip scan is a new execution plan in Oracle whereby an Oracle query can bypass the leading-edge of a concatenated index and access the inside keys of a multi-values index. For example, consider the following concatenated index:

 

create index
   sex_emp_id
on
   emp (sex, emp_id)
;

 

 

Prior to Oracle9i, this index could only be used with both sex and emp_id were present in the SQL query, or when the sex column was specified.  The following query would not be able to use the concatenated index:

 

select
   emp_id
from
   emp
where
   emp_id = 123;

 

The Oracle skip scan execution plan allows for the concatenated index to be used, even though sex is not specified in the SQL query.  This feature promises that there is no need to provide a second index on the emp_id column.  Oracle acknowledges that the index skip scan is not as fast as a direct index lookup, bit states that the index skip scan is faster than a full-table scan.

 

What Oracle does not mention is that the cardinality of the leading column has a direct impact on the speed of the index skip scan.  In our example, the first column, sex  has two columns (three if you count eunuchs or those who have recently visited Sweden for surgical procedures).

 

While Oracle does not publish the internals of the index skip scan, we can infer from the execution plans that Oracle is internally generating multiple queries, thereby satisfying the query with multiple sub-queries:

 

SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)

   0 SORT (AGGREGATE)

        1 INDEX (SKIP SCAN) OF 'SEX_EMP_ID' (NON-UNIQUE)

 

 

Internally, Oracle is probably generating two queries and joining the resulting Row ID lists:

 

select emp_name from emp_where sex = ‘F’ and emp_id = 123
UNION
select emp_name from emp_where sex = ‘M’ and emp_id = 123;

 

 

The implications of using the index skip scan are clear:

 

  1. Oracle skip scan execution plan performance will decrease according to the number of unique values in the high order key.   If the leading column were “state” with 50 values, Oracle would be issuing 50 index probes to retrieve the result set.

 

  1. The index skip scan is only useful in shops where disk space savings are critical.  Shops that can afford the disk space to build a second index will always get faster performance.

 

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.