Re: Query Tuning.

From: Greg Rahn <greg_at_structureddata.org>
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-l
Received on Sat May 22 2010 - 23:42:07 CDT

Original text of this message