UNDERSTANDING EXPLAIN PLAN

    Explain Plan a small overview

            To interpret an execution plan and correctly evaluate your SQL optimization options, you need to first understand the differences between the available database operations. The following topics describe each database access operation identified by the name given to it by the Oracle EXPLAIN PLAN command, along with its characteristics.

For each type of operation, an example is provided. In some cases, the example for an operation will use operations described later in the topic (such as MERGE JOIN, which always uses a SORT JOIN operation).

The operations are classified as row operations or set operations. The following list describes the differences between row and set operations. 

Row Operations	Set Operations
Executed on one row at a time	Executed on a result set of rows
Executed at the FETCH stage, if there is no set operation involved	Executed at the EXECUTE stage when the cursor is opened
Ability to view the first result before the last row is fetched	Inability to view the first result until all rows are fetched and processed
Example: A full-table scan	Example: A full-table scan with a GROUP BY

Aggregation Operations

The following Aggregation operations are available.

COUNT

Counts the rows in the result set to satisfy the COUNT() function

COUNT is executed when the RowNum pseudo-column is used without specifying a maximum value for RowNum. COUNT receives rows from its child operations and increments the RowNum counter. If a limiting counter is used on the RowNum pseudo-column, then the COUNT STOPKEY operation is used instead of COUNT. 
.

Example

.

select Name, City, State, RowNum from COMPANY where City > ‘Roanoke’order by Zip;

.

The query shown in the preceding listing selects rows from the COMPANY. Each row will have the original row number returned.

Execution Plan

SORT ORDER BY
COUNT
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 table that satisfy the WHERE clause condition (where City > ‘Roanoke’). The ROWIDs from the City index scan are used to query the COMPANY table for the Name and State column values. For each row returned, the counter is incremented. Because of the use of the index, the rows that are returned will be the "lowest" city names that are greater than the value ‘Roanoke’. The rows will be returned from the COMPANY$CITY index in ascending order of the City column’s value. The RowNum pseudo-column will then be calculated and put into the row. The SORT ORDER BY operation will order the rows by Zip, as requested in the ORDER BY clause.  The RowNum values are assigned before the ordering takes place.

.

STOPKEY

Counts the numbers of rows returned by a result set and stop processing when a certain number of rows are reached. This is usually the result of a WHERE clause which specifies a maximum ROWNUM (for example, WHERE ROWNUM <=10).

­­­Example 

.

 

Select Name, City, State from COMPANY where City > ‘Roanoke’ and Rownum <= 100;
.

Execution Plan

COUNT STOPKEY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

SORT	Performs a sort.
ORDER BY	Sorts a result set to satisfy an ORDER BY clause.
AGGREGATE		Occurs when a group function is used on data, which is already grouped.

SORT AGGREGATE is used to sort and aggregate result sets whenever a grouping function appears in a SQL statement without a GROUP BY clause. Grouping functions include MAX, MIN, COUNT, SUM, and AVG.

Example 

.

select SUM(Total) from SALES;
.

SORT AGGREGATE
TABLE ACCESS FULL SALES
.

The Execution Plan shows that after the SALES table is scanned (via the TABLE ACCESS FULL operation), the records are passed to the SORT AGGREGATE operation. SORT AGGREGATE sums the Total values and returns the output to the user.

JOIN	Sorts the rows in preparation for a merge join.

SORT JOIN sorts a set of records that is to be used in a MERGE JOIN operation.

Example

select C.Name  from COMPANY c, SALES s where C.Company_ID+0 = S.Company_ID+0
and S.Period_ID =3 and S.Sales_Total>1000;

Execution Plan

MERGE JOIN
SORT JOIN
TABLE ACCESS FULL SALES
SORT JOIN
TABLE ACCESS FULL COMPANY

Interpreting the Execution Plan

The Execution Plan shows that the COMPANY table and SALES table will be accessed using TABLE ACCESS FULL operations. Before the records from those tables are passed to the MERGE JOIN operation, they will first be processed by SORT JOIN operations that sort the records. The SORT JOIN output is used as input to the MERGE JOIN operation.