Re: Query Tuning.
Date: Sat, 22 May 2010 21:42:07 -0700
Message-ID: <AANLkTimpNvSv5RQJMBUPiUbUQs6X6xREMgPeYJjKCwCy_at_mail.gmail.com>
Can you clarify "runs fine with out the timedim table join" with a number? Three minutes doesn't seem long to me for a DW, but I have no clue what your (or your user's) expectation is.
At first glance, I would say it's likely no partitioning will
significantly help this query. You have (what appears to be) a single
fact-like table (POSITIONTRANSACTION_PARTS) joined to a single
dimension (TIMEDIMREL) on two columns (POSTINGDATE & EFFECTIVEDATE).
I say "fact-like" because it appears the fact table has type 2 SCD
attributes (effective date) which seems odd to me and I can't say I
have seen a time dimension that contains type 2 SCD attributes either
-- time/date doesn't seem like something that changes over time, no?
TIMEDIMREL is simply being used as a join filter with no predicate
filters on itself (it's the entire table of 2244 rows joined to
POSITIONTRANSACTION_PARTS) along with the IN list of 246 values (if I
counted correct) on JOURNALENTRYTYPEID. The only way I see
partitioning aiding here is if TIMEDIMREL has only a fraction of the
date range in it that POSITIONTRANSACTION_PARTS contains (which seems
unlikely, but would not rule it out completely).
For this query, I'm going to disagree with Tim on the recommendation of hash sub-partitioning on JOURNALENTRYTYPEID -- there are simply too many values listed in the predicate (246) and in all likelihood every single hash subpartition would need to be probed so there would be no added benefit then. There isn't any group by on JOURNALENTRYTYPEID so there would be no "push up" on the subpartition dimension to aid in lowering PGA usage or the GBY spilling to temp.
The only design modification I could possibly see given the little information here (and I am *very, very* hesitant to offer this as I strongly discourage this approach) is to make a 4 column index consisting of (POSTINGDATE, EFFECTIVEDATE, JOURNALENTRYTYPEID, AMOUNT). The first two columns are required for predicates and the latter are the projections needed for the select list. This may or may not yield gains -- it depends on how many columns POSITIONTRANSACTION_PARTS is and how selective the join is.
BTW - its very useful to post execution plans.
explain plan for
<your query>
;
select * from table(dbms_xplan.display);
--
Regards,
Greg Rahn
http://structureddata.org
-- http://www.freelists.org/webpage/oracle-lReceived on Sat May 22 2010 - 23:42:07 CDT