Chat now with support
Chat mit 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

ALTER INDEX

 

The ALTER INDEX statement alters an existing index.

 

 

  

 

 

AND EQUAL

Execution Plan image: image\Plan_Index_And_Equal.gif   

Index operation.

The AND-EQUAL command merges sorted lists of values returned by indexes. It returns a list of values that are common to both lists (such as the ROWIDs that are found in two separate indexes). The AND-EQUAL command is used for merges of non-unique indexes and range scans of unique indexes.

Example

select Name, City, State
from COMPANY
where City = 'Roanoke'
and State = 'VA';

This query selects from the COMPANY table based on two values in the WHERE clause. These values can use the COMPANY$CITY index on the City column and the COMPANY$STATE index on the State column to obtain ROWIDs for the rows they return. Since the Name column is required by the query, the COMPANY table is accessed. The other column values are already available from the index searches.  

Execution Plan

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 two non-unique indexes - on the City column and on the State column - are scanned for values matching the WHERE clause criteria. The ROWIDs from each index scan are placed in sorted lists. The AND-EQUAL operation merges the two lists and generates a single list of ROWIDs that were present in both lists. When a ROWID exists in only one of the indexes, it will not be returned by the AND-EQUAL operation. Since the WHERE clause contains an AND clause, the AND-EQUAL operation is used to prevent ROWIDs from being returned from the indexes unless the ROWIDs are found in both indexes. The ROWIDs are then used to access the rows in the COMPANY table that satisfy the WHERE clause criteria, and the query is complete.

 

ANTI JOIN

Execution plan image: image\Plan_Connect_By_Anti.gif, image\Plan_Nested_Loops.gif, image\Plan_Merge_Join_Anti.gif or image\Plan_Hash_Join.gif   

 

An ANTI-JOIN is a query that returns rows in one table that do not match some set of rows from another table. Since this is effectively the opposite of normal join behavior, the term ANTI-JOIN has been used to describe this operation. ANTI-JOINs are usually expressed using a sub-query, although there are alternative formulations.

One method of using an ANTI-JOIN query is to combine the IN operator with the NOT operator. This method works well when using the cost-based optimizer.

The rule-based optimizer method of using an ANTI-JOIN query is to use it with the NOT EXISTS operator in place of NOT IN. This method uses the WHERE clause in the sub-query.

Note: When Oracle is using rule-based optimization, avoid using NOT IN to perform an anti-join. Use NOT EXISTS instead.

You can also implement the ANTI-JOIN operation as an OUTER JOIN. An OUTER JOIN includes NULL for rows in the inner table, which have no match in the outer table. This feature can be used to include only rows that have no match in the inner table. However, the most efficient implementation is to use HASH JOIN and its hints.

To take advantage of Oracle’s ANTI-JOIN optimizations, the following must be true:

  • Cost-based optimization must be enabled.

  • The ANTI-JOIN columns used must not be NULL. This either means that they are not NULL in the table definition, or an IS NOT NULL clause displays in the query for all the relevant columns.

  • The subquery is not correlated.

  • The parent query does not contain an Or clause.

  • The database parameter ALWAYS_ANTI_JOIN is set to either MERGE or HASH or a MERGE_AJ or HASH_AJ hint displays within the sub-query.

 

 

BITMAP AND

Execution Plan image: image\Plan_Bitmap_Index.gif

Row operation.

The BITMAP AND operation is a bit-wise AND between two bitmap indexes; the entries of two bitmap indexes are compared and an entry is returned if it meets the criteria in both bitmaps. The result of a BITMAP AND is a list of all the values that are in both bitmap indexes. The BITMAP AND operation is functionally equivalent to the AND-EQUAL operation between B-Tree indexes.

The City and State columns of the COMPANY are indexed via separate single-column non-unique B*-tree indexes. For the BITMAP operations, only those two indexes are replaced by bitmap indexes.

Example

select Name, City, State
from COMPANY
where City = 'Roanoke'
and State = 'VA';

The query shown in the preceding listing selects rows from the COMPANY table for all companies in Roanoke, VA.

Execution Plan

TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY'
BITMAP CONVERSION (TO ROWIDS)
BITMAP AND
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$CITY'
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$STATE'

Interpreting the Execution Plan

The plan shows that the bitmap index on the City column is used to find a bitmap for all the rows in the COMPANY table that satisfy the WHERE clause condition (where City=’Roanoke’). Another bitmap is created from the index on the State column. The two bitmaps are then united to one bitmap using a BITMAP AND operation. The resulting bitmap is converted to a list of ROWIDs via the BITMAP CONVERSION operation. The resulting list of ROWIDs are used as a basis for an access of the COMPANY table.

 

Related Topics

 

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen