Re: Star Transformation - 10g

The Oracle 10G DW Guide states the following as the minimum requirement to 
enable star transformation.
  1) The initialization parameter STAR_TRANSFORMATION_ENABLED should be set to 
TRUE
  2) A bitmap index should be built on each of the foreign key columns of the 
fact table or tables.
  3) Set up appropriate pk-fk relationships between fact/dimension tables
   
  The link to this doc:  
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/schemas.htm#CIHEIFCC
   
  We cannot get it working based on this doc.  Has anyone else got it to work 
on 10g and can pass on details?
  
Thanks!
  

Thomas Day <tomdaytwo@xxxxxxxxx> wrote:
    This is what were're doing - 
  
star_transformation_enabled needs to be set to TRUE in the initialization 
parameters.  
   
  hash_join_enabled also needs to be TRUE.  It's default is FALSE. 
   
  _always_star_transformation  cannot be set in the initialization prarmeters.  
 It needs to be TRUE.  It's default is FLASE.  The command ' ALTER SESSION SET 
"_always_star_transformation" = TRUE;' should be placed in a global logon 
trigger.
   
  sort_area_size should equal sort_area_retained_size and both should be set to 
the maximum amount of memory in bytes that a session can allocate. 
   
  bitmap_merge_area_size should also be set to the maximum amount of memory in 
bytes that a session can allocate.   Typical for data warehouses 100MB to 250MB 
are reasonable if enough memory is available.
   
  create_bitmap_area_size should be ditto above.  If memory is limited it can 
be set to 50% of bitmap_merge_area_size. 
   
  pga_aggregate_target should NOT be used.
   
  Primary keys and foreign keys are enforced with b-tree indexes.  They should 
be set to RELY.  Oracle does a b-tree to bitmap transform on the fly. 
   
  Primary keys of fact tables consist of all the primary keys of the 
participating dimensions.  Each of those should be foreign keyed to its 
dimension.  I like to make fact tables IOTs but that's just an acquired taste 
and has nothing to do with your question. 
   
  Any column that participates in a WHERE clause (except for already b-tree 
indexed columns) should have a bitmap index.  Ignore guidelines on cardinality. 
 


       
---------------------------------
Looking for last minute shopping deals?  Find them fast with Yahoo! Search.

Other related posts: