Oracle's explain plan

Whenever you read or write data in Oracle, you do so by issuing an SQL statement. One of Oracle's task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.

If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this.

The general syntax of EXPLAIN PLAN is:

explain plan for your-precious-sql-statement;

If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:

explain plan into table_name for your-precious-sql-statement;

If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

The Plan Table

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.

The fields (attributes) within the plan table

Arguably, the most important fields within the plan table are operation, option, object_name, id, and parent_id. The pair operation and object_name define what operation would be done on (or with) object_name. If an operation has an id which other operations have as parent_id, it means the other operations feed their result to the parent.

Possible values for operation are:

DELETE STATEMENT
INSERT STATEMENT
SELECT STATEMENT
UPDATE STATEMENT
AND-EQUAL
CONNECT BY
CONCATENATION
COUNT
DOMAIN INDEX
FILTER
FIRST ROW
FOR UPDATE
HASH JOIN
INDEX
INLIST ITERATOR
INTERSECTION
MERGE JOIN
MINUS
NESTED LOOPS
PARTITION,
REMOTE
SEQUENCE
SORT
TABLE ACCESS
UNION
VIEW

Option tells more about how an operation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others. Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.

dbms_xplan

As of 9i, dbms_xplan can be used to format the plan table.

Operations

The following table is used to demonstrate EXPLAIN PLAN:

create table test_for_ep (a number, b varchar2(100));

Now, let's explain the plan for selecting everything on that table:

delete plan_table;
explain plan for select /*+ rule */ * from test_for_ep where a = 5;

Displaying the execution plan

In order to view the explained plan, we have to query the plan table:

select 
  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", 
  object_name                                                              "Object"
from 
  plan_table 
start with id = 0 
connect by prior id=parent_id;

This statement is a simplified version of utlxpls.sql. utlxpls.sql is a script that Oracle ships.

Here's the output of the explain plan:

SELECT STATEMENT ()
 TABLE ACCESS (FULL)           TEST_FOR_EP

First, take a look at the indention: TABLE ACCESS is indented right. In an explain plan output, the more indented an operation is, the earlier it is executed. And the result of this operation (or operations, if more than one have are equally indented AND have the same parent) is then feeded to the parent operation. In this case, TABLE ACCESS is made first, and its result feeded to SELECT STATEMENT (which is not an actual operation). Note the FULL in paranthesis in TABLE ACCESS: this means that the entire table is accessed.

Btw, sql*plus automatically explains the plan for you if autotrace is enabled.

Now, let's create an index on that table:

create index test_for_ep_ix on test_for_ep (a);

And do the same select statement again:

delete plan_table;
explain plan for select /*+ rule */ * from test_for_ep where a = 5;

SELECT STATEMENT ()
 TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
  INDEX (RANGE SCAN)           TEST_FOR_EP_IX

Obviously, the index (TEST_FOR_EP_IX) is used first (most indented) then used for a TABLE ACCESS, second most indented, then the result is returned. The table access is not done by a full table scan but rather by using the data's rowid.

INDEX

In the last example, Oracle employed an INDEX (RANGE SCAN). The RANGE SCAN basically means, that the index was used, but that it can return more than one row. Now, we create a unique index to see how this alters the explain plan:

create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a));

delete plan_table;
explain plan for select /*+ rule */ * from test_for_ep where a = 5;

The explained plan is:

SELECT STATEMENT ()
 TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
  INDEX (UNIQUE SCAN)          UQ_TP

INDEX (UNIQUE SCAN) means, that this index is used, and it sort of guarantees that this index returnes exactly one rowid. What happens, if we query the field not for equality but for greater than (a>5)?

explain plan for select /*+ rule */ * from test_for_ep where a > 5;

Here, we see that the index is used, but for a RANGE SCAN:

SELECT STATEMENT ()
 TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
  INDEX (RANGE SCAN)           UQ_TP

If we only query fields of a table that are already in an index, Oracle doesn't have to read the data blocks because it can get the relevant data from the index:

create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a));

delete plan_table;
explain plan for select /*+ rule */ a from test_for_ep where a > 5 and a < 50;

Here's the query execution planexecution plan. No table access anymore!

SELECT STATEMENT ()
 INDEX (RANGE SCAN)            UQ_TP

MERGE JOIN

See here. The first table's join key is ba while the second table's join key is aa.

create table test_for_ep_a (aa number, ab varchar2(100));
create table test_for_ep_b (ba number, bb varchar2(100));

Note, there are no indexes on both of the tables. Now, we join the tables on aa and ba:

explain plan for 
  select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where 
  a.aa=b.ba and a.aa > 5;

As there are no indexes, both tables must be TABLE ACCESSed (FULL). After these accesses, their results are sorted.

SELECT STATEMENT ()
 MERGE JOIN ()
  SORT (JOIN)
   TABLE ACCESS (FULL)         TEST_FOR_EP_B
  SORT (JOIN)
   TABLE ACCESS (FULL)         TEST_FOR_EP_A

Note MERGE JOINs can only be used for equi joins, as is demonstrated in NESTED LOOPS

NESTED LOOPS

For each relevant row in the first table (driving table), find all matching rows in the other table (probed table).

create table test_for_ep_a (aa number, ab varchar2(100));
create table test_for_ep_b (ba number, bb varchar2(100));

explain plan for 
  select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where 
  a.aa > b.ba and a.aa > 5;

Note, there is no equi join to join test_for_ep_a and test_for_ep_b, (a.aa > b.ba)

SELECT STATEMENT ()
 NESTED LOOPS ()
  TABLE ACCESS (FULL)          TEST_FOR_EP_B
  TABLE ACCESS (FULL)          TEST_FOR_EP_A

Now, we put an index on TEST_FOR_EP_B and see how that influences our nested loop:

create table test_for_ep_a (aa number, ab varchar2(100));
create table test_for_ep_b (ba number, bb varchar2(100), constraint uq_ba unique(ba));

delete plan_table;
explain plan for 
  select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where 
  a.aa > b.ba;

SELECT STATEMENT ()
 NESTED LOOPS ()
  TABLE ACCESS (FULL)          TEST_FOR_EP_A
  INDEX (RANGE SCAN)           UQ_BA

Interpreted, this means: TEST_FOR_EP_A is fully accessed and for each row, TEST_FOR_EP_B (or more accurately, its index UQ_BA) is probed. Thinking about it, this makes sense, doing the costly TABLE ACCESS once and use the index for each row. Then again, thinking about it, if TEST_FOR_EP_A is very small nad TEST_FOR_EP_B is large, this doesn't make sense anymore. This is when the Cost Based Optimizer comes into play.

Sorts

Aggregate Sorts

Whenever a result set must be sorted, the operation is sort. If this sort is used to return a single row (for example max or min) the options is AGGREGATE. Consider the following example:

create table t_ep (
  w date,
  v number,
  x varchar2(40)
);


delete plan_table;
explain plan for select /*+ rule */ max(w) from t_ep where v=4;

SELECT STATEMENT ()
 SORT (AGGREGATE)
  TABLE ACCESS (FULL)          T_EP

Now: creating an index:

alter table t_ep add constraint uq_t_ep unique(v);
delete plan_table;
explain plan for select /*+ rule */ max(w) from t_ep where v=4;

SELECT STATEMENT ()
 SORT (AGGREGATE)
  TABLE ACCESS (BY INDEX ROWID) T_EP
   INDEX (UNIQUE SCAN)          UQ_T_EP