Oracle skip index scan test

Oracle skip index scan test

In Oracle, we know that we can use the Index Skip Scan. However, there are some limitations when we can use the Skip Index Scan.
Create table test as select rownum a, ROWNUM-1 B, ROWNUM-2 c, ROWNUM-3 d, ROWNUM-4 e FROM all_objects;

SQL> create table test as select rownum a, ROWNUM-1 B, ROWNUM-2 c, ROWNUM-3 d, ROWNUM-4 e FROM all_objects;

Table created.

Elapsed: 00:00:02. 78
SQL> desc test;
Name Null? Type
-------------------------------------------------------------------------------------------------
A NUMBER
B NUMBER
C NUMBER
D NUMBER
E NUMBER

Select distinct count (a) FROM test;
SQL> SELECT DISTINCT COUNT (a) FROM test;

COUNT ()
----------
84394

 

Create index test_idx ON test (a, B, c );

SQL> create index test_idx on test (a, B, c );

Index created.

Analyze table test compute statistics;
SET autotrace traceonly explain;
SELECT * FROM test WHERE B = 99;

SQL> analyze table test compute statistics;

Table analyzed.

Elapsed: 00:00:02. 46
SQL> set autotrace traceonly explain;
SQL> select * from test where B = 99;
Elapsed: 00:00:00. 00

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 1 | 20 | 96 (2) | 00:00:02 |
| * 1 | table access full | TEST | 1 | 20 | 96 (2) | 00:00:02 |
--------------------------------------------------------------------------

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

1-filter ("B" = 99)

-The CBO selects full table scan.
Let's start another test.
Drop table test;
Create table test as select decode (MOD (ROWNUM, 2), 0, '1', '2') a, ROWNUM-1 B, ROWNUM-2 c, ROWNUM-3 d, ROWNUM-4 e FROM all_objects
Set autotrace off
Select distinct a from test;
SQL> select distinct a from test;

A
-
1
2

Elapsed: 00:00:00. 08

Create index test_idx ON test (a, B, c)
 
SQL> SELECT * FROM test WHERE B = 99;
Elapsed: 00:00:00. 01

Execution Plan
----------------------------------------------------------
Plan hash value: 2705879578

Bytes ----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------
| 0 | select statement | 1 | 17 | 4 (0) | 00:00:01 |
| 1 | table access by index rowid | TEST | 1 | 17 | 4 (0) | 00:00:01 |
| * 2 | index skip scan | TEST_IDX | 1 | 3 (0) | 00:00:01 |
Bytes ----------------------------------------------------------------------------------------

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

2-access ("B" = 99)
Filter ("B" = 99)

Conclusion:
The Oracle optimizer (CBO here) has at least a few conditions for querying the Index Skip Scans of an application:

1. The optimizer considers it appropriate.
2. The number of unique values of the leading column in the index can meet certain conditions.
3. The optimizer needs to know the value distribution of the leading column (obtained through analysis/Statistical table)
4. Suitable SQL statements