Database Reference
In-Depth Information
To optimize this query against the star schema, the query optimizer should do the following:
1.
Start evaluating each dimension table that has restrictions on it.
2.
Assemble a list with the resulting dimension keys.
3.
Use this list to extract the matching rows from the fact table.
Unfortunately, this approach can't be implemented with regular joins. On the one hand, the query optimizer can
join only two data sets at one time. On the other hand, joining two dimension tables leads to a Cartesian product.
To solve this problem, Oracle Database implements the star transformation .
although the star transformation was initially introduced with version 8.0 in 1997 and was strongly
enhanced in version 8.1 two years later—that is, a long time ago—its stability has always been a problem. probably
every patchset released since its introduction has fixed bugs related to it. Whenever something goes wrong, errors like
Ora-07445, Ora-00600, Ora-00942, or incorrect results are generated. That said, I have successfully used this feature
since version 8.1.6. The Query Optimizer group at Oracle, aware of this issue, completely rewrote the code for version
11.2. hence, in recent versions the stability is improved. my advice is simply to test it carefully. If it works, the
improvement in performance will be considerable. If not, at least you would know it before going into production. I also
advise you to check Oracle Support note 47358.1 (Init.ora parameter STar_TranSFOrmaTIOn_enaBLeD reference
note). It gives you a list of the bugs affecting each specific version.
Caution
You need to meet two basic requirements to take advantage of the star transformation. First, the feature must
be enabled. You use the star_transformation_enabled initialization parameter to control it. Note that, per default,
the feature is disabled because the parameter is set to FALSE . To enable it, you should set it to either temp_disable
or TRUE . Second, on the fact table, there must be an index for each join condition referencing a dimension table. The
join conditions don't have to be based on foreign keys, but if foreign keys do exist, they assist the query optimizer in
finding an optimal execution plan. Even though the query optimizer can convert B-tree indexes into bitmap indexes
on the fly, execution plans using bitmap indexes are more efficient. In summary, for best performance I advise
creating foreign keys and bitmap indexes on every join condition.
a join condition between a fact table and a dimension table based on several columns isn't always supported by
the star transformation. Therefore, I strongly recommend that you use use join conditions and, consequently, foreign keys
and bitmap indexes based on a single column.
Tip
 
 
Search WWH ::




Custom Search