INDEX SCAN TYPES ORACLE
Access path in oracle is basically retrieving rows from row source be it table, index or any other objects etc..
Types of access paths:
- Full table scan
- Table access by rowid
- Index unique scan
- Index range scan
- Index full scan
- Index fast full scan
- Index skip scan
- Bitmap index single value
- Bitmap index range scan
- Index join scan
- Sample table scan
- Bitmap merge
- Cluster scan
- Hash scan
Confusions: Often there is a confusion between index full scan and index fast full scan
Index unique scan:
An index unique scan is used from the predicate whenever an indexed column contains unique key or primary key constraint.
This type of scan return always one unique row . The scan starts from root block to leaf block with a rowid and that rowid is used to retrieve datablock from the datafile.
No. of block accesses = Height of index + 1
Exception for above formula is row chaining blocks and LOB tables
In below plan, there are two steps
- Index unique scan – Index leaf block touch(single index block read with rowid)
- Table access by index rowid – datablock touch from datafile with the help of previous index rowid
SQL> set autot traceonly explain
SQL> select DEPARTMENT_NAME from DEPARTMENTS where DEPARTMENT_ID=270;
Execution Plan
----------------------------------------------------------
Plan hash value: 4024094692
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)|
00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)|
00:00:01 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=270)
Index range scan:
Index range scan is basically slicing the index based on a range based value specified on the predicate. The index can be unique or non unique and single or multiple rows are returned based on the predicate condition .If the range is large, then index full scan operation will be attracted rather than range scan.
Index is traversed from root block to leaf block matching a predicate condition. Here if you see there are two steps
- Table access by index rowid batched
- Index range scan
Here we can calculate the block accesses using this formula
No. of block accesses = (Returned rows which match the condition * 2 ) + blevel
Example: If there are 3 rows returned for a query, then server process retrieve the index leaf blocks from root blocks based on predicate condition,fetch the rowids consecutively back & forth and read the datablocks from the datafile. So data blocks are accessed twice both from index rowid and datablocks provided the range entries are not large.
If the range of entries are large for an index , then more than one leaf block should be scanned with the pointer. Index range scan will not occur when ‘like’ operator is used like ‘%xyz’ which scans the index in broader range from upper to lower boundary.
Conditions used – >,<,LIKE,BETWEEN , = and non unique index
SQL> select * from DEPARTMENTS where LOCATION_ID=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 2156672468
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 |
2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS | 1 | 21 |
2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | |
1 (0)| 00:00:01 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LOCATION_ID"=10)
In index range scan, if we can also use order by clause to sort the data using index rowid in ascending (default) order without extra processing for sort. The index entrires can also be used to sort the data in the descending order. We can also see an inlist iterator step in the plan which occur due to ‘in’ clause.
kish@exdbx<>set autot traceonly explain
kish@exdbx<>select * from tabx
2 where order_id in (33,36,39)
3 order by order_id;
Elapsed: 00:00:00.86
Execution Plan
----------------------------------------------------------
Plan hash value: 3742152234
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 228 | 6 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TABX | 3 | 228 | 6 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_ID | 3 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ORDER_ID"=33 OR "ORDER_ID"=36 OR "ORDER_ID"=39)
========================================================================
kish@exdbx<>select * from tabx
2 where order_id in (33,36,39)
3 order by order_id desc;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1806198194
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 228 | 6 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TABX | 3 | 228 | 6 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN DESCENDING| IDX_ID | 3 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ORDER_ID"=33 OR "ORDER_ID"=36 OR "ORDER_ID"=39)
Index full scan:
If the rows are scanned in the index leaf blocks in an order and eliminate the need for sorting,then this scan takes place. Access of all index blocks will be more efficient than accessing all table blocks.This scan uses single block read to fetch the rows. Prevents sorting operations.If there is no not null constraint, then index full scan cannot be chosen.It occur when all columns used in query are indexed and atleast one column in index has NOT NULL constraints.
- This may also happen when there is no predicate in the query and indexed column is used after select
- the predicate contains a condition on a non leading column in an index
- An order by clause used on non-nullable indexed columns
Index full scan = (Predicate contain atleast one not null constraint column + Other indexed columns)
SQL> select DEPARTMENT_ID from DEPARTMENTS;
Execution Plan
----------------------------------------------------------
Plan hash value: 3963224445
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 108 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 108 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------
========================================================================
SQL> alter table employees modify (email null);
Table altered.
SQL> select email from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 856 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------
SQL> alter table employees modify (email not null);
Table altered.
SQL> select email from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196514524
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 1 (0)| 00:00:01
|
| 1 | INDEX FULL SCAN | EMP_EMAIL_UK | 107 | 856 | 1 (0)| 00:00:01
|
------------------------------------------------------------------------
Index full scan(min/max):
If we want to retrieve either min or max value from an index, then the column which is indexed is used with a index full scan.
SQL> select min(department_id) from DEPARTMENTS;
Execution Plan
----------------------------------------------------------
Plan hash value: 2336069222
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 0
0:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
|
| 2 | INDEX FULL SCAN (MIN/MAX)| DEPT_ID_PK | 1 | 4 | 1 (0)| 0
0:00:01 |
-----------------------------------------------------------------------Below is an example for index full scan descending in a sorted order
SQL> select * from DEPARTMENTS order by DEPARTMENT_ID desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 2565564082
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 567 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 567 | 2 (0)|
00:00:01 |
| 2 | INDEX FULL SCAN DESCENDING| DEPT_ID_PK | 27 | | 1 (0)|
00:00:01 |
------------------------------------------------------------------------
Index skip scan:
An index skip scan is used when a predicate contains a non leading column and leading column has high cardinality. This scan works by splitting multiple column index into smaller subindexes. A number of logical subindexes determined by number of distinct values in leading columns of index. If leading column has more distinct values , then more logical subindexes should be created. If the number of subindexes required is less, then index skip scan would be more efficient than full scan (scanning index block is more efficient than scanning table blocks). But if the number of subindexes required is larger, then full scan is efficient than index scan.
Index skip scan = (distinct leading column + predicate with non leading column)
SQL> create index emp_idx on employees(EMPLOYEE_ID,FIRST_NAME,SALARY);
Index created.
SQL> select * from employees where FIRST_NAME='William';
Execution Plan
----------------------------------------------------------
Plan hash value: 3789230693
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 72 | 2
(0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | EMP_IDX | 1 | | 1
(0)| 00:00:01 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FIRST_NAME"='William')
filter("FIRST_NAME"='William')Index fast full scan:
This is also similar to full table scan which uses multiblock reads. The data is accessed from the index itself rather than table blocks because optimizer thinks that index size is smaller than table size and all the rows are contained in index itself. Root and branch blocks are not read but leaf blocks are read directly.This scan cant be used to avoid a sort because the blocks are read using unordered multiblock reads. This operation doesnot require not-null constraint or indexed column like a index full scan. Count of records triggers index fast full scan.
Index fast full scan = (count of total records)
SQL> set autot traceonly explain
SQL> select /*+ Index_ffs(employees) */ FIRST_NAME,LAST_NAME from test.employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1847877723
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 1605 | 2 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 1605 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Situations when optimizer skips index scan:
- No index created on the columns
- Index is present but cannot be utilized due to 1)Functions applied on the column 2)IS NULL operator is used when an indexed column contains null value
- low selectivity
- Table size is assumed as small but its really big due to missing statistics
