UNDERSTANDING EXPLAIN PLAN 6

 Miscellaneous Operations

FOR UPDATE

Locks returned rows as a result of the FOR UPDATE clause.

FOR UPDATE places a row-level lock on all the rows that can be retrieved from the SELECT statement.

Using FOR UPDATE allows you to use the WHERE CURRENT OF clause in INSERT, UPDATE, and DELETE commands. A COMMIT will invalidate the cursor, so you will need to reissue the SELECT FOR UPDATE after every commit.

Example

select Name, City, State from COMPANY where City > ‘Roanoke’ and Active_Flag = ‘Y’
for update of Name;

Execution Plan

FOR UPDATE
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

Interpreting the Execution Plan

The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY that satisfy the limiting condition on the City value (City > ‘Roanoke’). The ROWIDs from the index scan are used to query the COMPANY table for the Name and State values. The Active_Flag=‘Y’ criteria is implicitly applied during the TABLE ACCESS BY ROWID operation. The FOR UPDATE operation is then applied to give the user row-level locks on each row returned from the query.

FILTER

Eliminates rows from a result set not matching the selection criteria.

FILTER performs a WHERE clause condition when no index can be used to assist in the evaluation. Unfortunately, the FILTER operation is sometimes implicit. Any FILTER condition that is applied when performing a table access (such as during a TABLE ACCESS BY ROWID) does not show up in the plan. When FILTER shows up in an Explain plan, it usually the result of a missing index or the disabling of an existing index.

The FILTER operation was in a prior example—the CONNECT BY operation’s example. In the query shown in the following listing, the WHERE criteria on the State column is not applied until after the CONNECT BY hierarchy has completed; the resulting rows are filtered to determine which meet the specified State criteria.

Example

select Company_ID, Name from COMPANY where State = ‘VA’
connect by Parent_Company_ID = prior Company_ID start with Company_ID = 1;

Execution Plan

FILTER
CONNECT BY
INDEX UNIQUE SCAN COMPANY_PK
TABLE ACCESS BY ROWID COMPANY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$PARENT

Interpreting the Execution Plan

The plan shows that first the COMPANY_PK index is being used to find the root node (Company_ID = 1), then the index on the Parent_Company_ID column is used to provide values for queries against the Company_ID column in an iterative fashion. After the hierarchy of Company_IDs is complete, the FILTER operation—the WHERE clause related to the State value—is applied. The query does not use the index on the State column, although it is available and the column is used in the WHERE clause.

REMOTE

Accesses an external database through a database link.

REMOTE sends a SQL statement to be executed at a remote node via a database link. The syntax of the SQL statement sent to the remote node is shown in the Other column of PLAN_TABLE.

Example

Since REMOTE requires a remote database access, a database link will be created for this example. The database link connects to the Hobbes account in the database that is identified via the ‘test’ service name in the local tnsnames.ora file.

create database link REMOTE1 connect to hobbes identified by tiger using ‘test’;

In the example query, a local COMPANY is joined to a remote SALES via a NESTED LOOPS join.

Example

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

Execution Plan

NESTED LOOPS
REMOTE
TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK

For the step with the REMOTE operation, you can query PLAN_TABLE for the syntax of the query sent to the remote node:

select Other from PLAN_TABLE where Operation = ‘REMOTE’;

The value of the Other column for this example is:

SELECT "COMPANY_ID","PERIOD_ID","SALES_TOTAL"
FROM "SALES" SALES WHERE "SALES_TOTAL">1000 AND "PERIOD_ID"=3

Interpreting the Execution Plan

The Explain Plan shows that the remote SALES table is used as the driving table for the NESTED LOOPS join (see the NESTED LOOPS operation for a brief discussion of driving tables). The text in the PLAN_TABLE. The Other column shows the query that is executed in the remote database. For each Company_ID value returned by the query of the remote SALES table, the COMPANY_PK index will be checked to see if a matching Company_ID value exists in the COMPANY table. When a match exists, that row is returned to the user by using the NESTED LOOPS operation.

FIRST ROW

Retrieves the first row of a query.

FIRST ROWS uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best response time (minimum resource use to return the first row of the result set).

The following statement changes the goal of the cost-based optimizer for your session to best response time:

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

SEQUENCE

Accesses an Oracle sequence generator to obtain a unique sequence number

SEQUENCE is used when accessing a database sequence via the NextVal and CurrVal pseudo-columns.

Example

Since the SEQUENCE operation requires a sequence to exist, a sequence named COMPANY_ID_SEQ will be created.

create sequence COMPANY_ID_SEQ
start with 1 increment by 1;

In the example query, the next value is selected from the sequence by selecting the NextVal pseudo-column from DUAL.

select COMPANY_ID_SEQ.NextVal  from DUAL;

Execution Plan

SEQUENCE COMPANY_ID_SEQ
TABLE ACCESS FULL DUAL

Interpreting the Execution Plan

The Execution Plan shows that the DUAL table (comprising 1 row, and owned by SYS) is scanned. The COMPANY_ID_SEQ sequence is used to generate the value of the NextVal pseudo-column for the returned row, using the SEQUENCE operation.

INLIST ITERATOR

Performs the next operation once for each value in an IN list predicate.

An INLIST ITERATOR operation appears in the Execution Plan output if an index implements an INLIST predicate.

Example

SELECT * FROM emp WHERE empno IN (7876, 7900, 7902);

Execution Plan

Operation	Options	Object_name
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS	BY ROWID	EMP
INDEX	RANGE SCAN	EMP_EMPNO

The INLIST ITERATOR operation iterates over the operation below it for each value in the IN-list predicate. For partitioned tables and indexes, the three possible types of INLIST columns are:

·         Index column

·         Index and partition column

·         Partition column

Index Column

When the INLIST column empno is an index column but not a partition column, then the plan is as follows (the INLIST operator appears above the table operation but below the partition operation):

Operation	Options	Object_name	Partition Start	Partition Stop
SELECT STATEMENT
PARTITION	INLIST		KEY(INLIST)	KEY(INLIST)
INLIST ITERATOR
TABLE ACCESS	BY ROWID	EMP	KEY(INLIST)	KEY(INLIST)
INDEX	RANGE SCAN	EMP_EMPNO	KEY(INLIST)	KEY(INLIST)

The KEY(INLIST) designation for the partition start and stop keys specifies that an INLIST predicate appears on the index start/stop keys.

Index and Partition Columns

When empno is an indexed and a partition column, the Explain plan contains an INLIST ITERATOR operation above the partition operation:

Operation	Options	Object_name	Partition Start	Partition Stop
SELECT STATEMENT
INLIST ITERATOR
PARTITION	ITERATOR		KEY(INLIST)	KEY(INLIST)
TABLE ACCESS	BY ROWID	EMP	KEY(INLIST)	KEY(INLIST)
INDEX	RANGE SCAN	EMP_EMPNO	KEY(INLIST)	KEY(INLIST)

Partition Column

When empno is a partition column and there are no indexes, then no INLIST ITERATOR operation is allocated:

Operation	Options	Object_name	Partition Start	Partition Stop
SELECT STATEMENT
PARTITION			KEY(INLIST)	KEY(INLIST)
TABLE ACCESS	BY ROWID	EMP	KEY(INLIST)	KEY(INLIST)
INDEX	RANGE SCAN	EMP_EMPNO	KEY(INLIST)	KEY(INLIST)

When emp_empno is a bitmap index, the Execution plan is as follows:

Operation	Options	Object_name
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS	BY INDEX ROWID	EMP
BITMAP CONVERSION		TO ROWIDS
BITMAP INDEX	SINGLE VALUE	EMP_EMPNO

LOAD AS SELECT

Denotes a direct path INSERT based on a SELECT statement.

LOAD AS SELECT bypasses the buffer cache when performing a direct path load based on a SELECT statement.

An Execution plan will denote the presence of a direct load operation with the LOAD AS SELECT operation:

	Rows	Explain Plan
	0	SELECT STATEMENT GOAL: CHOOSE
	0	LOAD AS SELECT
	0	TABLE ACCESS (FULL) OF 'CUSTOMERS'
FIXED TABLE			Accesses a "fixed" (X$) table.

Fixed tables are those not in Oracle’s data dictionary. FIXED TABLE is normally used to optimize V$ and X$ statements. However, join order for a V$ query will be entirely determined by the order of tables in the FROM clause. Be careful when using FIXED TABLE because:

·         Neither the cost-based optimizer nor the rule-based optimizer recognizes the presence of V$ indexes when determining join order or method.

·         There are never any optimizer statistics held against the V$ or X$ tables and consequently the cost-based optimizer has no information to use to determine the best join order.

FIXED INDEX

Accesses an "index" on "fixed" (X$) table.

Where an index exists on a V$ table, it will normally be used whenever the column is used for an exact lookup. The Explain Plan reveals that this is so through the special access path FIXED INDEX. For instance, the following query uses the SID index on V$SESSION:

select * from v$session where sid=171

Rows	Explain Plan
1	FIXED TABLE FIXED INDEX #1 X$KSUSE cpu=1 elapsed=1 logical=0 physical=0

Remembering that the "index" is not really an Oracle B-tree index and in has more in common with a hash cluster, it’s not surprising to see that the index is disabled if a range scan is attempted:

select * from v$session where sid<8

	Rows	Explain Plan
	700	FIXED TABLE FULL X$KSUSE cpu=19 elapsed=19 logical=0 physical=0
TEMP TABLE GENERATION			Creates a temporary dimension table for dimension tables joined by a Star Transformation.

The TEMP TABLE GENERATION operation creates a temporary dimension table for dimension tables that have been joined by a Star Transformation. For each dimension table in the joined set, TEMP TABLE GENERATION creates a temporary table to replace the table in the Execution Plan. A temporary table is created using two steps: create table and insert each. Since there are at least two tables in a join, this operation creates a minimum of four temporary tables.

COLLECTION ITERATOR

Returns certain values from a collection such as VARRAY and nested table.

The Collection Iterator operation returns certain values from a collection such as VARRAY and nested table.

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