5
Using EXPLAIN PLAN

This chapter introduces execution plans, describes the SQL statement EXPLAIN PLAN, and explains how to interpret its output. This chapter also discusses plan stability features and the use of stored outlines to preserve your tuning investment for particular SQL statements. This chapter provides procedures for managing outlines to control application performance characteristics.

This chapter contains the following sections:

Understanding EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to execute the statement. The components of execution plans include:

An ordering of the tables referenced by the statement.
An access method for each table mentioned in the statement.
A join method for tables affected by join operations in the statement.

EXPLAIN PLAN output shows how Oracle executes SQL statements. EXPLAIN PLAN results alone, however, cannot differentiate between well-tuned statements and those that perform poorly. For example, if EXPLAIN PLAN output shows that a statement uses an index, then this does not mean the statement runs efficiently. Sometimes using indexes can be extremely inefficient. It is best to use EXPLAIN PLAN to determine an access plan, and later prove that it is the optimal plan through testing.

When evaluating a plan, always examine the statement's actual resource consumption. For best results, use the Oracle Trace or SQL trace facility and TKPROF to examine individual SQL statement performance.

Creating the Output Table

Before issuing an EXPLAIN PLAN statement, create a table to hold its output. Use one of the following approaches:

Run the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. For example, on Sun Solaris, the UTLXPLAN.SQL is located under $ORACLE_HOME/rdbms/admin. PLAN_TABLE is the default table into which the EXPLAIN PLAN statement inserts rows describing execution plans.
Issue a CREATE TABLE statement to create an output table with any name you choose. When you issue an EXPLAIN PLAN statement, you can direct its output to this table.

Any table used to store the output of the EXPLAIN PLAN statement must have the same column names and datatypes as the PLAN_TABLE:

CREATE TABLE PLAN_TABLE (

STATEMENT_ID    VARCHAR2(30),
TIMESTAMP       DATE,
REMARKS         VARCHAR2(80),
OPERATION       VARCHAR2(30),
OPTIONS         VARCHAR2(30),
OBJECT_NODE     VARCHAR2(128),
OBJECT_OWNER    VARCHAR2(30),
OBJECT_NAME     VARCHAR2(30),
OBJECT_INSTANCE NUMERIC,
OBJECT_TYPE     VARCHAR2(30),
OPTIMIZER       VARCHAR2(255),
SEARCH_COLUMNS  NUMBER,
ID              NUMERIC,
PARENT_ID       NUMERIC,
POSITION        NUMERIC,
COST            NUMERIC,
CARDINALITY     NUMERIC,
BYTES           NUMERIC,
OTHER_TAG       VARCHAR2(255),
PARTITION_START VARCHAR2(255),
PARTITION_STOP  VARCHAR2(255),
PARTITION_ID    NUMERIC,
OTHER           LONG,
DISTRIBUTION    VARCHAR2(30));

Displaying PLAN_TABLE Output

Display the most recent plan table output using the following scripts:

UTLXPLS.SQL - Shows plan table output for serial processing.
UTLXPLP.SQL - Shows plan table output with parallel execution columns.

The row source count values in EXPLAIN PLAN output identify the number of rows processed by each step in the plan. This helps you identify inefficiencies in the query; for example, the row source with an access plan that is performing inefficient operations.

Output Table Columns

The PLAN_TABLE used by the EXPLAIN PLAN statement contains the following columns:

Table 5-1 PLAN_TABLE Columns (Page 1 of 3)

