Prior to Oracle 9i version, if the leading column of an index wasn’t specified in a predicate, the index was effectively ignored by the CBO (Cost Based Optimizer). A composite index could only be used if the first column, the leading edge, of the index was referenced in the WHERE clause of a statement.
Index Skip Scan can be imposed on composite index in the certain scenarios only. Composite index is otherwise called as concatenated index or multi-column index. Composite (Concatenated) index means creating an index on more than one column.
However, if the leading column isn’t referenced now, Oracle can use the index anyways via an Index Skip Scan access path (This is applicable from Oracle 9i version onwards). So, Index Skip Scan means, if a composite index is created on 2 columns where the number of distinct values in the first column is very less and the number of distinct values in the second column is very high, and if only the second column is mentioned in the WHERE clause, then there is a high possibility that Oracle will follow Index Skip Scan on this index for that respective SQL.
CREATE TABLE SAMPLE_SS as
with generator as (
select
rownum id
from dual
connect by
level <= 2000
)
select
rownum id,
mod(rownum,2) modded, --- less Unique values
dbms_random.string('A',10) name --- Unique Values
from
generator v1,
generator v2
where
rownum <= 2000;
BEGIN
dbms_stats.Gather_table_stats('HR','SAMPLE_SS',method_opt=>'for all columns size skewonly');
END;
CREATE INDEX SS_IX_NAME on SAMPLE_SS(modded,NAME);
Select s.modded,s.name from sample_ss s where s.name = 'XHvjuUqWtH';
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 | 00:00:01 |
| * 1 | INDEX SKIP SCAN | SS_IX_NAME | 1 | 14 | 3 | 00:00:01 |
-------------------------------------------------------------------------
Select /*+ INDEX_SS(S SS_IX_NAME) */* from sample_ss s where s.modded = 1 order by s.name;
--------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 3600 | 177 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | SAMPLE_SS | 200 | 3600 | 177 | 00:00:01 |
| * 2 | INDEX SKIP SCAN | SS_IX_NAME | 200 | | 8 | 00:00:01 |
The reason why i included an order by clause in my second query was to try and show how and index scan can reduce the cost of a query provided we efficiently choose an column to do the sorting.
I have seen many, using an order by on non-indexed column there by consuming a lot of cost and cpu utilization in the sorting.
Like if you try doing something like below:
Select /*+ INDEX_SS(S SS_IX_NAME) */* from sample_ss s where s.modded = 1 order by s.id;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 3600 | 178 | 00:00:01 |
| 1 | SORT ORDER BY | | 200 | 3600 | 178 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | SAMPLE_SS | 200 | 3600 | 177 | 00:00:01 |
| * 3 | INDEX SKIP SCAN | SS_IX_NAME | 200 | | 8 | 00:00:01 |
----------------------------------------------------------------------------------------------
Please note, i have used an hint to force INDEX_SS scan however FTS would be a better option in above two scenario, but because i wanted to show the impact of index on order by clause i did it. The same is valid for any index scan.
Next read parallel_index