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
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.
.
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
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
.
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;
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
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.
No comments:
Post a Comment