STAR TRANSFORMATION ENABLED

目次

基本情報

 

パラメータ情報

 

Syntax   : STAR_TRANSFORMATION_ENABLED = TRUE|TEMP_DISABLE|FALSE
設定方法  : ・Parameter File
           ・ALTER SYSTEM SET STAR_TRANSFORMATION_ENABLED =
      ・ALTER SESSION SET STAR_TRANSFORMATION_ENABLED =

 

バージョンとデフォルト
9iR2  : FALSE
10gR1 : FALSE	
10gR2 : FALSE	
11g   : FALSE	

 

説明

 

Star Transformationを使用するかどうかを指定します。

 

・TRUE:Star Transformationを使用します。
・TEMP_DISABLE:Star Transformationを使用しますが、Temp Table Transformation作業(Operation)は、
 使用していません。
  Temp Table TransformationはDimension Tableを二度アクセスする負担を軽減するために、アクセス結果を
 Temp Tableに保存することを意味します。
  一般的に、性能に有利ですので、性能的に問題のある動作をしている場合は、この値を用いて制御します。
・FALSE:Star Transformationを使用しません。

 

注意

 

Star Transformation

Star Transformationを使用するには、次のような条件を満たさなければなりません。

 

・FactテーブルとDimensionテーブルで構成されStar Schemaを使用します。
・FactテーブルとDimensionテーブルはParent-Child Relationshipを明示的に持たなければなりません。
 つまり、Primary key、Foreign Key Constraintを与えなければならないのです。
・FactテーブルのDimension Keyはすべて単一の列からなるBitmap Indexを持たなければなりません。

 

 

Star Transformationを使用するには、次のようにParent/ Child Relationshipを持つStar Schemaを設定する必要があります。

 

-- create objects
-- this is star schema
drop table t_fact purge;
drop table t_dim1 purge;
drop table t_dim2 purge;

create table t_fact(dim1_key int, dim2_key int, dim3_key int, value1 int, value2 int);
alter table t_fact add constraint pk_fact primary key(dim1_key, dim2_key, dim3_key);
create table t_dim1(dim1_key int primary key, value varchar2(20));
create table t_dim2(dim2_key int primary key, value varchar2(20));
alter table t_fact add constraint fk_dim1 
	foreign key (dim1_key)
	references t_dim1(dim1_key);
alter table t_fact add constraint fk_dim2
	foreign key (dim2_key)
	references t_dim2(dim2_key);


-- generate data
-- generate dimension data
insert into t_dim1
select
	level as dim1_key,
	'area'||level as value
from
	dual
connect by level <= 100;

insert into t_dim2
select
	level as dim2_key,
	'code'||level as value
from
	dual
connect by level <= 100;


-- generate fact data
insert /*+ append */ into t_fact
select
	t_dim1.dim1_key,
	t_dim2.dim2_key,
	dummy.dim3_key,
	dbms_random.random as value1,
	dbms_random.random as value2	
from
	t_dim1, t_dim2, 
	(select level as dim3_key from dual connect by level <= 10) dummy
;

commit;

 

また、Factテーブルの各Dimension KeyはBitmap Indexを持たなければなりません。

 

-- create bitmap index
create bitmap index t_fact_bidx1 on t_fact(dim1_key);
create bitmap index t_fact_bidx2 on t_fact(dim2_key);


-- gather statistics
exec dbms_stats.gather_table_stats(user, 't_dim1', cascade=>true, no_invalidate=>false);
exec dbms_stats.gather_table_stats(user, 't_dim2', cascade=>true, no_invalidate=>false);
exec dbms_stats.gather_table_stats(user, 't_fact', cascade=>true, no_invalidate=>false);

 

Star Transformationを使用していない場合は、次のような実行計画に続きます。

 

-- case 1: no star transformation  
-- disable start transformation
alter session set star_transformation_enabled = false;

select /*+ gather_plan_statistics */
	d1.value as area,
	d2.value as code,
	sum(f.value1) as sum_value1,
	max(f.value2) as max_value2,
	count(*) as counts
from
	t_fact f,
	t_dim1 d1,
	t_dim2 d2
where
	f.dim1_key = d1.dim1_key and
	f.dim2_key = d2.dim2_key and
	d1.value like 'area1%' and
	d2.value = 'code1'
group by
	d1.value, d2.value
;
 
 select * from table(dbms_xplan.display_cursor(null, null, 'iostats last'));      
