ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle STAR Transformations and SQL

Oracle STAR Transformations and SQL

原创 Linux操作系统 作者:jiuniang012 时间:2009-10-09 19:07:25 0 删除 编辑
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 Start Transformation Plan

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

 

ALTER SESSION SET _always_star_transformation= TRUE;

 

select /*+ star_transformation X "star_transformation"  */ wdate, hour, minute, sum(bytes) from

network_fact nf,

date_dimension ddi,

hour_dimension hdi,

minute_dimension mdi

where

nf.date_key=ddi.date_key

and nf.hour_key=hdi.hour_key

and nf.minute_key=mdi.minute_key

and wdate>=to_date('2004/10/14 21', 'yyyy/mm/dd hh24')

and wdate<=to_date('2004/10/15 21', 'yyyy/mm/dd hh24')

group by wdate, hour, minute;

 

Execution Plan

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

    0      SELECT STATEMENT ptimizer=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'

Good Star Transformation Plan

The following is what a successful star_transformation join looks like:

 

Execution Plan

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

    0      SELECT STATEMENT ptimizer=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.

 引自:http://www.dba-oracle.com/oracle10g_tuning/t_star_transformations_sql.htm

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9466564/viewspace-616181/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-07-02

  • 博文量
    126
  • 访问量
    211731