Index Techniques   «Prev 

Star Schema

This is an example of a star schema. Notice that the DIMENSION tables are not joined to each other.


A single set of data about an item described in a fact table, a dimension is usually a denormalized table. A dimension table holds a key value and a numerical measurement or set of related measurements about the fact table object. A measurement is usually a sum but could also be an average, a mean or a variance. A dimension can have many attributes, 50 or more is the norm, since they are denormalized structures.

This is an example of a star schema

Ad Unified Star Schema

More on Star Schema

The star schema is one of the simplest of data warehouse schemas. It is known as star because it appears like a star with points expanding from a center. Figure 6.4 represents the star schema in which the fact table is at the center and the dimension tables are the nodes of the star. Each dimension in a star schema represents a one-dimensional table only and the dimension table consists of a set of attributes. Dimension tables comprise of relatively small numbers of records in comparison to fact tables, but each record may consist of a large number of attributes to describe the fact data. Fact tables usually consist of numeric facts and foreign keys to dimensional data. Generally, fact tables are in (3NF) third normal form in the case of star schema while dimensional tables are in de-normalized form. Although the star schema is one of the simplest structures, it is still extensively used nowadays and recommended by Oracle.

Figure 6.4 Graphical representation of Star schema

About Bitmap Indexes on Partitioned Tables

You can create bitmap indexes on partitioned tables but they must be local to the partitioned table, they cannot be global indexes. A partitioned table can only have global B-tree indexes, partitioned or nonpartitioned.

Benefits of Indexes for Data Warehousing Applications

Bitmap indexes are primarily intended for data warehousing applications where users query the data rather than update it. They are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. Indexes are more beneficial for high cardinality columns.