Index Techniques   «Prev 

Oracle Execution Plan Techniques

In this module we covered some of the advanced Oracle execution plan techniques that rely on specialized indexing.
The main points of this module include:
  1. Bitmapped indexes provide very fast access to table rows when used with low cardinality columns.
  2. A STAR query requires a single concatenated index on the FACT table.
  3. STAR queries work similar to hash joins in Oracle, building a hash table in the shared pool.
  4. The STAR transformation method creates ROWID lists in temporary segments.
  5. The STAR transformation is faster than the STAR query in cases where there are few rows returned by the sub-queries.
  6. A concatenated index is used whenever the high-order keys are present in the queries.

About Bitmap Indexes and Nulls

Unlike most other types of indexes, bitmap indexes include rows that have NULL values. Indexing of nulls can be useful for some types of SQL statements, such as queries with the aggregate function COUNT.
Example 6-7 Bitmap Index
SELECT COUNT(*) FROM customers 
WHERE cust_marital_status IS NULL;

This query uses a bitmap index on cust_marital_status. Note that this query would not be able to use a B-tree index, because B-tree indexes do not store the NULL values.
SELECT COUNT(*) FROM customers;
Any bitmap index can be used for this query because all table rows are indexed, including those that have NULL data. If nulls were not indexed, the optimizer would be able to use indexes only on columns with NOT NULL constraints.