 |
|
Pre-Oracle8i Star Join
Execution
Oracle Tips by
Burleson
|
As we noted,
prior to Oracle8i you must have a concatenated index on all columns of
the fact table. During the STAR join, Oracle will first service the
queries against the smaller dimension tables, combining the result set
into a Cartesian product table that is held in Oracle memory. This
virtual table will contain all of the columns from all of the
participating dimension tables. The primary key for this virtual table
will be a composite of all of the keys for the dimension tables. If
this key matches the composite index on the fact table, then the query
will be able to process very quickly. 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.
Oracle8i parallel bitmap star join execution
In Oracle8i,
bitmap indexes are required for all join columns on the fact table,
and Oracle8i will initially use these bitmap indexes as a path to the
fact table. The SQL optimizer will then re-write the original
query, replacing the equi-join criteria with sub-queries using the IN
clause. These sub-queries are used as sources of keys to drive
the bitmap index accesses, using bitmap key iteration to access the
dimension tables. Once the resulting bitmap-ROWID lists are retrieved,
Oracle will use a hash join to merge the result sets.
To see how the
Oracle SQL optimizer transforms a star query, consider the following
query where we sum the sales by region for all southern regions during
the months of March and April:
select
store.region,
time.month,
sum(sales.sales_amount)
from
sales,
store,
time,
product
where
sales.store_key = store.store_key
and
sales.month = time.month
and
store.region = `south’
and
time.month in (`01-03’, `01-04’)
group by
store.region, time.month
;
The star optimizer replaces the where clauses as
follows. Note that the equi-join criteria is replaced by a sub-query
using the IN clause.
where
store.region = `south’
and
sales.store_key = store.store_key
where
sales.store_key in (select store_key from store
where region = `south’)
We see a similar transformation in the join into
the time table:
where
sales.month = time.month
and
time.month in (`01-03’, `01-04’)
where
sales.month in (select month from time
where month in (`01-03’, `01-04’))
As we see, the query is significantly transformed,
replacing all where clause entries for the dimension table with a
single sub-select statement. These IN sub-queries are ideal for
the use of bitmap indexes because the bitmap can quickly scan the
low-cardinality columns in the bitmap and produce a ROWID list of rows
with matching values.
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. The speed is a
result of reducing the physical I/O. The indexes are read to gather
the virtual table in memory, and the fact table will not be accessed
until the virtual index has everything it requires to go directly to
the requested rows via the composite index on the fact table (Figure
16-11).
Starting with
Oracle8i, the requirement for a concatenated index has changed,
and the STAR hint requires bitmap indexes. The bitmap indexes can be
joined more efficiently than a concatenated index, and they provide a
faster result.
Figure 11: Oracle star
query processing
As I have noted, the star query can be very
tricky to implement, and careful consideration must be given to the
proper placement of indexes. Each dimension table must have an index
on the join key, and in Oracle7 and Oracle8, the large fact table must
have a composite index consisting of all of the join keys from all of
the dimension tables, while in Oracle8i you need bitmap indexes
on the fact table. In addition, the sequencing of the keys in the fact
table composite index must be in the correct order, or Oracle will not
be able to use the index to service the query.
Next, let’s move on and look at some other
important issues surrounding Oracle table joins. As I have noted in
earlier chapters, Oracle must go to a great deal of work during the
parse phase of a query to determine the optimal table join order when
many tables are being joined.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