-------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name       |Starts| E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |HASH GROUP BY               |            |    1 |      9 |     12 |00:00:00.05 |     415 |
|*  2 |HASH JOIN                   |            |    1 |    119 |    120 |00:00:00.05 |     415 |
|*  3 |TABLE ACCESS FULL           |T_DIM1      |    1 |     12 |     12 |00:00:00.01 |       7 |
|   4 |TABLE ACCESS BY INDEX ROWID |T_FACT      |    1 |    986 |   1000 |00:00:00.03 |     408 |
|   5 |NESTED LOOPS                |            |    1 |    986 |   1002 |00:00:00.02 |       9 |
|*  6 |TABLE ACCESS FULL           |T_DIM2      |    1 |      1 |      1 |00:00:00.01 |       7 |
|   7 |BITMAP CONVERSION TO ROWIDS |            |    1 |        |   1000 |00:00:00.01 |       2 |
|*  8 |BITMAP INDEX SINGLE VALUE   |T_FACT_BIDX2|    1 |        |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------
                                                                                                           
Predicate Information (identified by operation id):                                                       
---------------------------------------------------                                                       
                                                                                                          
   2 - access("F"."DIM1_KEY"="D1"."DIM1_KEY")                                                             
   3 - filter("D1"."VALUE" LIKE 'area1%')                                                                 
   6 - filter("D2"."VALUE"='code1')                                                                       
   8 - access("F"."DIM2_KEY"="D2"."DIM2_KEY")                                                             
                                                                                                       

 

 

Star Transformationを使用する場合には、次のような実行計画に続きます。Bitmap演算を利用してDimension値をまず抽出し、再びDimension値とFact値を抽出する変換手法を注意深く観察する必要があります。

 

-- case 2: star transformation
alter session set star_transformation_enabled = true;

select /*+ gather_plan_statistics */
	d1.value as area,
	d2.value as code,
	sum(f.value1) as sum_value1,
	max(f.value2) as max_value2,
	count(*) as counts
from
	t_fact f,
	t_dim1 d1,
	t_dim2 d2
where
	f.dim1_key = d1.dim1_key and
	f.dim2_key = d2.dim2_key and
	d1.value like 'area1%' and
	d2.value = 'code1'
group by
	d1.value, d2.value
;

 select * from table(dbms_xplan.display_cursor(null, null, 'iostats last'));      ----------------
| Id  | Operation                 | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |HASH GROUP BY              |            |      1 |      1 |     12 |00:00:00.01 |     106 |
|*  2 |HASH JOIN                  |            |      1 |      1 |    120 |00:00:00.01 |     106 |
|*  3 |HASH JOIN                  |            |      1 |      1 |    120 |00:00:00.01 |      99 |
|*  4 |TABLE ACCESS FULL          | T_DIM2     |      1 |      1 |      1 |00:00:00.01 |       7 |
|   5 |TABLE ACCESS BY INDEX ROWID| T_FACT     |      1 |    119 |    120 |00:00:00.01 |      92 |
|   6 |BITMAP CONVERSION TO ROWIDS|            |      1 |        |    120 |00:00:00.01 |      32 |
|   7 |BITMAP AND                 |            |      1 |        |      1 |00:00:00.01 |      32 |
|   8 |BITMAP MERGE               |            |      1 |        |      1 |00:00:00.01 |       9 |
|   9 |BITMAP KEY ITERATION       |            |      1 |        |      1 |00:00:00.01 |       9 |
|* 10 |TABLE ACCESS FULL          |T_DIM2      |      1 |      1 |      1 |00:00:00.01 |       7 |
|* 11 |BITMAP INDEX RANGE SCAN    |T_FACT_BIDX2|      1 |        |      1 |00:00:00.01 |       2 |
|  12 |BITMAP MERGE               |            |      1 |        |      1 |00:00:00.01 |      23 |
|  13 |BITMAP KEY ITERATION       |            |      1 |        |     12 |00:00:00.01 |      23 |
|* 14 |TABLE ACCESS FULL          |T_DIM1      |      1 |     12 |     12 |00:00:00.01 |       7 |
|* 15 |BITMAP INDEX RANGE SCAN    |T_FACT_BIDX1|     12 |        |     12 |00:00:00.01 |      16 |
|* 16 |TABLE ACCESS FULL          |T_DIM1      |      1 |     12 |     12 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------
                                                                                                          
Predicate Information (identified by operation id):                                                       
---------------------------------------------------                                                       
                                                                                                          
   2 - access("F"."DIM1_KEY"="D1"."DIM1_KEY")                                                             
   3 - access("F"."DIM2_KEY"="D2"."DIM2_KEY")                                                             
   4 - filter("D2"."VALUE"='code1')                                                                       
  10 - filter("D2"."VALUE"='code1')                                                                       
  11 - access("F"."DIM2_KEY"="D2"."DIM2_KEY")                                                             
  14 - filter("D1"."VALUE" LIKE 'area1%')                                                                 
  15 - access("F"."DIM1_KEY"="D1"."DIM1_KEY")                                                             
  16 - filter("D1"."VALUE" LIKE 'area1%')                                                                                                                                     

 

関連情報

 

1.Bitmap Join Index