Re: Star Transformation - 10g

From: A Ebadi <ebadi01_at_yahoo.com>
Date: Wed, 5 Dec 2007 11:23:23 -0800 (PST)
Message-ID: <637317.48902.qm@web51104.mail.re2.yahoo.com>

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_at_gmail.com> 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.
--

http://www.freelists.org/webpage/oracle-l Received on Wed Dec 05 2007 - 13:23:23 CST