Column	Description
STATEMENT_ID	The value of the optional `STATEMENT_ID` parameter specified in the `EXPLAIN` `PLAN` statement.
TIMESTAMP	The date and time when the `EXPLAIN` `PLAN` statement was issued.
REMARKS	Any comment (of up to 80 bytes) you want to associate with each step of the explained plan. If you need to add or change a remark on any row of the `PLAN_TABLE`, then use the `UPDATE` statement to modify the rows of the `PLAN_TABLE`.
OPERATION	The name of the internal operation performed in this step. In the first row generated for a statement, the column contains one of the following values: DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT See Table 5-4 for more information on values for this column.
OPTIONS	A variation on the operation described in the `OPERATION` column. See Table 5-4 for more information on values for this column.
OBJECT_NODE	The name of the database link used to reference the object (a table name or view name). For local queries using parallel execution, this column describes the order in which output from operations is consumed.
OBJECT_OWNER	The name of the user who owns the schema containing the table or index.
OBJECT_NAME	The name of the table or index.
OBJECT_INSTANCE	A number corresponding to the ordinal position of the object as it appears in the original statement. The numbering proceeds from left to right, outer to inner with respect to the original statement text. View expansion results in unpredictable numbers.
OBJECT_TYPE	A modifier that provides descriptive information about the object; for example, `NON`-`UNIQUE` for indexes.
OPTIMIZER	The current mode of the optimizer.
SEARCH_COLUMNS	Not currently used.
ID	A number assigned to each step in the execution plan.
PARENT_ID	The ID of the next execution step that operates on the output of the `ID` step.
POSITION	The order of processing for steps that all have the same `PARENT_ID`.
COST	The cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement, it is merely a weighted value used to compare costs of execution plans.
CARDINALITY	The estimate by the cost-based approach of the number of rows accessed by the operation.
BYTES	The estimate by the cost-based approach of the number of bytes accessed by the operation.
OTHER_TAG	Describes the contents of the `OTHER` column. See Table 5-2 for more information on the possible values for this column.
PARTITION_START	The start partition of a range of accessed partitions. It can take one of the following values: n indicates that the start partition has been identified by the SQL compiler, and its partition number is given by n. `KEY` indicates that the start partition will be identified at execution time from partitioning key values. `ROW` `LOCATION` indicates that the start partition (same as the stop partition) will be computed at execution time from the location of each record being retrieved. The record location is obtained by a user or from a global index. `INVALID` indicates that the range of accessed partitions is empty.
PARTITION_STOP	The stop partition of a range of accessed partitions. It can take one of the following values: n indicates that the stop partition has been identified by the SQL compiler, and its partition number is given by n. `KEY` indicates that the stop partition will be identified at execution time from partitioning key values. `ROW` `LOCATION` indicates that the stop partition (same as the start partition) will be computed at execution time from the location of each record being retrieved. The record location is obtained by a user or from a global index. `INVALID` indicates that the range of accessed partitions is empty.
PARTITION_ID	The step that has computed the pair of values of the `PARTITION_START` and `PARTITION_STOP` columns.
OTHER	Other information that is specific to the execution step that a user may find useful.
DISTRIBUTION	Stores the method used to distribute rows from producer query servers to consumer query servers. See Table 5-3 for more information on the possible values for this column. For more information about consumer and producer query servers, see Oracle8i Concepts.

Table 5-2 describes the values that may appear in the OTHER_TAG column.

Table 5-2 Values of OTHER_TAG Column of the PLAN_TABLE

OTHER_TAG Text (examples)	Meaning	Interpretation
blank		Serial execution.
SERIAL_FROM_REMOTE (S -> R)	Serial from remote	Serial execution at a remote site.
SERIAL_TO_PARALLEL (S -> P)	Serial to parallel	Serial execution; output of step is partitioned or broadcast to parallel execution servers.
PARALLEL_TO_PARALLEL (P - > P)	Parallel to parallel	Parallel execution; output of step is repartitioned to second set of parallel execution servers.
PARALLEL_TO_SERIAL (P -> S)	Parallel to serial	Parallel execution; output of step is returned to serial "query coordinator" process.
PARALLEL_COMBINED_ WITH_PARENT (PWP)	Parallel combined with parent	Parallel execution; output of step goes to next step in same parallel process. No interprocess communication to parent.
PARALLEL_COMBINED_ WITH_CHILD (PWC)	Parallel combined with child	Parallel execution; input of step comes from prior step in same parallel process. No interprocess communication from child.

Table 5-3 describes the values that can appear in the DISTRIBUTION column:

Table 5-3 Values of DISTRIBUTION Column of the PLAN_TABLE

DISTRIBUTION Text	Interpretation
PARTITION (ROWID)	Maps rows to query servers based on the partitioning of a table or index using the rowid of the row to `UPDATE`/`DELETE`.
PARTITION (KEY)	Maps rows to query servers based on the partitioning of a table or index using a set of columns. Used for partial partition-wise join, `PARALLEL` `INSERT`, `CREATE` `TABLE` `AS` `SELECT` of a partitioned table, and `CREATE` `PARTITIONED` `GLOBAL` `INDEX`.
HASH	Maps rows to query servers using a hash function on the join key. Used for `PARALLEL` `JOIN` or `PARALLEL` `GROUP` `BY`.
RANGE	Maps rows to query servers using ranges of the sort key. Used when the statement contains an `ORDER` `BY` clause.
ROUND-ROBIN	Randomly maps rows to query servers.
BROADCAST	Broadcasts the rows of the entire table to each query server. Used for a parallel join when one table is very small compared to the other.
QC (ORDER)	The query coordinator consumes the input in order, from the first to the last query server. Used when the statement contains an `ORDER` `BY` clause.
QC (RANDOM)	The query coordinator consumes the input randomly. Used when the statement does not have an `ORDER` `BY` clause.

Table 5-4 lists each combination of OPERATION and OPTION produced by the EXPLAIN PLAN statement and its meaning within an execution plan.

Table 5-4 OPERATION and OPTION Values Produced by EXPLAIN PLAN (Page 1 of 4)

Operation	Option	Description
AND-EQUAL		Operation accepting multiple sets of rowids, returning the intersection of the sets, eliminating duplicates. Used for the single-column indexes access path.
	CONVERSION	`TO` `ROWIDS` converts bitmap representations to actual rowids that can be used to access the table. `FROM` `ROWIDS` converts the rowids to a bitmap representation. `COUNT` returns the number of rowids if the actual values are not needed.
	INDEX	`SINGLE` `VALUE` looks up the bitmap for a single key value in the index. `RANGE` `SCAN` retrieves bitmaps for a key value range. `FULL` `SCAN` performs a full scan of a bitmap index if there is no start or stop key.
	MERGE	Merges several bitmaps resulting from a range scan into one bitmap.
	MINUS	Subtracts bits of one bitmap from another. Row source is used for negated predicates. Can be used only if there are nonnegated predicates yielding a bitmap from which the subtraction can take place. An example appears in "Bitmap Indexes and EXPLAIN PLAN".
	OR	Computes the bitwise `OR` of two bitmaps.
CONNECT BY		Retrieves rows in hierarchical order for a query containing a `CONNECT` `BY` clause.
CONCATENATION		Operation accepting multiple sets of rows returning the union-all of the sets.
COUNT		Operation counting the number of rows selected from a table.
COUNT	STOPKEY	Count operation where the number of rows returned is limited by the `ROWNUM` expression in the `WHERE` clause.
DOMAIN INDEX		Retrieval of one or more rowids from a domain index.
FILTER		Operation accepting a set of rows, eliminates some of them, and returns the rest.
FIRST ROW		Retrieval on only the first row selected by a query.
FOR UPDATE		Operation retrieving and locking the rows selected by a query containing a `FOR` `UPDATE` clause.
HASH JOIN (These are join operations.)		Operation joining two sets of rows and returning the result.
	ANTI	Hash anti-join.
	SEMI	Hash semi-join.
INDEX (These are access methods.)	UNIQUE SCAN	Retrieval of a single rowid from an index.
	RANGE SCAN	Retrieval of one or more rowids from an index. Indexed values are scanned in ascending order.
	RANGE SCAN DESCENDING	Retrieval of one or more rowids from an index. Indexed values are scanned in descending order.
INLIST ITERATOR		Iterates over the operation below it for each value in the `IN`-list predicate.
INTERSECTION		Operation accepting two sets of rows and returning the intersection of the sets, eliminating duplicates.
MERGE JOIN (These are join operations.)		Operation accepting two sets of rows, each sorted by a specific value, combining each row from one set with the matching rows from the other, and returning the result.
	OUTER	Merge join operation to perform an outer join statement.
	ANTI	Merge anti-join.
	SEMI	Merge semi-join.
CONNECT BY		Retrieval of rows in hierarchical order for a query containing a `CONNECT` `BY` clause.
MINUS		Operation accepting two sets of rows and returning rows appearing in the first set but not in the second, eliminating duplicates.
NESTED LOOPS (These are join operations.)		Operation accepting two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition.
NESTED LOOPS (These are join operations.)	OUTER	Nested loops operation to perform an outer join statement.
PARTITION	SINGLE	Access one partition.
	ITERATOR	Access many partitions (a subset).
	ALL	Access all partitions.
	INLIST	Similar to iterator, but based on an `IN`-list predicate.
	INVALID	Indicates that the partition set to be accessed is empty.
		Iterates over the operation below it, for each partition in the range given by the `PARTITION_START` and `PARTITION_STOP` columns. `PARTITION` describes partition boundaries applicable to a single partitioned object (table or index) or to a set of equi-partitioned objects (a partitioned table and its local indexes). The partition boundaries are provided by the values of `PARTITION_START` and `PARTITION_STOP` of the `PARTITION`. Refer to Table 5-1 for valid values of partition start/stop.
REMOTE		Retrieval of data from a remote database.
SEQUENCE		Operation involving accessing values of a sequence.
SORT	AGGREGATE	Retrieval of a single row that is the result of applying a group function to a group of selected rows.
	UNIQUE	Operation sorting a set of rows to eliminate duplicates.
	GROUP BY	Operation sorting a set of rows into groups for a query with a `GROUP` `BY` clause.
	JOIN	Operation sorting a set of rows before a merge-join.
	ORDER BY	Operation sorting a set of rows for a query with an `ORDER` `BY` clause.
TABLE ACCESS (These are access methods.)	FULL	Retrieval of all rows from a table.
	CLUSTER	Retrieval of rows from a table based on a value of an indexed cluster key.
	HASH	Retrieval of rows from table based on hash cluster key value.
	BY ROWID	Retrieval of a row from a table based on its rowid.
	BY USER ROWID	If the table rows are located using user-supplied rowids.
	BY INDEX ROWID	If the table is nonpartitioned and rows are located using index(es).
	BY GLOBAL INDEX ROWID	If the table is partitioned and rows are located using only global indexes.
	BY LOCAL INDEX ROWID	If the table is partitioned and rows are located using one or more local indexes and possibly some global indexes.
		Partition Boundaries: The partition boundaries may have been computed by: A previous `PARTITION` step, in which case the `PARTITION_START` and `PARTITION_STOP` column values replicate the values present in the `PARTITION` step, and the `PARTITION_ID` contains the ID of the `PARTITION` step. Possible values for `PARTITION_START` and `PARTITION_STOP` are `NUMBER`(n), `KEY`, `INVALID`. The `TABLE` `ACCESS` or `INDEX` step itself, in which case the `PARTITION_ID` contains the `ID` of the step. Possible values for `PARTITION_START` and `PARTITION_STOP` are `NUMBER`(n), `KEY`, `ROW` `LOCATION` (TABLE ACCESS only), and `INVALID`.
UNION		Operation accepting two sets of rows and returns the union of the sets, eliminating duplicates.
VIEW		Operation performing a view's query and then returning the resulting rows to another operation.

Note:
Access methods and join operations are discussed in Oracle8i Concepts.

Bitmap Indexes and EXPLAIN PLAN

Index row sources using bitmap indexes appear in the EXPLAIN PLAN output with the word BITMAP indicating the type of the index. Consider the following sample query and plan:

EXPLAIN PLAN FOR

