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:
- Bitmapped indexes provide very fast access to table rows when used with low cardinality columns.
- A STAR query requires a single concatenated index on the FACT table.
- STAR queries work similar to hash joins in Oracle, building a hash table in the shared pool.
- The STAR transformation method creates ROWID lists in temporary segments.
- The STAR transformation is faster than the STAR query in cases where there are few rows returned by the sub-queries.
- A concatenated index is used whenever the high-order keys are present in the queries.
Here are the terms from this module that may be new to you:
- n-way table joins
- STAR Transformation
Now that we know some advanced indexing techniques, we are ready to explore the Oracle database for Web applications.
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:
- What is the effect of promoting one product on the sale of a related product that is not promoted?
- What are the product sales before and after the promotion?
The data in the video chain's data warehouse system has two important components:
- dimensions and
- 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.