Data Access methods in Explain plan

An access method  or access path shows the way the data will be accesses from each table/index. This can be seen in the operation column tab in EXPLAIN PLAN.

Oracle Supports the below access methods.

1. Full Table SCAN (FTS)
2. Table Access by ROW-ID
3. Index Unique Scan
4. Index Range Scan
5. Full Index Scan
6. Fast Full Index Scans
7. Index Skip Scan
8. Index Joins
9. Hash Access
10. Cluster Access
11. Bit Map Index

 Full Table SCAN (FTS)

In a FTS operation, the whole table is read up to the high water mark (HWM). 

The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multi-block i/o to read the blocks from disk.

Buffers from FTS operations are placed on the Least Recently Used (LRU) end of the buffer cache so will be quickly aged out. 

In most cases FTS is not recommended for large tables unless you are reading >5-10% of it (or so) or you intend to run in parallel simply because there is likely to be a more economical way of accessing the data.

A multi-block read can be performed to speed up the table scan: several adjacent blocks are combined into a single I/O call. 

The DB_FILE_MULTIBLOCK_READ_COUNT parameter in the init.ora file defines the number of blocks that can be lumped into one multi-block read.

A full table scan is typically employed if

• no index exists;
• the index cannot be used because the query predicate has a function applied to an indexed column (in a non-function-based index);
• the optimizer decides against an index skip scan because the query predicate does not include the leading edge of a (B-tree) index;
• SELECT COUNT(*) is issued and the index contains nulls;
• the table statistics are stale and the table has grown considerably since the statistics were last computed;
• the query is not selective, so that a large portion of the rows must be accessed;
• the cost of a full table scan is the lowest because the table is small, in particular the number of formatted blocks under the high water mark is smaller than DB_FILE_MULTIBLOCK_READ_COUNT;
• the table has a high degree of parallelism, which makes the optimizer biased towards full table scans;
• the query uses the FULL hint.

SQL> set autotrace explain SQL> select * from dual; Execution Plan ---------------------------------------------------------- Plan hash value: 3543395131 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

 

 Table Access by ROW-ID

Rowid of a row specifies datafile,data block within the file and location of row within that block.
Oracle initially obtains the rowid’s either from a where clause predicate or through an index scan of one or more of table’s indexes.Once these  indexes are obtained,the required rows are selected based on the rowids and does a row by row access.

When the Optimizer Uses Rowids:
 
This is generally the second step after retrieving the rowid from an index. The table access might be required for any columns in the statement not present in the index.
Access by rowid does not need to follow every index scan. If the index contains all the columns needed for the statement, then table access by rowid might not occur

Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o.

In this example an index is used to find the relevant row(s) and then the table is accessed to look up the ename column (which is not included in the index).

If the execution plan shows a line TABLE ACCESS BY INDEX ROWID BATCHED it means that Oracle retrieves a bunch of ROWIDs from the index and then tries to access rows in block order to reduce the number of times each block needs to be accessed.

SQL> select empno,ename from emp where empno=10; Execution Plan ---------------------------------------------------------- Plan hash value: 4066871323 -------------------------------------------------------------------------------------- | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |        |     1 |    10 |     1   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=10)

  

Notice the ‘TABLE ACCESS BY ROWID’ section. This indicates that the table data is not being accessed via a FTS operation but rather by a rowid lookup. 

In this case the rowid has been produced by looking up values in the index first. The index name in this case is PK_EMP and the index is being accessed by an ‘INDEX UNIQUE SCAN’ operation.

If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access.

Index Unique Scan:

As the name says, it returns at most, a single rowid only.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.

For an index unique scan only one row of a (B-tree) index or index-organized table will be returned because of an equality predicate on a unique index or a primary key constraint.
The database stops looking for more rows once it has found its match because there cannot be any more matches thanks to the UNIQUE constraint.