SELECT * FROM t
WHERE c1 = 2 
AND c2 <> 6 
OR c3 BETWEEN 10 AND 20;


SELECT STATEMENT
   TABLE ACCESS T BY INDEX ROWID
      BITMAP CONVERSION TO ROWID
         BITMAP OR
            BITMAP MINUS
               BITMAP MINUS
                  BITMAP INDEX  C1_IND  SINGLE VALUE
                  BITMAP INDEX  C2_IND  SINGLE VALUE
               BITMAP INDEX  C2_IND  SINGLE VALUE
            BITMAP MERGE
               BITMAP INDEX  C3_IND  RANGE SCAN

In this example, the predicate c1=2 yields a bitmap from which a subtraction can take place. From this bitmap, the bits in the bitmap for c2 = 6 are subtracted. Also, the bits in the bitmap for c2 IS NULL are subtracted, explaining why there are two MINUS row sources in the plan. The NULL subtraction is necessary for semantic correctness unless the column has a NOT NULL constraint. The TO ROWIDS option is used to generate the ROWIDs that are necessary for the table access.

EXPLAIN PLAN and Partitioned Objects

Use EXPLAIN PLAN to see how Oracle accesses partitioned objects for specific queries.

Partitions accessed after pruning are shown in the PARTITION START and PARTITION STOP columns. The row source name for the range partition is "PARTITION RANGE". For hash partitions, the row source name is PARTITION HASH.

A join is implemented using partial partition-wise join if the DISTRIBUTION column of the plan table of one of the joined tables contains PARTITION(KEY). Partial partition-wise join is possible if one of the joined tables is partitioned on its join column and the table is parallelized.

A join is implemented using full partition-wise join if the partition row source appears before the join row source in the EXPLAIN PLAN output. Full partition-wise joins are possible only if both joined tables are equi-partitioned on their respective join columns. Examples of execution plans for several types of partitioning follow.

Displaying Range and Hash Partitioning with EXPLAIN PLAN

Consider the following table, emp_range, partitioned by range on hiredate to illustrate how pruning is displayed. Assume that the tables emp and dept from a standard Oracle schema exist.

CREATE TABLE emp_range 
PARTITION BY RANGE(hiredate) 
( 

PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1991','DD-MON-YYYY')),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1993','DD-MON-YYYY')),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1995','DD-MON-YYYY')),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1997','DD-MON-YYYY')),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-1999','DD-MON-YYYY')) 

) 
AS SELECT * FROM emp;

Example 1a

EXPLAIN PLAN FOR SELECT * FROM emp_range;

Enter the following to display the EXPLAIN PLAN output:

@?/RDBMS/ADMIN/UTLXPLS

Oracle displays something similar to:

Plan Table 
-------------------------------------------------------------------------------
| Operation               |  Name    |  Rows | Bytes|  Cost  | Pstart |  Pstop|
-------------------------------------------------------------------------------
| SELECT STATEMENT        |          |   105 |    8K|      1 |        |       |
|  PARTITION RANGE ALL    |          |       |      |        |     1  |     5 |
|   TABLE ACCESS FULL     |EMP_RANGE |   105 |    8K|      1 |     1  |     5 |
-------------------------------------------------------------------------------
6 rows selected.

A partition row source is created on top of the table access row source. It iterates over the set of partitions to be accessed.

In example 1a, the partition iterator covers all partitions (option ALL), because a predicate was not used for pruning. The PARTITION_START and PARTITION_STOP columns of the plan table show access to all partitions from 1 to 5.

Example 2a

EXPLAIN PLAN FOR SELECT * FROM emp_range 
WHERE hiredate >= TO_DATE('1-JAN-1995','DD-MON-YYYY'); 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 | Name    |  Rows  | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     3 |   54 |      1 |       |       |
|  PARTITION RANGE ITERATOR |          |       |      |        |     4 |     5 |
|   TABLE ACCESS FULL       |EMP_RANGE |     3 |   54 |      1 |     4 |     5 |
--------------------------------------------------------------------------------
6 rows selected.

In example 2a, the partition row source iterates from partition 4 to 5, because we prune the other partitions using a predicate on hiredate.

Example 3a

EXPLAIN PLAN FOR SELECT * FROM emp_range 
WHERE hiredate < TO_DATE('1-JAN-1991','DD-MON-YYYY'); 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     2 |   36 |      1 |       |       |
|  TABLE ACCESS FULL        |EMP_RANGE |     2 |   36 |      1 |     1 |     1 |
--------------------------------------------------------------------------------
5 rows selected.

In example 3a, only partition 1 is accessed and known at compile time; thus, there is no need for a partition row source.

Plans for Hash Partitioning

Oracle displays the same information for hash partitioned objects, except that the partition row source name is PARTITION HASH instead of PARTITION RANGE. Also, with hash partitioning, pruning is only possible using equality or IN-list predicates.

Pruning Information with Composite Partitioned Objects

To illustrate how Oracle displays pruning information for composite partitioned objects, consider the table emp_comp that is range partitioned on hiredate and subpartitioned by hash on deptno.

CREATE TABLE emp_comp PARTITION BY RANGE(hiredate) SUBPARTITION BY HASH(deptno) 
SUBPARTITIONS 3 
( 

PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1991','DD-MON-YYYY')),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1993','DD-MON-YYYY')),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1995','DD-MON-YYYY')),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1997','DD-MON-YYYY')),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-1999','DD-MON-YYYY')) 

) 
AS SELECT * FROM emp;

Example 1b

EXPLAIN PLAN FOR SELECT * FROM emp_comp; 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name   |  Rows | Bytes|  Cost  | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |         |   105 |    8K|      1 |        |       |
|  PARTITION RANGE ALL      |         |       |      |        |     1  |     5 |
|   PARTITION HASH ALL      |         |       |      |        |     1  |     3 |
|    TABLE ACCESS FULL      |EMP_COMP |   105 |    8K|      1 |     1  |     15|
--------------------------------------------------------------------------------
7 rows selected.

Example 1b shows the plan when Oracle accesses all subpartitions of all partitions of a composite object. Two partition row sources are used for that purpose: a range partition row source to iterate over the partitions and a hash partition row source to iterate over the subpartitions of each accessed partition.

In example 1b, because no pruning is performed, the range partition row source iterates from partition 1 to 5. Within each partition, the hash partition row source iterates over subpartitions 1 to 3 of the current partition. As a result, the table access row source accesses subpartitions 1 to 15. In other words, it accesses all subpartitions of the composite object.

Example 2b

EXPLAIN PLAN FOR SELECT * FROM emp_comp 
WHERE hiredate = TO_DATE('15-FEB-1997', 'DD-MON-YYYY'); 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     1 |   96 |      1 |       |       |
|  PARTITION HASH ALL       |          |       |      |        |     1 |     3 |
|   TABLE ACCESS FULL       |EMP_COMP  |     1 |   96 |      1 |    13 |    15 |
--------------------------------------------------------------------------------
6 rows selected.

In example 2b, only the last partition, partition 5, is accessed. This partition is known at compile time, so we do not need to show it in the plan. The hash partition row source shows accessing of all subpartitions within that partition; that is, subpartitions 1 to 3, which translates into subpartitions 13 to 15 of the emp_comp table.

Example 3b

EXPLAIN PLAN FOR SELECT * FROM emp_comp WHERE deptno = 20; 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     2 |  200 |      1 |       |       |
|  PARTITION RANGE ALL      |          |       |      |        |     1 |     5 |
|   TABLE ACCESS FULL       |EMP_COMP  |     2 |  200 |      1 |       |       |
--------------------------------------------------------------------------------
6 rows selected.

In example 3b, the predicate deptno = 20 enables pruning on the hash dimension within each partition, so Oracle only needs to access a single subpartition. The number of that subpartition is known at compile time, so the hash partition row source is not needed.

Example 4b

