Re: Query Tuning.
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: dbaprimatics@xxxxxxxxx, tim@xxxxxxxxx
- Date: Sat, 22 May 2010 21:42:07 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
