Star transformation prerequisites

  • From: tim.x.brown@xxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 16 Feb 2006 17:04:27 -0500

Oracle version Enterprise Edition 
Solaris 2.8 

Can someone please definitively clarify if it is mandatory for foreign 
keys (not necessarily enabled) to be declared, in order that that CBO will 
apply star transformation in a traditional star schema? (fact table and 
dimension tables are indexed according to Bert Scalzo's Guide to Oracle 8i 
Data Warehousing). In the Bert Scalzo book I did not see a categorical 
statement that the fks MUST be declared.

In the Oracle 9.2 Data warehousing guide, I also cannot find where it is 
clearly stated that the fks MUST be declared, only that the fks (join 
columns) must have bitmap indexes (subtle difference).

I have also read Jonathan Lewis's various articles on bitmap indexes and 
can find nothing clear there either. 

star_transformation_enabled = temp_disable 

# As I recall (from a year ago), hit a bug in where use of temp 
tables had serious negative impact on some queries. Never turned it back 
to value = "true"

My questions are:
1. Are foreign key declarations mandatory for the CBO to use star 
transformation, if fks are not declared, but the bitmap indexes exist and 
all objects are appropriately analyzed?

2. Do fks influence the CBO? If so, how? Can someone point me at or 
recommend documentation?

I posted the same question on the Metalink forum, and the only response so 
far has been from someone who has NOT declared fks but has observed star 
transformation, (but no explain plans or schema descriptions were given).

If this is a case of me misreading documentation, I apologize.


Tim Brown
PCS Technology Group

(614) 213-9259
(614) 226-8605

Other related posts: