The ALTER INDEX statement alters an existing index.
Execution Plan image:
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.
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.
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE
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.
Execution plan image: , , or
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.
Execution Plan image:
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.
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.
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'
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.
© ALL RIGHTS RESERVED. Termini di utilizzo Privacy Cookie Preference Center