Index Skip Scans

An index skip scan occurs when the initial column of a composite index is “skipped” or not specified in the query. Basically skip scanning lets a composite index be split logically into smaller sub-indexes, making every sub-index a viable candidate to be used by optimizer to come up with an efficient plan. The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

Often, skip scanning index blocks is faster than scanning table blocks, and faster than performing full index scans.

Following are the situations where optimizer would choose skip scanning:-

  • The leading column of a composite index is not specified in the query predicate.
  • Few distinct values exist in the leading column of the composite index, but many distinct values exist in the nonleading key of the index.

 

SQL_ID  d7a6xurcnx2dj, child number 0
-------------------------------------
SELECT * FROM   sh.customers WHERE  cust_email = 'nitish@example.com'

Plan hash value: 797907791

-----------------------------------------------------------------------------------------
|Id| Operation                          | Name               |Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------------
| 0|SELECT STATEMENT                    |                      |  |    |10(100)|        |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS            |33|6237|  10(0)|00:00:01|
|*2|  INDEX SKIP SCAN                   | CUST_GENDER_EMAIL_IX |33|    |   4(0)|00:00:01|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUST_EMAIL"='nitish@example.com')
       filter("CUST_EMAIL"='nitish@example.com')

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s