Explain Plan

Full Table Scans

Full table scan will scan all the data block that under the HWM (high water mark), including the empty data block. In order to reduce the physical I/O, one parameter DB_FILE_MUTLIBLOCK_READ_COUNT can be set to a higher value.

ROWID Scans

ROWID is composed of data object id, data file id, data block id and the record slot in the data block. The fastest way to retrieve one record from one table is to use ROWID.

Index Scans

1. Index Unique Scan
2. Index Range Scan
3. Index Range Scans Descending
   Index range scan descending is similar to index range scan, except it accesses the table data in descending order instead of in ascending order. The optimizer will choose this kind of index scan under two circumstances: one is the query uses the “ORDER BY…DESC” and the other one is the query uses the “INDEX_DESC” hint.
4. Index Skip Scan
   Index Skip Scan is introduced to resolve the issue of the composite index cannot be used if the leading column is not used in the predicates.
5. Index Full Scan
   Index Full Scan will be used when the following two conditions are met,
   All the columns in the SELECT-list are included in the index.
   There is at least one NOT NULL column in the index
6. Index Fast Full Scan
   The difference between Index Fast Full Scan and Index Full Scan is that the Index Fast Full Scan will read multiple index blocks rather than one block in each I/O operation.

B-Tree Cluster Access

Hash Cluster Access

Sample Table Access

Sample table access is only available in Full Table Scans and Index Fast Full Scans. The basic syntax is as follows,

SELECT … FROM table_name SAMPLE {BLOCK option} (Sample Percent) WHERE… GROUP BY… HAVING… ORDER BY…