Showing posts with label set operation. Show all posts
Showing posts with label set operation. Show all posts

Jul 13, 2011

UNDERSTANDING EXPLAIN PLAN 4

Set Operations

CONCATENATION
Merges multiple result sets in the same way as in an explicit UNION statement. This typically occurs when an Or statement is used with indexed columns.
CONCATENATION performs a UNION ALL (a UNION without elimination of duplicate values) of result sets.

Example

select Name, City, State from COMPANY where State = ‘TX’ and City in (‘Houston’, ‘Austin’, ‘Dallas’);
This query selects from the COMPANY Table based on two criteria in the WHERE clause. The criteria can use the COMPANY$CITY index and the COMPANY$STATE index to obtain ROWIDs for the rows to be returned. Since the NAME column is required by the query, the COMPANY table will have to be accessed; the other column values are already available via the index searches.
The query of the CITY column uses an IN clause, which is functionally equivalent to an OR clause. The query could be rewritten as:
select Name, City, State  from COMPANY where State = ‘TX’ and (City = ‘Houston’
or City = ‘Austin’ or City = ‘Dallas’);
Taking this expansion one step further, the STATE portion of the query can be placed into each section of the OR clause. The revised query would now read:
select Name, City, State from COMPANY where (State = ‘TX’ and City = ‘Houston’)
or (State = ‘TX’ and City = ‘Austin’) or (State = ‘TX’ and City = ‘Dallas’);
This query helps with understanding the plan that is generated.

Execution Plan

CONCATENATION
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE

Interpreting the Execution Plan

The Execution Plan shows that the query is executed as if the IN clause is rewritten as an OR clause, and the other criteria are placed within the OR clauses. Within each OR clause, an AND-EQUAL operation is performed to merge the lists of ROWIDs returned from the index scans. The ROWIDs returned by the AND-EQUAL operations are then used to select the requested columns from the COMPANY table via a TABLE ACCESS BY ROWID operation. The resulting records from each part of the query are then concatenated to carry out the OR clause.
Note: When the query is very complex, the Optimizer may decide not to use the CONCATENATION operation. Instead, it will use partial index range scans. If you want to force the use of the CONCATENATION operation, you may need to use the format shown in the last example query.
INTERSECTION
Compares two result sets and returns only rows common to both. This operation usually only takes place as a result of an explicit use of the INTERSECTION clause.
Set operation.
INTERSECTION is used to merge sets of records returned by multiple queries; in this sense, it is analogous to the index record merge performed by AND-EQUAL. INTERSECTION is used when the INTERSECT clause is used in a query.
Most INTERSECT queries should be rewritten to use joins instead, to improve their ability to use row operations instead of set operations. The following example could be rewritten to use a join, but for purposes of illustration is shown using an INTERSECT clause.

Example

select Company_ID from COMPANY where State = ‘AZ’ and Parent_Company_ID is null
INTERSECT
select Company_ID from COMPETITOR;
The query in the previous listing will select all COMPANY_IDs from the COMPANY table whose state value is ‘AZ’, whose Parent_Company_ID value is null, and whose Company_ID value also is present in the COMPETITOR table.

Execution Plan

PROJECTION
INTERSECTION
SORT UNIQUE
TABLE ACCESS BY ROWID COMPANY
 INDEX RANGE SCAN COMPANY$STATE
SORT UNIQUE
TABLE ACCESS FULL COMPETITOR

Interpreting the Explain Plan

The Execution Plan shows the INTERSECTION operation, along with two operations described later: SORT UNIQUE and PROJECTION.
The Explain Plan shows that each of the queries is executed separately, and the results of the two queries are returned to the INTERSECTION operation. In the first (top) query, the index on the State column is used, and the ROWID values returned from that index scan are used to select records from the COMPANY table. During the TABLE ACCESS BY ROWID on the COMPANY table, the Parent_Company_ID is null criteria is applied (via an implicit FILTER operation).
The second (bottom) query does not have any WHERE clause criteria, so a TABLE ACCESS FULL (full table scan) of the COMPETITOR table is performed. The results of each query are then sorted separately via the SORT UNIQUE operations—only unique Company_ID values will be passed on to the INTERSECTION operation.
The INTERSECTION operation takes the sorted rows from the two queries and returns to the user the rows that are common to both queries via the PROJECTION operation (which makes the two result sets appear as one).

Advanced INTERSECTION Example

A second example of INTERSECTION involves the CONNECT BY operation. Assume that you want to add a row into the COMPANY table, with a Company_ID =10 and Parent_Company_ID =5. How can you be sure that there will be no loops in the hierarchical structure of the data in the COMPANY table? The query in the following listing checks for the intersection of two searches - one up the tree, and one down the tree. If the intersection of these two queries returns a row, then there is a loop in the hierarchy of the Company_ID values.

