Execution Plan image:
The BITMAP MINUS operation subtracts one bitmap from another.
Execution plan image:
The BITMAP OR operation is a bit-wise OR between two bitmap indexes; if either bitmap index returns the entry, the BITMAP OR of the two indexes will return an entry. The result of the BITMAP OR operation is a list of all the values that are in either index. The BITMAP OR operation is functionally equivalent to the CONCATENATION operation between B-Tree indexes.
Assume that both City and State columns each has a single-column bitmap index.
select Name, City, State
where City = 'Portland'
and State = 'MD';
This query selects rows from the COMPANY Table for all companies in the state "MD" or in the city named "Portland."
TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY' (Cost=7 Card=26 Bytes=1040)
BITMAP CONVERSION (TO ROWIDS)
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$CITY'
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$STATE'
The Execution 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='Portland'). Another bitmap is created from the index on the State column (for the condition State='MD'). The two bitmaps are then united to one bitmap using a BITMAP OR operation. The resulting bitmap is converted to a list of ROWIDs to access the COMPANY table.
Execution plan image:
The BUFFER SORT operation reads frequently accessed data during statement execution into private memory to reduce overhead.
Execution plan image: , , or
A join with no join condition results in a CARTESIAN product, or a cross product. A CARTESIAN product is the set of all possible combinations of rows drawn from each table. In other words, for a join of two tables, each row in one table is matched with every row in the other. A CARTESIAN product for more than two tables is the result of pairing each row of one table with every row of the Cartesian product of the remaining tables.
All other kinds of joins are subsets of CARTESIAN products effectively created by deriving the CARTESIAN product and then excluding rows that fail the join condition.
Note: When using the ORDERED hint, it is important that the tables in the FROM clause are listed in the correct order to prevent CARTESIAN joins.
Hint: Consider using Oracle’s STAR query optimization when joining a very large "fact" table to smaller, unrelated "dimension" tables. You will need a concatenated index on the fact table and may need to specify the STAR hint.