Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle index skip scan tips

Oracle Database Tips by Donald BurlesonUpdated May 3, 2015


Question:
 Can you explain the index skip scan? I'm trying to invoke an index skip scan but it's not working. I have a table A with columns (f1,f2,f3), and I made the composite primary key with f1 and f2. Index IND1 got created for the composite primary key by oracle. I am able see that index IND1 being used when I query like this:

1) where f1=

2) where f1= and f2=

But the index IND1 is not being used for the below query

3) where f2= Why does the index not work when I reference the f2 key?

Can you force an index skip scan with a hint?

Answer: The index skip scan was introduced in 9i to allow Oracle to "skip" leading-edge predicates in a multi-column index. You can force an index skip scan with the /*+ index_ss */ hint. For example, consider the following concatenated index on a super-low cardinality column, following by a very selective column:

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 index skip scan execution plan allows for our concatenated index to be used, even though sex is not specified in the SQL query WHERE clause. The index skip scan promises that there is no need to build a second index on the emp_id column, since it is technically redundant to the multi-column index. Oracle acknowledges that the index skip scan is not as fast as a direct index lookup, but 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. 

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 implementing the index skip scan by 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 index skip scan is a new execution plan in Oracle 10g 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 on region and employee ID:

create index
region_emp_id
on
emp (region, emp_id)
;

Prior to Oracle 10g, this index could only be used with both region and emp_id were present in the SQL query, or when the region column was specified.  For example, 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 Oracles 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, region, has four distinct values.

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 'REGION_EMP_ID' (NON-UNIQUE)

Internally, the Oracle index skip scan is probably generating multiple queries and joining the resulting Row ID lists:

select emp_name from emp_where region = 'NORTH' and emp_id = 123
UNION
select emp_name from emp_where region = 'SOUTH' and emp_id = 123
UNION
select emp_name from emp_where region = 'EAST' and emp_id = 123
UNION
select emp_name from emp_where region = 'WEST' and emp_id = 123;

The implications of using the index skip scan are clearly related to the number of distinct values in the leading edge column.

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.

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 Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational