Showing posts with label PROJECTION. Show all posts
Showing posts with label PROJECTION. 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 1



 UNIQUE
Sorts to eliminate duplicate rows. This typically occurs as a result of using the DISTINCT clause.


SORT UNIQUE sorts result sets and eliminates duplicate records prior to processing with the MINUS, INTERSECTION and UNION operations.

Example

A MINUS operation will be used in this example, although the SORT UNIQUE operation is also used in the INTERSECTION and UNION operation.
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 each of the queries is separately resolved (by the TABLE ACCESS FULL operations), the records are passed to the SORT UNIQUE operation prior to being input into the MINUS operation. The SORT UNIQUE operation sorts the records and eliminates any duplicates, then sends the records to the MINUS operation.
SAME IN THE CASE FOR INTERSECT ALSO.
FOR  UNION
select Company_ID  from COMPANY
UNION
select Company_ID from COMPETITOR;

Execution Plan

PROJECTION
SORT UNIQUE
UNION -ALL
TABLE ACCESS FULL COMPANY
TABLE ACCESS FULL COMPETITOR
For UnionAll operation there will not be SORT-UNIQUE OPERATION IN EXPLAIN PLAN
GROUP BY
Sorts a result set to group it for the GROUP BY clause.
SORT GROUP BY performs grouping functions on sets of records.

Example

select Zip, COUNT(*) from COMPANY group by Zip;

Execution Plan

SORT GROUP BY
TABLE ACCESS FULL COMPANY
GROUP BY  NOSORT
GROUP BY clause that does not require a sort operation.
One cause of sorting is when indexes are created . Creating an index for a table involves sorting all of the rows in a table based on the values of the indexed columns. Oracle also allows you to create indexes without sorting, using the SORT GROUP BY NOSORT operation. When the rows in the table are loaded in ascending order, you can create the index faster without sorting.

NOSORT Clause

To create an index without sorting, load the rows into the table in ascending order of the indexed column values. Your operating system may provide a sorting utility to sort the rows before you load them. When you create the index, use the NOSORT clause on the CREATE INDEX statement. For example, this CREATE INDEX statement creates the index EMP_INDEX on the ENAME column of the emp table without sorting the rows in the EMP table:
CREATE INDEX emp_index ON emp(ename)  NOSORT;

When to Use the NOSORT Clause

Presorting your data and loading it in order may not always be the fastest way to load a table. When you have a multiple-CPU computer, you may be able to load data faster using multiple processors in parallel, each processor loading a different portion of the data. To take advantage of parallel processing, load the data without sorting it first. Then create the index without the NOSORT clause. When you have a single-CPU computer, you should sort your data before loading, when possible. Then create the index by using the NOSORT clause.

GROUP BY NOSORT

Sorting can be avoided when performing a GROUP BY operation when you know that the input data is already ordered, so that all rows in each group are clumped together. This may be the case when the rows are being retrieved from an index that matches the grouped columns, or when a sort-merge join produces the rows in the right order. ORDER BY sorts can be avoided in the same circumstances. When no sort takes place, the Explain Plan output indicates GROUP BY NOSORT.
GROUP BY ROLLUP
GROUP BY clause that includes the ROLLUP option.
SORT GROUP BY ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query. For example, ROLLUP appears in the GROUP BY clause in a SELECT statement. ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns. For instance, if a query specifies ROLLUP on grouping columns of Time, Region, and Department (n=3), the result set will include rows at four aggregation levels.
GROUP BY CUBE
GROUP BY clause that includes the CUBE option.

The subtotals created by ROLLUP represent only a fraction of the possible subtotal combinations. The easiest way to generate the full set of subtotals needed for cross-tabular reports is to use the CUBE extension.
CUBE enables a SELECT statement to calculate subtotals for all of the possible combinations of a group of dimensions. It also calculates a grand total. This is the set of information typically needed for all cross-tabular reports, so CUBE can calculate a cross-tabular report with a single SELECT statement. Like ROLLUP, CUBE is a simple extension to the GROUP BY clause. When n columns are specified for a CUBE, there will be 2n combinations of subtotals returned.
 .
Index Operations
 .
AND-EQUAL
Combines the results from one or more index scans.
INDEX
Indicates an index lookup.

The following INDEX options are available:
Option
Description
Where Clause Example
SINGLE VALUE
Access a single value in the index and return a bitmap for all the matching rows.
Where State = ‘MD’
FULL SCAN
A complete scan of the index to find any matching values.
Where State not in (‘HI’, ‘AL’)
RANGE SCAN
Access a range of values in the index and return multiple bitmaps. These bitmaps are then merged into one bitmap.
Where City like ‘New*’
 .
INDEX UNIQUE SCAN
An index lookup that returns the address (ROWID) of only one row.
INDEX UNIQUE SCAN, which selects a unique value from a unique index, is the most efficient method of selecting a row from known field values.
Each unique index access is built from a separate access into the index’s B*-tree structure, drilling down from the index root to the leaf blocks. On average, three blocks are read to fulfill the unique index access.

Example

select Name, City, State from COMPANY where Company_ID = 12345;

Execution Plan

TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK

Interpreting the Execution Plan

The query uses the COMPANY_ID column as the sole criteria in its WHERE clause. Since COMPANY_ID is the primary key of the COMPANY table, it has a unique index associated with it. The unique index for the COMPANY_ID primary key is named COMPANY_PK.
During the query, the COMPANY_PK index is scanned for one COMPANY_ID value (12345). When the COMPANY_ID value is found, the ROWID associated with that COMPANY_ID is used to query the COMPANY table. 
.
RANGE SCAN
Returns the ROWID of more than one row. This can occur because the index is non-unique or because a range operator (>) was used. Indexed values are scanned in ascending order.
 .
INDEX RANGE SCAN selects a range of values from an index; the index can be either unique or non-unique. Range scans are used when one of the following conditions are met:
·         A range operator (such as < or >) is used.
·         The BETWEEN clause is used.
·         A search string with a wildcard is used (such as A*).
·         Only part of a concatenated index is used (such as by using only the leading column of a two-column index).
·         The access to the range of values within the index starts with an index search for the first row that is included in the range. After the first row has been located, there is a "horizontal" scan of the index blocks until the last row inside the range is found.
·         Note: The efficiency of an INDEX RANGE SCAN is directly related to two factors: (1) the number of keys in the selected range (the more values, the longer the search), (2) the condition of the index (the more fragmented, the longer the search).
The access to the range of values within the index starts with an index search for the first row that is included in the range. After the first row has been located, there is a "horizontal" scan of the index blocks until the last row inside the range is found.
Note: The efficiency of an INDEX RANGE SCAN is directly related to two factors: (1) the number of keys in the selected range (the more values, the longer the search), (2) the condition of the index (the more fragmented, the longer the search).

Example

select Name, City, State  from COMPANY where City > ‘Roanoke’;

Execution Plan

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 satisfies the limiting condition on the City value. Since a range of values is specified City > ‘Roanoke’, an INDEX RANGE SCAN is performed. The first value that falls within the range is found in the index; the rest of the index is then searched for the remaining values. For each matching value, the ROWID is recorded. The ROWIDs from the INDEX RANGE SCAN are used to query the COMPANY table for the Name and State values. 
.
RANGE SCAN (MIN/MAX)
Finds the highest or lowest index entry in the range.

RANGE SCAN DESCENDING
Retrieves one or more ROWIDs from an index. Indexed values are scanned in descending order.
FULL SCAN
Scans every entry in the index in key order.


.


