STAR Query Hints And STAR Joins With Oracle 7.3

As we have already discovered, the STAR schema design involves creating a main fact table that contains all of the primary keys in the related tables. This massive denormalization of the database structure means that just about any query against the STAR schema is going to involve the joining of many large tables—including a large “fact” table and many smaller reference tables. Oracle has provided a new feature with release 7.3 that detects STAR query joins and invokes a special procedure to improve performance of the query. Prior to release 7.3 of Oracle, this feature only worked with up to five tables, but this restriction has been eliminated. Also, release 7.3 no longer requires the use of STAR query hints. However, they are still allowed in the SQL syntax and are generally a good idea for documentation purposes. The STAR query requires that a single concatenated index resides in the fact table for all keys.

Essentially, Oracle follows a simple procedure for addressing these queries. Oracle will first service the queries against the smaller reference tables, combining the result set into a Cartesian product table in memory. Once the sum of the reference tables has been addressed, Oracle will perform a nested-loop join of the intermediate table against the fact table. This approach is far faster than the traditional method of joining the smallest reference table against the fact table, and then joining each of the other reference tables against the intermediate table.

Using Oracle’s Bitmap Indexes

For data warehouse applications that must elicit as much performance as possible, some special situations can arise where Oracle bitmapped indexes may be useful. As we know, the sheer volume of rows in very large tables can make even a trivial query run for a long time. Oracle has introduced bitmapped indexes with release 7.3 in an attempt to improve index lookup performance for queries, especially decision support type queries that may have many conditions in the WHERE clause.

The bitmap approach to indexing is very different from the traditional B-tree style of indexes. In a traditional index, the index keys are sorted and carried in several tree nodes; in a bitmapped index, an array is created. This array has all possible index values as one axis, while the other axis contains all rows in the base table. For example, consider a bitmapped index on the region field of the SALES table, where the regions are North, South, East, and West. If the SALES table contains 1 million rows, the bitmapped index would create an array of 4 × 1 billion to store the possible key values.

Within this array, the index data is binary. If a value is true, it is assigned a binary “1”—a false reading is set to binary “0” (see Figure 10.11).

Figure 10.11  An Oracle bitmapped index.

Here we can see how this query runs faster than a traditional query. The Oracle optimizer will notice that the items in the WHERE clause have bitmapped indexes, scan for non-zero values in the proper array column, and quickly return the Row ID of the columns. A fast merge of the result set will then quickly identify the rows that meet the query criteria.

While it may appear that the index is very large, Oracle has developed a compression method whereby all of the binary zeros are omitted from the bitmap. This makes it very compact.

While this may look reasonable at first glance, some drawbacks to bitmapped indexing have to be considered. The first and most obvious is that bitmapped indexes work best for columns that only have a small amount of possible values. For columns that have many values such as state_name or city_name, the index overhead would probably exceed any performance gains that might accrue from using bitmapped indexes.

However, for columns such as sex, color, and size that have a small number of finite values, bitmapped indexes will greatly improve query retrieval speeds. Bitmapped indexes are especially useful for decision support systems where many conditions are combined to a single WHERE clause.

Using Oracle 7.3 Hash-Joins

Oracle release 7.3 also provides another method for speeding up decision support and warehouse queries. This method is called the hash-join. A hash-join is a technique where Oracle bypasses the traditional sort-merge join technique and replaces it with an algorithm that performs a full-table scan, placing the rows into memory partitions. Partition pairs that do not fit into memory are placed in the TEMP tablespace. Oracle will then build a hash table on the smallest partition, using the larger partition to probe the newly created hash table. This technique alleviates the need for in-memory sorting and does not require that indexes exist on the target tables.

The following init.ora parameters must be set to use hash-joins:

•  optimizer_mode parameter must be set to COST

•  hash_join_enabled=TRUE

•  hash_multiblock_io_count=TRUE

•  hash_area_size=SIZE

To execute a hash-join, the hash-join hint must be used. Consider this example:

SELECT /* USE_HASH/ *
FROM CUSTOMER, ORDER
WHERE
CUSTOMER.cust_no = ORDER.cust_no
AND
credit_rating = 'GOOD';

Summary

Now that we have seen how data warehouse applications function in a client/server environment, we can move on for a closer look at networking issues and how Oracle servers can be monitored for performance.