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 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'
Good Star Transformation Plan
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.