SQL> select empno from emp where empno=10; Execution Plan ---------------------------------------------------------- Plan hash value: 4008335093 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |     1 |     4 |     0   (0)| 00:00:01 | |*  1 |  INDEX UNIQUE SCAN| PK_EMP |     1 |     4 |     0   (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("EMPNO"=10) If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access.  In the above example all the columns (empno) are in the index. Notice that no table access takes place

  

 Index Range Scan:

Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. It can be used for range operations (e.g. > < >= <= between  ) or where the data to be returned is not unique.  

n.b: A non-unique index may return multiple values for the predicate mgr = 5 and will use an 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.

Index Range scan descending: It’s conceptually the same as an index range scan but used when it’s needed a situation alike of .. “order by descending”.

When the Optimizer Uses Index range scan :

The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:

1.     col1 = :b1
2.     col1 < :b1
3.     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 nonunique indexes. Range scans avoid sorting when index columns constitute the ORDER BY/GROUP BY clause.

select empno,ename from emp where empno > 7876 order by empno; Execution Plan ---------------------------------------------------------- Plan hash value: 2449469783 -------------------------------------------------------------------------------------- | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |        |     1 |    10 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | PK_EMP |     1 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO">7876)

n.b: A non-unique index may return multiple values for the predicate mgr = 5 and will use an index range scan.

SQL> create index emp_mgr on emp(mgr); SQL> select mgr from emp where mgr = 5; Execution Plan ---------------------------------------------------------- Plan hash value: 1542557660 ---------------------------------------------------------------------------- | Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT |         |     1 |     4 |     1   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| EMP_MGR |     1 |     4 |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("MGR"=5)

  

Index Full Scan

A full index scan does not read every block in the index though. Instead it reads the root block and goes down the left-hand side (ascending scan) or right-hand side (descending scan) of the branch blocks until it reaches a leaf block. From there it reads the index on a block-by-block basis

The index blocks are scanned one by one, not using multi-block I/O (like a FTS or Index FFS). 

In the following example, Index E_CIX is a concatenated index on emp (empno,ename). A select with no predicates results in an index full scan since it can satisfy the whole query without need to visit the table

 

SQL> create index E_CIX on emp (empno,ename); Index created. SQL> select empno,ename from emp order by empno,ename; Execution Plan ---------------------------------------------------------- Plan hash value: 2418964722 -------------------------------------------------------------------------- | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT |       |    14 |   140 |     1   (0)| 00:00:01 | |   1 |  INDEX FULL SCAN | E_CIX |    14 |   140 |     1   (0)| 00:00:01 | -------------------------------------------------------------------------- Index Full Scan can also be used to access second column of concatenated indexes because the whole index is being retrieved as compared to a range scan which may not retrieve all the blocks SQL> select ename from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 2418964722 -------------------------------------------------------------------------- | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT |       |    14 |    84 |     1   (0)| 00:00:01 | |   1 |  INDEX FULL SCAN | E_CIX |    14 |    84 |     1   (0)| 00:00:01 | -------------------------------------------------------------------------- The full index scan is used when one of the following situations arises: a predicate references a (non-leading) column in an index; no predicate is specified but all columns in the table as well as query are in the index, and at least one indexed column is not null; the query has an ORDER BY clause on indexed non-null columns; means order of the columns referenced in the query matches the order of the leading index columns the query has a GROUP BY clause where all aggregation columns are present in the index. means A group by clause is present in the query and the columns in the group by clause are present in the index Fast Full Index Scans Fast full index scans are 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. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized. Fast full index scans cannot be performed against bitmap indexes. A fast full index scan reads index blocks as they exist on disk. The index (entries on the leaf blocks) rather than the table is read in multi-block I/O mode. SQL> select /*+ Index_FFS(emp) */ empno,ename from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 2100043038 ------------------------------------------------------------------------------ | Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT     |       |    14 |   140 |     2   (0)| 00:00:01 | |   1 |  INDEX FAST FULL SCAN| E_CIX |    14 |   140 |     2   (0)| 00:00:01 | ------------------------------------------------------------------------------ Index Skip Scan:     If all the other columns in an index are referenced in the query except the first (leading one)…. Oracle prefers index skip scan. The index skip scan is also an option if the leading column has few distinct values. SQL> create index E_CIX on emp (empno,ename); Index created. SQL>  select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH'; Execution Plan ---------------------------------------------------------- Plan hash value: 112616935 --------------------------------------------------------------------------------------------- | Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |       |     1 |    14 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP   |     1 |    14 |     2   (0)| 00:00:01 | |*  2 |   INDEX SKIP SCAN                   | E_CIX |     1 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ENAME"='SMITH') filter("ENAME"='SMITH') Index Joins:   It’s a join of several indexes on the same table that collectively contain all of the columns that are referenced in the query from that of the table. Whenever Index Join is used no table access is required as all the relevant columns are retrieved from the joined indexes. This can never be used to eliminate a sort operation. Hash Access: A hash scan is used to locate rows in a hash cluster, based on a hash value. In a hash cluster, all rows with the same hash value are stored in the same data block. To perform a hash scan, Oracle first obtains the hash value by applying a hash function to a cluster key value specified by the statement. Oracle then scans the data blocks containing rows with that hash value. Cluster Access:   A cluster scan is used to retrieve, from a table stored in an indexed cluster, all rows that have the same cluster key value. In an indexed cluster, all rows with the same cluster key value are stored in the same data block. To perform a cluster scan, Oracle first obtains the rowid of one of the selected rows by scanning the cluster index. Oracle then locates the rows based on this rowid. Bit Map Index access: If the predicate on a bitmap-indexed column contains an equality operator, the query optimizer considers the bitmap index single value access path to look up a single key value. A single bitmap is scanned for all positions containing a value of 1. All matching values are converted into ROWIDs, which in turn are used to find the corresponding rows A bitmap merge is typically preferred by the optimizer when bitmaps generated from a bitmap index range scan are combined with an OR operation between two bitmaps