Reading rows in key order requires a block-by-block full scan of the index, which is incompatible with the Fast Full Scan. Although the fast full scan is much more efficient than the "normal" full index scan, the fast full scan does not return rows in index order.
Although using an index can eliminate the need to perform a sort, the overhead of reading all the index blocks and all the table blocks may be greater than the overhead of performing the sort. However, using the index should result in a faster retrieval of the first row since as soon as the row is retrieved it may be returned, whereas the sort approach will require that all rows be retrieved before the first row is returned. As a result, the cost based optimizer will tend to use the index if the optimizer goal is FIRST ROWS, but will choose a full table scan if the goal is ALL ROWS.
A way of avoiding both sort and table lookup overhead is to create an index which contains all the columns in the select list as well as the columns in the ORDER BY clause. Oracle can then resolve the query by using an index lookup alone.
Using an index to avoid a sort will lead to vastly superior response time (time to retrieve the first row) but much poorer throughput (time to retrieve the last row).
.
FULL SCAN (MIN/MAX)
Finds the highest or lowest index entry.

FULL SCAN DESCENDING
Finds one or more index entries. Index entries are scanned in descending order.

FAST FULL SCAN
Scans every entry in the index in block order, possibly using multi-block read.
 .
There are many examples in which an index alone has been used to resolve a query. Providing all the columns needed to resolve the query are in the index, there is no reason why Oracle cannot use the index alone to generate the result set.
The FAST FULL INDEX SCAN operation improves the efficiency of queries that can be resolved by reading an entire index. FAST FULL INDEX SCAN offers some significant advantages over other index scan methods, as follows:
·         In an index range scan or full index scan, index blocks are read in key order, one at a time. In a full fast scan, blocks are read in the order in which they appear on disk. Oracle is able to read multiple blocks in a single I/O - depending on the value of the server parameter DB_FILE_MULTIBLOCK_READ_COUNT (multi-block reads are discussed further later in this chapter).
·         The fast full index scan can be performed in parallel, while an index range scan or full index scan can only be processed serially. That is, Oracle can allocate multiple processes to perform a fast full index scan, but can only use a single process for traditional index scans.
Although a full table scan can use parallelism and multi-block read techniques, the number of blocks in a table will typically be many times the number of blocks in an index. The fast full index scan will therefore usually outperform an equivalent full table scan.
·         You can consider a fast full index scan in the following circumstances:
·         All the columns required to satisfy the query are included in the index.
·         At least one of the columns in the index is defined as NOT NULL.
·         The query will return more than 10-20* of the rows in the index.
·         The cost based optimizer can use the fast full scan as it sees fit unless you have FAST_FULL_SCAN_ENABLED=FALSE or V733_PLANS_ENABLED=FALSE (depending on your version of Oracle).
The Index fast full scan can take advantage of optimizations normally only available to table scans, such as multi-block read and parallel query. Counting the number of rows in a table is a perfect application for the fast full scan because there will almost always be an index on a NOT NULL column which could be used to resolve the query.
.
When you are using an index to optimize a GROUP BY, a fast full index scan solution will probably result in better throughput, while a index full scan solution will probably result in better response time. When you need to scan your Index Organized table, it is essential that you take advantage of the fast full index scan. Without the fast full index scan, you will be unable to use multi-block reads or exploit parallel query capabilities.
Note: Fast full scan is disabled by default, but it is possible to enable it in Oracle by setting FAST_FULL_SCAN_ENABLED to True. Make sure that you do not inadvertently try to scan Index Organized tables with fast full scans disabled.
The fast full index scan can provide a powerful alternative to the full table scan when the query references only columns in the index.
 .
.
DOMAIN INDEX
Retrieves one or more ROWIDs from a user-defined index.
DOMAIN INDEX is a user-defined index typically created on complex datatypes whose algorithms and optimizer characteristics are provided by the user. DOMAIN INDEXES are created using the Oracle Data Cartridge Interface API.
You can use the Oracle Explain Plan to derive user-defined CPU and I/O costs for domain indexes. The Oracle 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.

Example

SELECT * FROM emp WHERE CONTAINS(resume, 'Oracle') = 1

Execution Plan

OPERATION
OPTIONS
OBJECT_NAME
OTHER
SELECT STATEMENT
TABLE ACCESS 
DOMAIN INDEX
 
BY ROWID
 
EMP
EMP_RESUME
 
CPU: 300, I/O: 4