Lesson 9

The main points of this Oracle-Execution Plan Techniques include:

1. Bitmapped indexes are binary arrays rather than B-tree structures.
2. The STAR hint was developed to aid in large n-way table joins in data warehouses.
3. The STAR transformation method is used when bitmapped indexes are defined on a FACT table.
4. Concatenated indexes are very useful in cases where the multiple WHERE conditions are commonly found in SQL queries.
5. Function-based indexes are used to provide alternative representations to existing columns.

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.

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.

• What is a Dimension?
  A dimension is a structure that categorizes data in order to enable end users to answer business questions. Commonly used dimensions are Customer, Product, and Time. For example, each store of a video chain might gather and store data regarding sales and rentals of video tapes at the check-out counter. The video chain management can build a data warehouse to analyze the sales of its products across all stores over time and help answer questions such as:
  1. What is the effect of promoting one product on the sale of a related product that is not promoted?
  2. What are the product sales before and after the promotion?
  The data in the video chain's data warehouse system has two important components:
  1. dimensions and
  2. facts.
  
  The dimensions are products, locations (stores), promotions, and time. One approach for identifying your dimensions is to review your reference tables, such as a product table that contains everything about a product, or a store table containing all information about a store. The facts are sales (units sold or rented) and profits. A data warehouse contains facts about the sales of each product at each store on a daily basis.

Here are the terms from this module that may be new to you:

1. n-way table joins
2. STAR Transformation

Now that we know some advanced indexing techniques, we are ready to explore the Oracle database for Web applications.

To complete this module, click the Quiz link below below to check your knowledge of Oracle8 index tuning options.
Advanced Execution Plan Techniques - Quiz