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.
Execution Plan image: ,
,
,
or
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.
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;
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL SALES
SORT JOIN
TABLE ACCESS FULL COMPANY
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.
Execution Plan image:
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.
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.
PROJECTION
MINUS
SORT UNIQUE
TABLE ACCESS FULL COMPANY
SORT UNIQUE
TABLE ACCESS FULL COMPETITOR
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).
The MULTI-TABLE INSERT operation inserts data into multiple tables.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center