Oracle query access paths

 In Oracle, the query optimizer generates execution plans to determine how to retrieve the data for a SQL statement. These execution plans define the access paths that will be used to retrieve the data. There are several execution plan types in Oracle, and each has its own ranking in terms of performance:

Index Unique Scan

This execution plan type retrieves a single row using a unique or primary key index. It is the fastest access path and is used when the query references a unique or primary key column.

Index Range Scan

This execution plan type retrieves a range of rows using an index. It is faster than a full table scan and is used when the query references a non-unique index column.

Index Full Scan

This execution plan type scans the entire index and is used when the query references a non-unique index column, and the optimizer determines that an index range scan would be slower.

Table Access by Rowid

This execution plan type retrieves a single row using a row identifier. It is used when the query references the ROWID pseudo-column or when the query joins two or more tables using a ROWID.

Table Access Full

This execution plan type reads all rows in a table, regardless of the query criteria. It is the slowest access path and is used when the optimizer determines that a full table scan is the most efficient method of accessing the data.

Table Access by Index Rowid

This execution plan type retrieves data from a table using an index and a row identifier. It is used when the query references a non-unique index column and the optimizer determines that an index range scan would be slower.

Bitmap Index Scan

This execution plan type retrieves a range of rows using a bitmap index. It is used when the query references a bitmap index column.

Bitmap Index Single Value

This execution plan type retrieves a single row using a bitmap index. It is used when the query references a bitmap index column and the optimizer determines that an index unique scan would be slower.