STAR TRANSFORMATION(Star Query)

[bandarna]

I am trying to tune an Oracle data warehouse query, which is developed on a star schema. I am trying to implement star transformation (9i new future). I have set a STAR_TRANSFORMATION_ENABLED = TRUE for my database.

My fact table has bitmap indexes and my dimension tables are with normal (B-Tree) index.

My question is how I can confirm that my Star transformation is working. My report is now running in a one minute but my manager asking me to run it for one second.

Can any one help??

 

[blom0344]

Best strategy is to run an explain plan on the query and try to determine whether indexes are used properly.

Both tables may be indexed already ,but are you sure that the query itself is not referencing non-indexed fields?

A one second response time may be too optimistic in ANY scenario. Sounds silly altogether, putting that as a demand...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[avjoshi]

Star transformation is not a new feature in 9i. It has been there since 8.0.x if not before.

Anand

 

[bandarna]

After analyze statistics of tables and indexes and separate table space created for indexes, finally I reached it to 16 seconds. I may not make it more then that until I need a special training on tuning.

Anyway thanks to both of you. Sorry Anand this is the first time I am using Star Transformation, out of my all experience.

 

[blom0344]

Hmmm,

You sound as if you expect to force your query down to the required second response just by investing enough time and training.

You might find that 16 seconds is about as good as it is going to get unless you consolidate everything into one big fact-table (and even then........)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[sh4kil]

Hi,

I'm not familiar with Star Transformation in Oracle but in DW generally you could try and use aggregated tables to further reduce the response time. Unless, the data is already at the correct granularity.

 

[SarangPatil123]

Ya i agree u need to look at Explain plan. here are some more point to think about.

1. MV's
2. Partations.
3. Look at OPTIMIZER_FEATURE_ENABLE = 9.0.1 0r 9.2.0
4. Disk contention if any.