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')
Oracle Database Internal Mechanism 
Hi,
I’ve been dealing with a few queries on a production environment. Its costs and execution time are very high, However, I realized the fact that the skip scan doesn’t help in those queries, actually are the main problem due to the poor selectivity of the chosen columns, Here are my findings:
Plan hash value: 1440992606
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 256K| 52M| | 12M (1)| 08:31:47 |
|* 1 | HASH JOIN | | 256K| 52M| 402M| 12M (1)| 08:31:47 |
|* 2 | HASH JOIN RIGHT SEMI | | 4023K| 356M| | 253K (4)| 00:10:48 |
|* 3 | TABLE ACCESS FULL | BDC | 17 | 272 | | 12 (9)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TMC | 5120K| 376M| | 253K (4)| 00:10:47 |
|* 5 | TABLE ACCESS BY INDEX ROWID| PRQ | 327K| 38M| | 11M (1)| 08:20:04 |
|* 6 | INDEX SKIP SCAN | PRQ_IDX10 | 73M| | | 398K (2)| 00:16:58 |
Elapsed: 00:02:34.33
Execution Plan
Plan hash value: 27315579
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 256K| 52M| | 1579K (5)| 01:07:12 |
|* 1 | HASH JOIN | | 256K| 52M| 41M| 1579K (5)| 01:07:12 |
|* 2 | TABLE ACCESS FULL | PRQ | 327K| 38M| | 1303K (5)| 00:55:28 |
|* 3 | HASH JOIN RIGHT SEMI| | 4023K| 356M| | 253K (4)| 00:10:48 |
|* 4 | TABLE ACCESS FULL | BDC | 17 | 272 | | 12 (9)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | TMC | 5120K| 376M| | 253K (4)| 00:10:47 |
LikeLike
Index skip scan not happening in my case DB 11G
What would be the reason?
Query:-
explain plan for
select * from employees
where first_name=’Raphaely’ ;
Execution Plan:-
Plan hash value: 1445457117
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 – filter(“FIRST_NAME”=’Raphaely’)
LikeLike