VARIABLE dno NUMBER; 
EXPLAIN PLAN FOR SELECT * FROM emp_comp WHERE deptno = :dno; 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     2 |  200 |      1 |       |       |
|  PARTITION RANGE ALL      |          |       |      |        |     1 |     5 |
|   PARTITION HASH SINGLE   |          |       |      |        |   KEY |   KEY |
|    TABLE ACCESS FULL      |EMP_COMP  |     2 |  200 |      1 |       |       |
--------------------------------------------------------------------------------
7 rows selected.

Example 4b is the same as example 3b, except that deptno = 20 has been replaced by deptno = :dno. In this case, the subpartition number is unknown at compile time, and a hash partition row source is allocated. The option is SINGLE for that row source, because Oracle accesses only one subpartition within each partition. The PARTITION_START and PARTITION_STOP is set to KEY. This means that Oracle determines the number of the subpartition at run time.

Partial Partition-wise Joins

Example 1c

In this example, emp_range is joined on the partitioning column and is parallelized. This enables use of partial partition-wise join, because the dept table is not partitioned. Oracle dynamically partitions the dept table before the join.

ALTER TABLE emp PARALLEL 2; 

STATEMENT PROCESSED.

ALTER TABLE dept PARALLEL 2; 

STATEMENT PROCESSED.

To show the plan for the query, enter:

EXPLAIN PLAN FOR SELECT /*+ ORDERED USE_HASH(D) */ ename, dname 
FROM emp_range e, dept d 
WHERE e.deptno = d.deptno 

AND e.hiredate > TO_DATE('29-JUN-1996','DD-MON-YYYY');

Plan Table 
------------------------------------------------------------------------------------------------------------ 
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| PQ Distrib | Pstart| Pstop | 
------------------------------------------------------------------------------------------------------------ 
| SELECT STATEMENT           |          |     1 |   51 |      3 |      |      |            |       |       | 
|  HASH JOIN                 |          |     1 |   51 |      3 | 2,02 | P->S |QC (RANDOM) |       |       | 
|   PARTITION RANGE ITERATOR |          |       |      |        | 2,02 | PCWP |            |     4 |     5 | 
|    TABLE ACCESS FULL       |EMP_RANGE |     3 |   87 |      1 | 2,00 | PCWP |            |     4 |     5 | 
|    TABLE ACCESS FULL       |DEPT      |    21 |  462 |      1 | 2,01 | P->P |PART (KEY)  |       |       | 
------------------------------------------------------------------------------------------------------------ 
8 rows selected.

The plan shows that the optimizer selects partition-wise join, because the DIST column contains the text PART (KEY), or partition key.

Example 2c

In example 2c, emp_comp is joined on its hash partitioning column, deptno, and is parallelized. This enables use of partial partition-wise join, because the dept table is not partitioned. Again, Oracle dynamically partitions the dept table.

ALTER TABLE emp_comp PARALLEL 2; 

STATEMENT PROCESSED. 

EXPLAIN PLAN FOR SELECT /*+ ORDERED USE_HASH(D) */ ename, dname 

FROM emp_comp e, dept d 
WHERE e.deptno = d.deptno 
AND e.hiredate > TO_DATE('13-MAR-1995','DD-MON-YYYY');

Plan Table
------------------------------------------------------------------------------------------------------------ 
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| PQ Distrib | Pstart| Pstop | 
------------------------------------------------------------------------------------------------------------ 
| SELECT STATEMENT           |          |    1  |  51  |      3 |      |      |            |       |       |
|  HASH JOIN                 |          |     1 |   51 |      3 | 0,01 | P->S | QC (RANDOM)|       |       | 
|   PARTITION RANGE ITERATOR |          |       |      |        | 0,01 | PCWP |            |     4 |     5 | 
|    PARTITION HASH ALL      |          |       |      |        | 0,01 | PCWP |            |     1 |     3 | 
|     TABLE ACCESS FULL      |EMP_COMP  |     3 |   87 |      1 | 0,01 | PCWP |            |    10 |    15 | 
|   TABLE ACCESS FULL        |DEPT      |    21 |  462 |      1 | 0,00 | P->P | PART (KEY) |       |       | 
------------------------------------------------------------------------------------------------------------ 
9 rows selected.

Full Partition-wise Joins

In the following example, emp_comp and dept_hash are joined on their hash partitioning columns. This enables use of full partition-wise join. The PARTITION HASH row source appears on top of the join row source in the plan table output.

To create the table dept_hash, enter:

CREATE TABLE dept_hash 

PARTITION BY HASH(deptno) 
PARTITIONS 3 
PARALLEL 
AS SELECT * FROM dept;

To show the plan for the query, enter:

EXPLAIN PLAN FOR SELECT /*+ ORDERED USE_HASH(D) */ ename, dname 

FROM emp_comp e, dept_hash d 
WHERE e.deptno = d.deptno 


AND e.hiredate > TO_DATE('29-JUN-1996','DD-MON-YYYY');

Plan Table 
------------------------------------------------------------------------------------------------------------ 
| Operation                   |  Name    |  Rows | Bytes|  Cost  |  TQ |IN-OUT| PQ Distrib | Pstart| Pstop | 
------------------------------------------------------------------------------------------------------------ 
| SELECT STATEMENT            |          |     2 |   102|      2 |     |      |            |       |       |
|  PARTITION HASH ALL         |          |       |      |        | 4,00| PCWP |            |     1 |     3 | 
|   HASH JOIN                 |          |     2 |  102 |      2 | 4,00| P->S | QC (RANDOM)|       |       | 
|    PARTITION RANGE ITERATOR |          |       |      |        | 4,00| PCWP |            |     4 |     5 | 
|     TABLE ACCESS FULL       |EMP_COMP  |     3 |   87 |      1 | 4,00| PCWP |            |    10 |    15 | 
|    TABLE ACCESS FULL        |DEPT_HASH |    63 |    1K|      1 | 4,00| PCWP |            |     1 |     3 | 
------------------------------------------------------------------------------------------------------------ 
9 rows selected.

INLIST ITERATOR and EXPLAIN PLAN

An INLIST ITERATOR operation appears in the EXPLAIN PLAN output if an index implements an IN-list predicate. For example, for the query:

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

The EXPLAIN PLAN output appears as follows:

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 IN-list columns are described in the following sections.

Index Column

If the IN-list column empno is an index column but not a partition column, then the plan is as follows (the IN-list 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 IN-list predicate appears on the index start/stop keys.

Index and Partition Column

If empno is an indexed and a partition column, then the 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

If 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)

If emp_empno is a bitmap index, then the 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

Domain Indexes and EXPLAIN PLAN

You can also use EXPLAIN PLAN to derive user-defined CPU and I/O costs for domain indexes. EXPLAIN PLAN displays these statistics in the OTHER column of PLAN_TABLE.

For example, assume table emp has user-defined operator CONTAINS with a domain index emp_resume on the resume column, and the index type of emp_resume supports the operator CONTAINS. Then the query:

SELECT * FROM emp WHERE CONTAINS(resume, 'Oracle') = 1

might display the following plan:

	OPERATION            OPTIONS      OBJECT_NAME     OTHER 
-----------------    -----------  ------------    ----------------
SELECT STATEMENT 
TABLE ACCESS         BY ROWID     EMP
DOMAIN INDEX                      EMP_RESUME      CPU: 300, I/O: 4

EXPLAIN PLAN Restrictions

Oracle does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables. With bind variables in general, the EXPLAIN PLAN output may not represent the real execution plan.

From the text of a SQL statement, TKPROF cannot determine the types of the bind variables. It assumes that the type is CHARACTER, and gives an error message if this is not the case. You can avoid this limitation by putting appropriate type conversions in the SQL statement.

See Also:
Chapter 6, "Using SQL Trace and TKPROF".

5 Using EXPLAIN PLAN