Re: Star Transformation - 10g

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.

--
http://www.freelists.org/webpage/oracle-l