Access paths are ways in which data is retrieved from the database. In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. Online transaction processing (OLTP) applications, which consist of short-running SQL statements with high selectivity, often are characterized by the use of index access paths. Decision support systems, on the other hand, tend to use partitioned tables and perform full scans of the relevant partitions.
The optimizer chooses the access path based on the following factors:
- The available access paths for the statement
- The estimated cost of executing the statement, using each access path or combination of paths
In Index Scan method, a row is retrieved by traversing the index, using the indexed column values specified by the statement. An index scan retrieves data from an index based on the value of one or more columns in the index. To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement. If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.
The index contains not only the indexed value, but also the rowids of rows in the table having that value. Therefore, if the statement accesses other columns in addition to the indexed columns, then Oracle can find the rows in the table by using either a table access by rowid or a cluster scan.
ops$rperumal@PDB10> CREATE TABLE t ( 2 pkc NOT NULL, 3 sv2, 4 cv20, 5 sv200, 6 rsn1, 7 rsn2, 8 rsn3, 9 data, 10 CONSTRAINT t_pk primary key (pkc) 11 ) 12 AS 13 SELECT rownum pkc, 14 mod(rownum, 2)+1 sv2, 15 trunc((rownum-1)/500) cv20, 16 mod(rownum, 200)+1 sv200, 17 rownum rsn1, 18 rownum rsn2, 19 rownum rsn3, 20 lpad(rownum,10,'0') data 21 FROM dual 22 CONNECT BY LEVEL <= 10000; Table created. ops$rperumal@PDB10> CREATE INDEX t_sv2_rsn1 ON t(sv2,rsn1); Index created. ops$rperumal@PDB10> CREATE INDEX t_cv20_rsn2 ON t(cv20,rsn2); Index created. ops$rperumal@PDB10> CREATE INDEX t_sv200_rsn3 ON t(sv200,rsn3); Index created. ops$rperumal@PDB10> CREATE INDEX t_data ON t(data); Index created. ops$rperumal@PDB10> EXECUTE DBMS_STATS.gather_table_stats(USER,'T',cascade=>TRUE); PL/SQL procedure successfully completed. ops$rperumal@PDB10> SET AUTOTRACE TRACEONLY EXPLAIN
Index Unique Scan
This scan returns, at most, a single rowid. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed. This access path is used when all columns of a unique (B-tree) index or an index created as a result of a primary key constraint are specified with equality conditions.
Execution plan using Index Unique Scan below:
ops$rperumal@PDB10> SELECT * FROM t WHERE pkc = 2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1303508680
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 36 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PKC"=2000)
Index Range Scan
An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid. If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.
The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:
- col1 = :b1
- col1 < :b1
- col1 > :b1
- AND combination of the preceding conditions for leading columns in the index
- col1 like ‘ASD%’ wild-card searches should not be in a leading position otherwise the condition col1 like ‘%ASD’ does not result in a range scan. Range scans can use unique or non-unique indexes. Range scans avoid sorting when index columns constitute the ORDER BY/GROUP BY clause.
Execution plan usingIindex Range Scan below:
ops$rperumal@PDB10> SELECT * FROM t WHERE pkc < 200;
Execution Plan
----------------------------------------------------------
Plan hash value: 3772518221
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 199 | 7164 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 199 | 7164 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_PK | 199 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PKC"<200)
ops$rperumal@PDB10> SELECT * FROM t WHERE pkc > 9900;
Execution Plan
----------------------------------------------------------
Plan hash value: 3772518221
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3600 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 3600 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_PK | 100 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PKC">9900)
ops$rperumal@PDB10> SELECT * FROM t WHERE pkc BETWEEN 2000 AND 3000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3772518221
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1002 | 36072 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1002 | 36072 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_PK | 1002 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PKC">=2000 AND "PKC"<=3000)
ops$rperumal@PDB10> SELECT * FROM t WHERE data = '0000000001';
Execution Plan
----------------------------------------------------------
Plan hash value: 3782328437
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 36 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_DATA | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA"='0000000001')
Index Range Scan Descending
An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order. Usually, this scan is used when ordering data in a descending order to return the most recent data first, or when seeking a value less than a specified value. The optimizer uses index range scan descending when an order by descending clause can be satisfied by an index.
Execution plan using Index Range Scans Descending below:
ops$rperumal@PDB10> SELECT * FROM t WHERE pkc BETWEEN 2000 AND 3000 ORDER BY pkc DESC;
Execution Plan
----------------------------------------------------------
Plan hash value: 725076093
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1002 | 36072 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1002 | 36072 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| T_PK | 1002 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PKC">=2000 AND "PKC"<=3000)
Index Skip Scan
Index skip scan improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks. Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
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.
Execution plan using Index Skip Scan below:
ops$rperumal@PDB10> SELECT * FROM t WHERE rsn1 = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 2069911305
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 36 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | T_SV2_RSN1 | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RSN1"=55)
filter("RSN1"=55)
ops$rperumal@PDB10> SELECT sv2,rsn1 FROM t WHERE rsn1 = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 714504825
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | T_SV2_RSN1 | 1 | 7 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("RSN1"=55)
filter("RSN1"=55)
ops$rperumal@PDB10> SELECT data FROM t WHERE rsn1 = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 2069911305
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 15 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | T_SV2_RSN1 | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RSN1"=55)
filter("RSN1"=55)
Index Full Scan
A full index scan eliminates a sort operation, because the data is ordered by the index key. It reads the blocks singly. A full scan is used in any of the following situations:
- All of the columns in the ORDER BY clause must be in the index: The order of the columns in the ORDER BY clause must match the order of the leading index columns. The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.
- The query requires a sort merge join. A full index scan can be done instead of doing a full table scan followed by a sort if the query meets the following requirements: All of the columns referenced in the query must be in the index. The order of the columns referenced in the query must match the order of the leading index columns.
- A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index. The columns do not need to be in the same order in the index and the GROUP BY clause. The GROUP BY clause can contain all of the columns in the index or a subset of the columns in the index.
Execution plan using Index Full Scan below:
ops$rperumal@PDB10> SELECT * FROM t ORDER BY pkc; Execution Plan ---------------------------------------------------------- Plan hash value: 1399892806 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10000 | 351K| 80 (2)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 10000 | 351K| 80 (2)| 00:00:01 | | 2 | INDEX FULL SCAN | T_PK | 10000 | | 21 (0)| 00:00:01 | ------------------------------------------------------------------------------------ ops$rperumal@PDB10> SELECT pkc FROM t ORDER BY pkc; Execution Plan ---------------------------------------------------------- Plan hash value: 2446862604 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 40000 | 21 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | T_PK | 10000 | 40000 | 21 (0)| 00:00:01 | -------------------------------------------------------------------------
Index Fast Full Scan
Index fast full scan is an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key.
Execution plan using Index Fast Full Scan below:
ops$rperumal@PDB10> SELECT cv20,rsn2 FROM t WHERE rsn2 = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 2742123628
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 7 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| T_CV20_RSN2 | 1 | 7 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RSN2"=55)
Sometimes the Oracle Optimizer will turn an Index access to Full table access, some of the scenarios are:
Index Range Scan to Full Table Scan
The optimizer does FTS if a large perentage of rows are read; usually over 5-20%, but this percentage can vary. Below example shows that an Index Range Scan turned into a Full Table scan upon requesting to retrieve 20% of the rows from a table.
ops$rperumal@PDB10> SELECT * FROM t WHERE pkc < 2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1999 | 71964 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1999 | 71964 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PKC"<2000)
ops$rperumal@PDB10> SELECT * FROM t WHERE pkc > 8000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 72000 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2000 | 72000 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PKC">8000)
ops$rperumal@PDB10> SELECT * FROM t WHERE pkc BETWEEN 2000 AND 6000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4002 | 140K| 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4002 | 140K| 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PKC"<=6000 AND "PKC">=2000)
ops$rperumal@PDB10> SELECT * FROM t WHERE pkc <> 2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 351K| 16 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 9999 | 351K| 16 (7)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PKC"<>2000)
Index Skip Scan / Index Fast Full Scan to Full Table Scan
Optimizer performs a Index Skip Scan only if a leading edge of the index has very few distinct values and the optimizer is aware of that. SV2 column in the test table has two distinct values, retrieving based on rsn1=55 uses index (T_SV2_RSN1), which results in Index Skip Scan. Whereas, CV2 column in the test table has 20 distinct values, retrieving based on rsn2=55 and selecting column that part of composite index (T_CV20_RSN2) , which results in Index Fast Full Scan.
- Index Skip Scan will turn into Full Table Scan when the leading edge of the index has many distinct values and you retrieve the data by non-leading index column.
- Index Fast Full Scan will turn into Full Table Scan when you do not refer indexed columns in your query.
ops$rperumal@PDB10> SELECT * FROM t WHERE rsn2 = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 36 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RSN2"=55)
ops$rperumal@PDB10> SELECT cv20,rsn2, data FROM t WHERE rsn2 = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 18 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RSN2"=55)
Implicit or Explict data type conversion to Number
When you have a indexed character column and the column holds only numeric values, following may turn index access to a full table scan.
ops$rperumal@PDB10> SELECT * FROM t WHERE data = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 16 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 36 | 16 (7)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("DATA")=1)
ops$rperumal@PDB10> SELECT * FROM t WHERE to_number(data)=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 16 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 36 | 16 (7)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("DATA")=1)
Using function on an indexed column
If you use a function on an indexed column similar to below then index will not be used unless there is a matching function based index.
SELECT * FROM t WHERE your_function(data) = my_value
Counting Records
If you issue a query similar below and it has a B*Tree Index then no NULL records will make it into the index; Hence, the optimizer will do a Full Table Scan to get the count of rows.
SELECT COUNT(*) FROM t
Missing Statistics
If the optimizer is not aware of the data then it will lead to incorrect cardinality; it could result in full table scans.