Star transformation vs Star optimization

eHealth 6.0 beta release features ReportCenter, an embedded Cognos solution to enable clients for report customization as well as drag-and-drop querying. Obviously, we use a star schema, a snowflaked one though.

I started working on the performance issues a month ago. The first thing comes to mind is that we have a star schema so we should use star transformation. I created bitmap indexes on fact table, turn on star_transformation_enabled init parameter and stick in STAR_TRANSFORMATION hint. Oracle did the transformation but the execution time is much worse. I was puzzled for a while and looked up the oracle doc. And there's the answer:


Whereas the star optimization works well for schemas with a small number of dimensions and dense fact tables, the star transformation may be considered as an alternative if any of the following holds true:

  • The number of dimensions is large
  • The fact table is sparse
  • There are queries where not all dimension tables have constraining predicates

The star transformation does not rely on computing a Cartesian product of the dimension tables, which makes it better suited for cases where fact table sparsity and/or a large number of dimensions would lead to a large Cartesian product with few rows having actual matches in the fact table. In addition, rather than relying on concatenated indexes, the star transformation is based on combining bitmap indexes on individual fact table columns.


In our case, we have only two dimensions and the fact table is very dense such that we almost have every combination of the two dimensions in the fact table. We also use the two dimensions in the predicate for all of our standard out-of-box reports.

I also googled a powerpoint, which details when it is appropriate to use star transformation and when to use star optimization.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: