In Data warehouse project with the star schema, it is recommended to run queries with STAR TRANSFORMATION Enabled for better performance. I had same exact scenario in my project where FACT table is joined with few DIM tables.
(DB version: 11g – 11.2.0.4)
Our FACT table –
Partitioned by DATE, then sub partitioned by GEO, table has 7+ billion records
DIM tables –
Some of these were hash partitioned.
Many article suggests that following are pre-requisites for the START transformation to work
- A bitmap index should be built on each of the foreign key columns of the fact table or tables.
- The initialization parameter STAR_TRANSFORMATION_ENABLED should be set to TRUE. This enables an important optimizer feature for star-queries. It is set to FALSE by default for backward-compatibility.
As per the foreign key – I don’t see any improvements if I have foreign keys on fact tables. I do have bitmap indexes on all columns that were referred to DIM tables in my query though
Initially I didn’t have foreign keys, – generated plan. Then I added foreign keys, analyzed tables, again generated plan – they are exactly same.
These queries runs more than 15 min and they times out on our OBIEE reports. So I couldn’t gain advantaged with foreign keys in place as many articles suggested to use.
However, I have noticed in the my plan that FACT table and one of the DIM tables were NESTED joined. I added a hint use_hash to join them using HASH and disabled START_TRANSFORMATION (you can use the hint : NO_STAR_TRANSFORMATION), then my query returned same results within few seconds.
Conclusion is that don’t just rely on the STAR TRANSFORMATION, instead look at the plan , disable and test to make sure you are returning data as fast as you can.
If you have encountered similar issues and if you gained or regressed with this option, please comment.
Oracle Enablement 