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

MAT_VIEW REWRITE ACCESS

 

Operation: MAT_VIEW REWRITE ACCESS

 

Option: BY GLOBAL INDEX ROWID

The MAT_VIEW REWRITE ACCESS BY GLOBAL INDEX ROWID operation retrieves data from a materialized view based on global index ROWIDs after a query rewrite.

 

Option: BY INDEX ROWID

The MAT_VIEW REWRITE ACCESS BY INDEX ROWID operation retrieves data from a materialized view based on index ROWIDs after a query rewrite.

 

Option: BY LOCAL INDEX ROWID

The MAT_VIEW REWRITE ACCESS BY LOCAL INDEX ROWID operation retrieves data from a materialized view based on local index ROWIDs after a query rewrite.

 

Option: BY ROWID RANGE

The MAT_VIEW REWRITE ACCESS BY ROWID RANGE operation retrieves data from a materialized view based on a provided range of ROWIDs after a query rewrite.

 

Option: CLUSTER

The MAT_VIEW REWRITE ACCESS CLUSTER operation retrieves data using an index cluster key from a materialized view after a query rewrite.

 

Option: FULL

The MAT_VIEW REWRITE ACCESS FULL operation retrieves data based on a full table scan of a materialized view after a query rewrite.

 

Option: SAMPLE

The MAT_VIEW REWRITE ACCESS SAMPLE operation retrieves data based on a full table sample scan of a materialized view after a query rewrite.

 

Option: HASH

The MAT_VIEW REWRITE ACCESS HASH operation retrieves data using a hash cluster key from a materialized view after a query rewrite.

 

  

 

 

MERGE JOIN

Execution Plan image: image\Plan_Merge_Join_Inner.gif, image\Plan_Merge_Join_Outer.gif, image\Plan_Merge_Join_Anti.gif, image\Plan_Merge_Join_Semi.gif or image\Plan_Merge_Join_Cartesian.gif  

Join operation.

MERGE JOINto the numeric keys during the join to force a merge join to occur. 0 is used whenever Oracle cannot use an index while conducting a join. In the following example, all of the tables are fully indexed. So the example deliberately disables the indexes by adding MERGE JOIN joins tables by merging sorted lists of records from each table. It is effective for large batch operations, but may be ineffective for joins used by transaction-processing applications.

Example

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

Execution Plan

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

Interpreting the Execution Plan

There are two potential indexes that could be used by a query joining the COMPANY table to the SALES table. First, there is an index on COMPANY.COMPANY_ID - but that index cannot be used because of the +0 value added to it (disabling indexes is described in detail in the Top SQL Tuning Tips topic). Second, there is an index whose first column is SALES.COMPANY_ID - but that index cannot be used, for the same reason.

As shown in the plan, Oracle will perform a full table scan (TABLE ACCESS FULL) on each table, sort the results (using the SORT JOIN operation), and merge the result sets. The use of merge joins indicates that indexes are either unavailable or disabled by the query’s syntax.

 

Related Topics

 

MINUS

Execution Plan image: image\Plan_Minus.gif  

Set operation.

MINUS returns the set of rows from one query that is not present in the set of rows returned by a second query. The MINUS operation is used whenever you use the MINUS clause in a query.

Example

select Company_ID
from COMPANY
MINUS
select Company_ID
from COMPETITOR;

Note: This example forces the use of full table scans. Rewriting the query to use the NOTEXISTS clause could enable it to take advantage of indexes.

Execution Plan

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

Interpreting the Execution Plan

Since there are no WHERE conditions, no indexes are used by the query. Each part of the larger query is executed separately. Both tables are fully scanned. When the scans are complete, the SORT UNIQUE operations sort the results of each query and remove any duplicate rows within each set. The MINUS operation then returns to the user the rows that are returned from the first query but are not returned from the second query via the PROJECTION operation (which makes the two result sets appear as one).

 

Related Topics

 

MULTI-TABLE INSERT

 

The MULTI-TABLE INSERT operation inserts data into multiple tables.

 

  

 

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating