Chat now with support
Chat with Support

SQL Optimizer for Oracle 10.0 - User Guide

Welcome to SQL Optimizer
About SQL Optimizer SQL Optimization Workflow New in This Release Additional Resources Database Privileges Database Privileges Script Connect to the Database Windows Layout Customize Toolbars Keyboard Shortcuts Support Bundle Register SQL Optimizer Check for Updates SQL Operations
ALL PARTITION ALTER INDEX AND EQUAL ANTI JOIN BITMAP AND BITMAP COMPACTION BITMAP CONSTRUCTION BITMAP CONVERSION BITMAP INDEX BITMAP JOIN INDEX UPDATE BITMAP JOIN INDEX UPDATE STATEMENT BITMAP KEY ITERATION BITMAP MERGE BITMAP MINUS BITMAP OR BUFFER SORT CARTESIAN JOIN COLLECTION ITERATOR CONCATENATION CONNECT BY CONNECT BY PUMP COUNT COUNT STOPKEY CREATE AS SELECT CUBE SCAN DDL STATEMENT DELETE DOMAIN INDEX FAST FULL INDEX SCAN FILTER FIRST ROWS FIXED INDEX FIXED TABLE FOR UPDATE FULL INDEX SCAN FULL INDEX SCAN DESCENDING FULL INDEX SCAN (MIN/MAX) HASH GROUP BY HASH GROUP BY PIVOT HASH JOIN HASH JOIN BUFFERED HASH PARTITION HASH UNIQUE INDEX INDEX BUILD NON UNIQUE INDEX RANGE SCAN INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN (MIN/MAX) INDEX SAMPLE FAST FULL SCAN INDEX SKIP SCAN INDEX SKIP SCAN DESCENDING INDEX UNIQUE SCAN INLIST ITERATOR INLIST PARTITION INSERT INTERSECTION INTO INVALID PARTITION ITERATOR PARTITION LOAD AS SELECT MAT_VIEW ACCESS MAT_VIEW REWRITE ACCESS MERGE JOIN MINUS MULTI-TABLE INSERT NESTED LOOPS OUTER JOIN PARTITION PARTITION HASH EMPTY PARTITION LIST PARTITION RANGE PROJECTION PX BLOCK ITERATOR PX COORDINATOR PX ITERATOR PX PARTITION PX PARTITION HASH ALL PX PARTITION LIST ALL PX PARTITION RANGE ALL PX RECEIVE PX SEND RANGE PARTITION RECURSIVE EXECUTION RECURSIVE WITH PUMP REFERENCE MODEL REMOTE SELECT SEMI JOIN SEQUENCE SINGLE PARTITION SINGLE RANGE PARTITION SORT SORT AGGREGATE SORT GROUP BY SORT GROUP BY CUBE SORT GROUP BY NOSORT SORT GROUP BY ROLLUP SORT JOIN SORT ORDER BY SORT UNIQUE SQL MODEL TABLE ACCESS TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ACCESS BY INDEX ROWID TABLE ACCESS BY LOCAL INDEX ROWID TABLE ACCESS BY ROWID TABLE ACCESS BY USER ROWID TABLE ACCESS CLUSTER TABLE ACCESS FULL TABLE ACCESS HASH TABLE ACCESS SAMPLE TABLE QUEUE TEMP TABLE GENERATION TEMP TABLE TRANSFORMATION UNION UNION ALL UNION ALL (RECURSIVE WITH) UNPIVOT UPDATE VIEW VIEW PUSHED PREDICATE WINDOW
Optimize SQL
Create Optimize SQL Sessions Open Optimizer SQL Sessions Rewrite SQL Generate Execution Plan Alternatives
Optimize Indexes Batch Optimize SQL Scan SQL Inspect SGA Analyze Impact Manage Plans Configure Options SQL Optimizer Tutorials About Us Legal Notices

TEMP TABLE TRANSFORMATION

 

The TEMP TABLE TRANSFORMATION operation retrieves data for a temporary table used in star transformations.

 

  

 

 

UNION

Execution Plan image: image\Plan_Union.gif   

Set operation.

UNIONreturns a single set of rows from the results of two or more queries. All duplicate results are removed. To perform this operation, Oracle first retrieves all the rows from both SELECT statements, sorts them, and then performs the Union merge.

Example

select Company_ID
from COMPANY
UNION
select Company_ID
from COMPETITOR;

The query in the preceding listing will return a single set of Company_ID values&emdash;all Company_ID values in either COMPANY or COMPETITOR Table. Duplicate COMPANY_ID values are eliminated.

Execution Plan

PROJECTION
SORT UNIQUE
UNION-ALL
TABLE ACCESS FULL COMPANY
TABLE ACCESS FULL COMPETITOR

Interpreting the Execution Plan

The Execution Plan contains a surprise&emdash;a UNION actually performs a UNION ALL! UNION returns unique records that are found in two separate queries. UNION ALL returns all records, including duplicates that are found in two separate queries. A UNION is, within the optimizer, the combination of a UNION ALL (which makes one concatenated result set out of the two queries’ output) and a SORT UNIQUE (which then eliminates the duplicates).

Since no WHERE clauses are used in either query, Oracle will perform a full table scan on each table. The UNION-ALL operation merges the two result sets into a single result set. SORT UNIQUE then eliminates the duplicate values and send the records to the PROJECTION operation for output to the user.

 

Related Topics

UNION ALL

Execution Plan image: image\Plan_Union-All.gif   

Set operation.

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, the optimizer can make the transformation. The optimizer then chooses an Execution 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, 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.

 

UNION ALL (RECURSIVE WITH)

 

Operation: UNION ALL (RECURSIVE WITH)

 

Option: BREADTH FIRST

The UNION ALL (RECURSIVE WITH) BREADTH FIRST operation retrieves data from child rows only after retrieving data from all sibling rows.

 

Option: DEPTH FIRST

The UNION ALL (RECURSIVE WITH) DEPTH FIRST operation retrieves data from sibling rows only after retrieving data from all child rows.

 

  

 

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating