Conditions for index range scan, index fast full scan, and index skip Scan

Index range scan ):

1. for unique index, if the where condition is followed by <,>,... and... if the where condition is followed by =, then index unique scan may be executed.

2. For none unique index, if the where condition is followed by =,>, <, betweed... And..., index range scan may be executed.

3. For composite indexes, if the bootstrap column of the composite index appears after the where condition, index range scan may be executed.

 

Index fast full scan (index fast full scan ):

If the columns after the SELECT statement are included in the composite index, And the WHERE clause does not contain the bootstrap column of the composite index, You must retrieve most of the data, in this case, index fast full scan may be executed. Conditions for index fast full scan:

1. It must be a composite index. 2. The bootstrap column is not in the where condition.

 

Index skip scan (index skip scan)

When the query results can be obtained through the combined index and few results are returned, and the where condition does not contain the index guide column, the index skip scan may be executed.

Conditions for index skip scan:

1. It must be a composite index.

2. The bootstrap column does not appear in the where condition.

 

The appeal theory is verified through a simple experiment below:

SQL> Create Table Test as select * From dba_objects;

The table has been created.

SQL> create unique index ind_id on test (object_id); ind_id is the unique index

The index has been created.

SQL> Create index ind_owner on test (owner); ind_owner is a non-unique index

The index has been created.

SQL> Create index OOO on test (owner, object_name, object_type); OOO is a composite index

The index has been created.

SQL> exec dbms_stats.gather_table_stats ('robinson ', 'test ');

The PL/SQL process is successfully completed.

SQL> set autot trace
SQL> select owner from test where object_id = 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 2544773305

Bytes --------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
Bytes --------------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 11 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | test | 1 | 11 | 2 (0) | 00:00:01 |
| * 2 | index unique scan | ind_id | 1 | 1 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------

SQL> select owner from test where object_id <10;

Eight rows have been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1361604213

Bytes --------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
Bytes --------------------------------------------------------------------------------------
| 0 | SELECT statement | 7 | 77 | 3 (0) | 00:00:01 |
| 1 | table access by index rowid | test | 7 | 77 | 3 (0) | 00:00:01 |
| * 2 | index range scan | ind_id | 7 | 2 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------

For a unique index, when index range scan occurs, multiple rows of records are returned, where is followed by >,<, between... and ..

SQL> select owner from test where owner = 'Scott ';

13 rows have been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2280863269

------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
------------------------------------------------------------------------------
| 0 | SELECT statement | 2936 | 17616 | 7 (0) | 00:00:01 |
| * 1 | index range scan | ind_owner | 2936 | 17616 | 7 (0) | 00:00:01 |
------------------------------------------------------------------------------

For non-unique indexes, even if the restriction condition after where is =, multiple rows may be returned, so index range scan is performed.

SQL> select object_name, object_type from test where owner = 'robinson ';

15 rows have been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2845720098

-------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
-------------------------------------------------------------------------
| 0 | SELECT statement | 2936 | 114k | 23 (0) | 00:00:01 |
| * 1 | index range scan | OOO | 2936 | 114k | 23 (0) | 00:00:01 |
-------------------------------------------------------------------------

Because 000 is not a unique index and the index OOO Bootstrap column is used after the WHERE clause, index range scan is performed.

SQL> select owner, object_name, object_type from test where object_name = 'emp ';
Execution Plan
----------------------------------------------------------
Plan hash value: 1799988433

-------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
-------------------------------------------------------------------------
| 0 | SELECT statement | 2 | 80 | 19 (0) | 00:00:01 |
| * 1 | index skip scan | OOO | 2 | 80 | 19 (0) | 00:00:01 |
-------------------------------------------------------------------------

Because the information required for the query can be obtained through the index Ooo, and there is no guide column owner behind the WHERE clause, and the number of returned rows is very small (there is only one row), the CBO selects index skip scan, autotrace does not show how many rows are returned.

SQL> set autot off
SQL> select owner, object_name, object_type from test where object_name = 'emp ';

Owner object_name object_type
---------------------------------------------------------------------
Scott EMP table

SQL> select owner, object_name, object_type from test where object_type = 'index ';

Row 1701 has been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3464522019

-----------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
-----------------------------------------------------------------------------
| 0 | SELECT statement | 1721 | 68840 | 70 (3) | 00:00:01 |
| * 1 | index fast full scan | OOO | 1721 | 68840 | 70 (3) | 00:00:01 |
-----------------------------------------------------------------------------

Because the information required for the query can be obtained through the index Ooo, and there is no guide column owner behind the where, and a large number of rows (1701 rows) are returned, the CBO selects index fastFull scan, which avoids full table scan.