Star Transformation is not happening

[johnnybee]

I have the following query:

SELECT /*+ STAR_TRANSFORMATION */
f_exp_det.adjust_flag,
f_exp_det.deal_id,
f_exp_det.approved_approach_id,
f_exp_det.asset_class_id,
d_asset_class.exposure_type,
d_asset_class.exposure_type_desc,
d_asset_class.exposure_sub_type,
d_asset_class.exposure_sub_type_desc,
d_asset_class.b1_fsa_reg_exp_type,
d_asset_class.b2_std_fsa_reg_exp_type,
d_asset_class.aclm_product_type
FROM d_asset_class,
f_exp_det
WHERE f_exp_det.as_at_date = TO_DATE ('01/30/2009', 'MM/DD/YYYY')
AND f_exp_det.adjust_flag = 'U'
AND f_exp_det.asset_class_id = d_asset_class.asset_class_id

All tables have recently been analyzed, after data has been loaded.

Star Transformations have been enabled.

Bitmap indexes are present on the table F_EXP_DET.

F_EXP_DET is partitioned on date, and sub-partitioned on adjust_flag.

Explain plan is:

Operation Object Name Rows Bytes Cost

SELECT STATEMENT Optimizer Mode=ALL_ROWS 2 M 62591
HASH JOIN 2 M 461 M 62591
TABLE ACCESS FULL RRA_DIM_OWNER.D_ASSET_CLASS 106 K 13 M 501
PARTITION RANGE SINGLE 2 M 115 M 53985
PARTITION LIST SINGLE 2 M 115 M 53985
TABLE ACCESS FULL RRA_FACT_OWNER.F_EXP_DET 2 M 115 M 53985


Can anyone help explain why the bitmap indexes are not used in a Star Transformation?

Many thanks.

 

[johnnybee]

I've also tried joining to a larger dimension table to try and avoid the optimiser choosing to do a full table scan. This dimension has 15million+ rows.

SELECT /*+ STAR_TRANSFORMATION */
f_exp_det.adjust_flag,
f_exp_det.deal_id,
f_exp_det.approved_approach_id,
f_exp_det.asset_class_id,
d_external_entity.cis_code
FROM d_external_entity,
f_exp_det
WHERE f_exp_det.as_at_date between TO_DATE ('08/30/2008', 'MM/DD/YYYY') and TO_DATE ('01/30/2009', 'MM/DD/YYYY')
AND f_exp_det.adjust_flag = 'U'
AND f_exp_det.external_entity_id = d_external_entity.ext_entity_id

But this results in a similar explain plan.

Operation Object Name Rows Bytes Cost

SELECT STATEMENT Optimizer Mode=ALL_ROWS 9 M 731976
HASH JOIN 9 M 537 M 731976
TABLE ACCESS FULL RRA_DIM_OWNER.D_EXTERNAL_ENTITY 15 M 158 M 480002
PARTITION RANGE ITERATOR 9 M 439 M 207836
PARTITION LIST SINGLE 9 M 439 M 207836
TABLE ACCESS FULL RRA_FACT_OWNER.F_EXP_DET 9 M 439 M 207836

 

[Thargy]

can't be sure, but if partition pruning is occurring (and it appears to be from the explain plan) that might be more powerful than a bit map across the entire table.

The CBO may have decided to ignore the index because a full scan of the relevant partition is less work.

Regards

T

 

[johnnybee]

I would have thought a combination of hitting the partitions and using the bitmap indexes was possible. If not, I'm open to suggestions as to how to tune this query.

At the moment it takes 29 secs to return the 1st 100 rows. Not exactly light-speed!

 

[taupirho]

Just a thought, but does changing this line

WHERE f_exp_det.as_at_date between TO_DATE ('08/30/2008', 'MM/DD/YYYY') and TO_DATE ('01/30/2009', 'MM/DD/YYYY')

to

WHERE f_exp_det.as_at_date between '30-aug-08' and '30-jan-09'

make a difference?



In order to understand recursion, you must first understand recursion.

 

[Thargy]

johnny,

I believe (from reading Tom Kyte's book), that if you use autotrace it doesn't show the partition start and stop info. Did you use autotrace to get the plan or utlxpls?

Regards

T

 

[johnnybee]

taupirho - changing the where clause as suggested made no difference.

thargtheslayer - I didn't use autotrace, I used Explain Plan functionality within TOAD (not sure if that calls utlxpls).

I deleted bits of the plan to make it more readable, I'll paste it in full below now:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 9 M 731976
HASH JOIN 9 M 537 M 731976
TABLE ACCESS FULL RRA_DIM_OWNER.D_EXTERNAL_ENTITY 15 M 158 M 480002
PARTITION RANGE ITERATOR 9 M 439 M 207836 32 37
PARTITION LIST SINGLE 9 M 439 M 207836 KEY KEY
TABLE ACCESS FULL RRA_FACT_OWNER.F_EXP_DET 9 M 439 M 207836 KEY KEY

 

[johnnybee]

Adding a Parallel hint instead of a Star Transformation gets the time down to 6 Seconds:

/*+ PARALLEL (f_exp_det, 6) */

Explain Plan:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 9 M 473792
PX COORDINATOR
PX SEND QC (RANDOM) SYS.:TQ10000 9 M 537 M 473792 :Q1000 P->S QC (RANDOM)
NESTED LOOPS 9 M 537 M 473792 :Q1000 PCWP
PX BLOCK ITERATOR 9 M 439 M 38330 :Q1000 PCWC KEY KEY
TABLE ACCESS FULL RRA_FACT_OWNER.F_EXP_DET 9 M 439 M 38330 :Q1000 PCWP KEY KEY
TABLE ACCESS BY INDEX ROWID RRA_DIM_OWNER.D_EXTERNAL_ENTITY 1 11 1 :Q1000 PCWP
INDEX UNIQUE SCAN RRA_DIM_OWNER.EXT_ENTITY_UK_ENTITY_ID_I 1 1 :Q1000 PCWP


This might be good enough, but I am interested to hear of any other options for tuning this. Perhaps a star transformation is not the best option here? Interested in hearing your opinions. Thanks.