Re: CUBE temp table transformation gives bad access plan?
- From: Martin Preiss <mtnpreiss@xxxxxx>
- To: kibeha@xxxxxxxxx
- Date: Thu, 30 Oct 2014 15:23:12 +0100
Kim,
I don't want to pretend that the trace files tell me much more than you.
(As you write) It seems that the cardinalities for the single table
access are in the right ballpark - but in the plan finally created for
the CUBE query obviously they are not. One difference I see between both
queries is that the "final query after transformations" contains a hint
/*+ NO_STATS_GSETS */ for the CUBE version but not for the ROLLUP
version - while the rest of the queries is similar (except for the
grouping function of course). But I don't know the semantics of this
hint and could not find a good reference. At least this information
tells us that the different plans are not a result of the (cost based
and heuristic) transformations in the logical optimizations done by the
CBO (as I understand it).
Could you add the index definition for I_102VAREGRPDEBGRPIDX (I guess
someone could infer it from the trace - but that's not me...)? In the
(bad) plan the critical error is the cardinality 1 (which could also
mean zero and correspond to the out of range remarks you mentioned) in
step 10, with the following predicates:
10 - access("SYS_TBL_$4$"."DATASET"='DAT' AND
"SYS_TBL_$4$"."FAKTURADATO">=TO_DATE(' 2013-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
"SYS_TBL_$4$"."FAKTURADATO"<TO_DATE(' 2013-02-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(("SYS_TBL_$4$"."FAKTURADATO">=TO_DATE(' 2013-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_DESCEND("FAKTURADATO")>HEXTORAW('878EFDFEF8FEF8FF') AND
"SYS_TBL_$4$"."FAKTURADATO"<TO_DATE(' 2013-02-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND
SYS_OP_DESCEND("FAKTURADATO")<=HEXTORAW('878EFEF8FEF8FEFAFF') ))
Maybe by checking the selectivities of the columns we could recalculate
the ix_sel: 0.004501 and ix_sel_with_filters: 0.004501 in the trace
files and get an idea which additional step reduces the expected number
of rows to zero.
Regards
Martin Preiss
--
//www.freelists.org/webpage/oracle-l
Other related posts:
