Oracle STAR Transformation tips

Oracle STAR Transformations and SQL

To enable a data warehouse, the following suggested initialization parameter settings might be used:

§         cursor_space_for_time=TRUE

§         db_cache_size=XXX

§         db_block_size=32

§         db_file_multiblock_read_count=64

§         filesystemio_options=ASYNC

§         pga_aggregate_target=XXX

§         optimizer_index_cost_adj=XXX

§         optimizer_index_caching=XXX

§         query_rewrite_enabled=TRUE

§         shared_pool_size=150M

§        star_transformation_enabled=TRUE

§         workarea_size_policy=AUTO

§         session_cached_cursors=100

§         log_buffer=XXX

§         bitmap_merge_area_size=XXX

§         create_bitmap_area_size=XXX

For star_transformation join plans, the following parameters must also be considered:

§        star_transformation_enabled= TRUE

§         No hint STAR: So forcing a star_query excludes star_transformation

§         No BIND VARIABLE in SELECT statement

§         No CONNECT BY and start with

§         For fact table columns involved in EQUIJOIN predicate, there must be bitmap index defined on them.

§         More than 2 bitmap index on fact table

§         Fact table must have more than 15,000 rows

§         Fact table cannot be a view

§         Fact table can not be a remote table

§         No hint FULL on fact table

Failure to set proper parameters can result in a botched attempt to initiate a star_transformation join as shown by the following example.

Bad Star Transformation Plan

The following is an example of a failed attempt at a star_transformation join:

Execution Plan

----------------------------------------------------------

    0      SELECT STATEMENT Optimizer=CHOOSE

    1    0   SORT (GROUP BY)

    2    1     HASH JOIN

    3    2       TABLE ACCESS (FULL) OF 'MINUTE_DIMENSION'

    4    2       HASH JOIN

    5    4         MERGE JOIN (CARTESIAN)

    6    5           TABLE ACCESS (FULL) OF 'DATE_DIMENSION'

    7    5           BUFFER (SORT)

    8    7             TABLE ACCESS (FULL) OF 'HOUR_DIMENSION'

    9    4         TABLE ACCESS (FULL) OF 'NETWORK_FACT'

The following is what a successful star_transformation join looks like:

Execution Plan

----------------------------------------------------------

    0      SELECT STATEMENT Optimizer=CHOOSE

    1    0   NESTED LOOPS

    2    1     HASH JOIN

    3    2       HASH JOIN

    4    2       TABLE ACCESS (FULL) OF 'MINUTE_DIMENSION'

    5    2     PARTITION CONCATENATED

    6    2        TABLE ACCESS BY ROWID 

    7    2          BITMAP CONVERSION TO ROWIDS

    8    2        BITMAP AND

    9    2        BITMAP MERGE

   10    2        BITMAP KEY ITERATION

   11    2        SORT BUFFER

   12    2       TABLE ACCESS (FULL) OF 'MINUTE_DIMENSION'

               BITMAP INDEX RANGE SCAN I_C1

               BITMAP MERGE

               BITMAP KEY ITERATION

               SORT BUFFER

             TABLE ACCESS ... D2

              BITMAP INDEX RANGE SCAN I_C2

BITMAP MERGE

BITMAP KEY ITERATION

SORT BUFFER

TABLE ACCESS ... D3

BITMAP INDEX RANGE SCAN I_C3

TABLE ACCESS ... D2

TABLE ACCESS BY ... D3

The execution plan looks like:

SELECT STATEMENT C=301

NESTED LOOPS

HASH JOIN

HASH JOIN

TABLE ACCESS ... D1

PARTITION CONCATENATED

TABLE ACCESS BY ROWID F

BITMAP CONVERSION TO ROWIDS

BITMAP AND

BITMAP MERGE

BITMAP KEY ITERATION

SORT BUFFER

TABLE ACCESS ... D1

BITMAP INDEX RANGE SCAN I_C1

BITMAP MERGE

BITMAP KEY ITERATION

SORT BUFFER

TABLE ACCESS ... D2

BITMAP INDEX RANGE SCAN I_C2

BITMAP MERGE

BITMAP KEY ITERATION

SORT BUFFER

TABLE ACCESS ... D3

BITMAP INDEX RANGE SCAN I_C3

TABLE ACCESS ... D2

TABLE ACCESS BY ... D3

This means that with:

§         (select C1_1 from D1 where D1.C1_2 op constant1), a bitmap B1 using I_C1 is generated.

§         (select C2_1 from D2 where D1.C2_2 op constant2), a bitmap B2 using I_C2 is generated.

§         (select C3_1 from D3 where D1.C3_2 op constant3), a bitmap B3 using I_C3 is generated.

Next, the DBA performs an AND between those bitmaps.  At the end, those rows from F with rowid coming from bitmap merging are taken.

SEE CODE DEPOT FOR FULL SCRIPTS