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-1981','DD-MON-YYYY')), PARTITION EMP_P2 VALUES LESS THAN (TO_DATE('1-JAN-1983','DD-MON-YYYY')), PARTITION EMP_P3 VALUES LESS THAN (TO_DATE('1-JAN-1985','DD-MON-YYYY')), PARTITION EMP_P4 VALUES LESS THAN (TO_DATE('1-JAN-1987','DD-MON-YYYY')), PARTITION EMP_P5 VALUES LESS THAN (TO_DATE('1-JAN-1989','DD-MON-YYYY')) ) AS SELECT * FROM EMP;
Example 1:
EXPLAIN PLAN FOR SELECT * FROM EMP_RANGE;
Then 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 1, 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 2:
EXPLAIN PLAN FOR SELECT * FROM EMP_RANGE WHERE HIREDATE >= TO_DATE('1-JAN-1985','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 2, the partition row source iterates from partition 4 to 5
because we prune the other partitions using a predicate on HIREDATE.
Example 3:
EXPLAIN PLAN FOR SELECT * FROM EMP_RANGE WHERE HIREDATE < TO_DATE('1-JAN-1981','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 3, 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-1981','DD-MON-YYYY')), PARTITION EMP_P2 VALUES LESS THAN (TO_DATE('1-JAN-1983','DD-MON-YYYY')), PARTITION EMP_P3 VALUES LESS THAN (TO_DATE('1-JAN-1985','DD-MON-YYYY')), PARTITION EMP_P4 VALUES LESS THAN (TO_DATE('1-JAN-1987','DD-MON-YYYY')), PARTITION EMP_P5 VALUES LESS THAN (TO_DATE('1-JAN-1989','DD-MON-YYYY')) ) AS SELECT * FROM EMP;
Example 1:
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 1 shows the explain 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 this example, since 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 2:
EXPLAIN PLAN FOR SELECT * FROM EMP_COMP WHERE HIREDATE = TO_DATE('15-FEB-1987', '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 2, 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 3:
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 this example, 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 4:
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 4 is the same as example 3 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 Oracle will determine the
number of the subpartition at run time.
Partial Partition-wise Joins
Example 1:
In the following 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-1986','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 select partition-wise join because the
DIST column contains the text "PART (KEY)", or, partition key.
Example 2:
In example 2, 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-1985','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-1986','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 INDEX 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
Formatting EXPLAIN PLAN Output
This section shows options for formatting EXPLAIN PLAN output
- Using the EXPLAIN PLAN Statement
- Selecting PLAN_TABLE Output in Table Format
- Selecting PLAN_TABLE Output in Nested Format
Using the EXPLAIN PLAN Statement
The following example shows a SQL statement and its corresponding
execution plan generated by EXPLAIN PLAN. The sample query retrieves
names and related information for employees whose salary is not within
any range of the SALGRADE table:
SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal);
This EXPLAIN PLAN statement generates an execution plan and places the output in PLAN_TABLE:
EXPLAIN PLAN SET STATEMENT_ID = 'Emp_Sal' FOR SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal);
Selecting PLAN_TABLE Output in Table Format
This SELECT statement:
SELECT operation, options, object_name, id, parent_id, position, cost, cardinality,other_tag, optimizerFROM plan_tableWHERE statement_id = 'Emp_Sal'ORDER BY id;
Generates this output:
OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION COST CARDINALITY BYTES OTHER_TAG OPTIMIZER-----------------------------------------------------------------------------------------------SELECT STATEMENT 0 2 2 1 62 CHOOSEFILTER 1 0 1NESTED LOOPS 2 1 1 2 1 62TABLE ACCESS FULL EMP 3 2 1 1 1 40 ANALYZEDTABLE ACCESS FULL DEPT 4 2 2 4 88 ANALYZEDTABLE ACCESS FULL SALGRADE 5 1 2 1 1 13 ANALYZED
The ORDER BY clause returns the steps of the execution plan sequentially
by ID value. However, Oracle does not perform the steps in this order.
PARENT_ID receives information from ID, yet more than one ID step fed
into PARENT_ID.
For example, step 2, a merge join, and step 6, a table access, both fed
into step 1. A nested, visual representation of the processing sequence
is shown in the next section.
The value of the POSITION column for the first row of output indicates
the optimizer's estimated cost of executing the statement with this plan
to be 5. For the other rows, it indicates the position relative to the
other children of the same parent.
CREATE VIEW test AS SELECT id, parent_id, lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '|| decode(id, 0, 'Cost = '||position) "Query Plan" FROM plan_table START WITH id = 0 and statement_id = 'TST' CONNECT BY prior id = parent_id and statement_id = 'TST'; SELECT * FROM foo ORDER BY id, parent_id;
This yields results as follows:
ID PAR Query Plan --- --- -------------------------------------------------- 0 Select Statement Cost = 69602 1 0 Nested Loops 2 1 Nested Loops 3 2 Merge Join 4 3 Sort Join 5 4 Table Access Full T3 6 3 Sort Join 7 6 Table Access Full T4 8 2 Index Unique Scan T2 9 1 Table Access Full T1 10 rows selected.
Selecting PLAN_TABLE Output in Nested Format
This type of SELECT statement generates a nested representation of the
output that more closely depicts the processing order used for the SQL
statement.
SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options ||' '||object_name ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan" FROM plan_table START WITH id = 0 AND statement_id = 'Emp_Sal' CONNECT BY PRIOR id = parent_id AND statement_id ='Emp_Sal'; Query Plan ------------------------------ SELECT STATEMENT Cost = 5 FILTER NESTED LOOPS TABLE ACCESS FULL EMP TABLE ACCESS FULL DEPT TABLE ACCESS FULL SALGRADE
The order resembles a tree structure, as illustrated in Figure 13-1.
Figure 13-1 Tree Structure of an Execution Plan
Tree structures illustrate how SQL statement execution operations feed
one another. Oracle assigns each step in the execution plan a number
representing the ID column of the PLAN_TABLE. Each step is depicted by a
"node". The result of each node's operation passes to its parent node,
which uses it as input.
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.
