UNDERSTANDING EXPLAIN PLAN 5

 Table Access Options

FULL	Reads every row in the table. Every block is read until the high water mark of the table is reached.
CLUSTER	Accesses data using an index cluster key. An INDEX CLUSTER stores rows from one or more tables in the same segment. Rows with common cluster key values are stored together. In a HASH CLUSTER, the location of rows in a single table is dependent on the hashed value of the table’s key.

TABLE ACCESS CLUSTER returns rows from a table that is stored within a cluster, when the cluster key is used.

The query from the NESTED LOOPS topic will be used here again as an example. For the purposes of this example, assume that the COMPANY table is stored in a cluster, named COMPANY_CLUSTER, and the cluster key is the Company_ID column. The name of the cluster key index (on Company_ID) is COMPANY_CLUSTER_NDX.

Example

select COMPANY.Name from COMPANY, SALES where COMPANY.Company_D = SALES.Company_ID and SALES.Period_ID = 3 and SALES.Sales_Total>1000;

Execution Plan

NESTED LOOPS
TABLE ACCESS FULL SALES
TABLE ACCESS CLUSTER COMPANY
INDEX UNIQUE SCAN COMPANY_CLUSTER_NDX

Interpreting the Execution Plan

The Execution Plan shows that the SALES table is used as the driving table for the query. For each Company_ID value in the SALES table there will be an access to the COMPANY table using the cluster index on the Company_ID column.

HASH	Accesses one or more rows in a table with a matching hash value.

TABLE ACCESS HASH is analogous to TABLE ACCESS BY ROWID; instead of accessing rows by ROWID, the optimizer calculates the location of the row inside the table using a hash formula on the key columns of the table and then uses that value to access the rows.

Example

Assume that the COMPANY table is stored in a hash cluster.  Execute the following SQL statement:

select Name from COMPANY where Company_ID = 12345 and Active_Flag = ‘Y’;

Execution Plan

TABLE ACCESS HASH COMPANY

Interpreting the Execution Plan

Since the Company_ID is the primary key for the COMPANY table, the hash formula uses the Company_ID value to determine the ROWID of the sought row. Since the ROWID will be determined by the hash formula, there is no need for an index access to retrieve the specified row.

BY ROWID

Accesses a single row in a table by specifying its ROWID. This is the fastest way to access a single row. Frequently, the ROWID is obtained by an associated index lookup.

TABLE ACCESS BY ROWID returns a single row from a table, based on the ROWID provided to the operation. This is the fastest way to return data from a table.

Note: TABLE ACCESS BY ROWID may also represent an implicit FILTER operation.

Example

select Name from COMPANY where Company_ID = 12345and Active_Flag = ‘Y’;

Execution Plan

TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK

Interpreting the Execution Plan

As shown in the Execution Plan, the use of the Company_ID column in the query’s WHERE clause allowed the COMPANY_PK index to be used. That index does not also contain the Name column, so Oracle must access the COMPANY table, using the ROWID returned by the index, to get the Name value. An implicit FILTER is then performed to return only the rows with Active_Flag = 'Y'.

BY USER ROWID

Accesses using a ROWID provided by a bind variable, literal or WHERE CURRENT OF CURSOR clause.

TABLE ACCESS BY USER ROWID is used to access records using records provided by a bind variable, literal or WHERE CURRENT OF CURSOR clause.

BY INDEX ROWID

Accesses using a ROWID obtained through an index lookup.

TABLE ACCESS BY INDEX ROWID is used to access records using a ROWID obtained through an index lookup.

BY GLOBAL INDEX ROWID

Accesses using a ROWID obtained from a globally partitioned index.

Indexes can also be partitioned. While it is possible to create a partitioned index on an unpartitioned table, partitioned indexes will more often be created on a table that is itself partitioned.

When an index on a partitioned table is unpartitioned, or is partitioned on different column range conditions than the source table, the index is known as a Global Index.

Note: In general, Local (partitioned) Indexes help release the maximum benefits of partitioning. Avoid using Global Indexes on partitioned tables.

BY LOCAL INDEX ROWID

Accesses using a ROWID obtained from a locally partitioned index.

Indexes can also be partitioned. To take advantage of partition maintenance facilities such as the ability to quickly drop a partition, you should normally create Local Indexes, which are partitioned in the same manner as their source table. When the leading columns of the index are also the columns upon which the index is partitioned then the index is known as a local prefixed index.

Local indexes have some significant advantages over global indexes. When a partitioned table with a global index has a partition split, merge, or move, the corresponding index partition will automatically have the same maintenance operation applied.

The parallel query option is invoked when the SQL statement includes a PARALLEL hint or when the PARALLEL clause has been associated with the table definition. In addition, the table to which the PARALLEL clause has been applied must be accessed by using a full table scan or must be a partitioned table accessed using a local index.

Note: In general, Local (partitioned) Indexes help release the maximum benefits of partitioning. Avoid Global Indexes on partitioned tables.

SAMPLE

Returns a subset of rows as a result of using of the SAMPLE clause

A sample table scan retrieves a random sample of data from a table. This access method is used when the statement's FROM clause includes the SAMPLE clause or the SAMPLE BLOCK clause. To perform a sample table scan when sampling by rows (the SAMPLE clause), Oracle reads a specified percentage of rows in the table and examines each of these rows to determine whether it satisfies the statement's WHERE clause. To perform a sample table scan when sampling by blocks (the SAMPLE BLOCK clause), Oracle reads a specified percentage of the table's blocks and examines each row in the sampled blocks to determine whether it satisfies the statement's WHERE clause.

Oracle does not support sample table scans when the query involves a join or a remote table. However, you can perform an equivalent operation by using a CREATE TABLE AS SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. Additional queries can be written to materialize samples for other tables. Sample table scans require the cost-based optimizer.

Example

The following statement uses a sample table scan to access 1% of the emp table, sampling by blocks:

SELECT * FROM emp SAMPLE BLOCK (1);

Execution Plan

Operation	Options	Object_name
SELECT STATEMENT TABLE ACCESS	SAMPLE	EMP