Example

select Company_ID, Name from COMPANY where State = ‘VA’
connect by Parent_Company_ID = prior Company_ID /*down the tree*/
start with Company_ID = 10 INTERSECTION
select Company_ID, Name from COMPANY where State = ‘VA’
connect by Company_ID = prior Parent_Company_ID /*up the tree*/
start with Company_ID = 5;
If the query in the preceding listing does not return any rows, then there will be no loop in the Company_ID hierarchy if you insert a row with a Company_ID value of 10 and a Parent_Company_ID value of 5.
PROJECTION
Returns a single set of records from the results of multiple queries. PROJECTION is used by the INTERSECTION, MINUS, and UNION operations.
PROJECTION is used by the INTERSECTION, MINUS, and UNION operations to return a single set of records from the results of multiple queries.

Example

select Company_ID from COMPANY MINUS select Company_ID  from COMPETITOR;

Execution Plan

PROJECTION
MINUS
SORT UNIQUE
TABLE ACCESS FULL COMPANY
SORT UNIQUE
TABLE ACCESS FULL COMPETITOR

Interpreting the Execution Plan

The Execution Plan shows that after the MINUS operation is performed, the PROJECTION operation resolves the output into a single set of data for output to the user. PROJECTION is always used in conjunction with the MINUS, INTERSECTION, and UNION operations.



MINUS
Returns all result sets in the first result set, except those appearing in the second result set. This occurs as a result of the MINUS set operator.
UNION ALL
Combines two result sets and returns rows from both are returned.
When a query contains a WHERE clause with multiple conditions combined with OR operators, the optimizer transforms the query into an equivalent compound query that uses the UNION ALL set operator. The optimizer determines whether this modified query will execute more efficiently in the following manner:
·         When each condition individually makes an index access path available, then the optimizer can make the transformation. The optimizer then chooses an Explain Plan for the resulting statement that accesses the table multiple times using the different indexes, and then puts the results together.
·         When any condition requires a full table scan because it does not make an index available, then the optimizer does not transform the statement. The optimizer chooses a full table scan to execute the statement, and Oracle tests each row in the table to determine whether it satisfies any of the conditions.
·         For statements that use the cost-based optimizer, the optimizer may use statistics to determine whether to make the transformation by estimating and then comparing the costs of executing the original statement versus the resulting statement.
·         The cost-based optimizer does not use the OR transformation for IN-lists or ORs on the same column; instead, it uses the INLIST ITERATOR operator.

Example

In the following query, the WHERE clause contains two conditions combined with an OR operator:
SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10;
When there are indexes on both the job and deptno columns, the optimizer may transform this query into the equivalent query as follows:
SELECT * FROM emp WHERE job = 'CLERK' UNION ALL
SELECT * FROM emp WHERE deptno = 10 AND job <> 'CLERK';
When the cost-based optimizer is deciding whether to make a transformation, the optimizer compares the cost of executing the original query using a full table scan with that of executing the resulting query.
With the rule-based optimizer, the optimizer makes this UNION ALL transformation, because each component query of the resulting compound query can be executed using an index. The rule-based optimizer assumes that executing the compound query using two index scans is faster than executing the original query using a full table scan.
VIEW
Accesses a view definition or creates a temporary table to store a result set.
VIEW resolves any query nested deeper than the VIEW operation into a temporary area. The use of VIEW may be caused by correlated queries or by the inability of Oracle to pull a view’s query into the rest of a larger query, forcing it to resolve the view separately.
The example is in two parts: a view is created and then queried. The plan that follows applies only to the query of the view, not to the view creation itself.

Example

create view COMPANY_COUNT as select Zip, COUNT(*) Company_Count
from COMPANY group by Zip;

select Zip, Company_Count from COMPANY_COUNT where Company_Count BETWEEN 10 and 20;

Execution Plan

VIEW COMPANY_COUNT
FILTER
SORT GROUP BY
TABLE ACCESS FULL COMPANY

Interpreting the Explain Plan

Because there is a set operation (SORT GROUP BY) within the view syntax, the optimizer must resolve the view before executing the conditions specified in the query. All the rows are fetched from COMPANY table using a full table scan, then they are sorted and counted by Zip during the SORT GROUP BY operation. The WHERE clause condition in the query is applied during the FILTER operation on the result of the view.
WINDOW
Consists of a physical or logical set of rows used for calculating the result of an analytical function.
The WINDOW operation consists of a physical or logical set of rows used for calculating the result of an analytical function. The function is then applied to all the rows in the window. The window "slides" through the query result set or partition from top-to-bottom.

Jun 29, 2011

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.
.
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.