Star hint does wonderful things

Following my last post about using star optimization, the old-fashioned B-Tree based star query since Oracle 7, I have found that star hint helps the optimizer tremendously. Without the hint, Oracle will not consistently join the dimensions first and/or in correct join order and partition prune the fact table. With hint, the plan is much stable, Oracle chooses the correct join order and then does partition pruning. The issue is the complexity of our dimensions. The time dimension is typical snowflake dimension, but the other dimension is really mini star-schema within itself. And each query generated by Cognos is not only huge and unreadable, but involves more than ten tables at the least.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: