Re: Query Tuning.
- From: "Jaromir D.B. Nemec" <jaromir@xxxxxxxxxxxx>
- To: <greg@xxxxxxxxxxxxxxxxxx>, <dbaprimatics@xxxxxxxxx>, <tim@xxxxxxxxx>
- Date: Sun, 23 May 2010 12:59:45 +0200
Hi Vamshi,
The above query runs fine with out the timedim table join.
In my opinion you diagnosed the cause of the problem with this statement -
the usage of the surrogate key (TIMEID) in the time dimension.
If I see it right, the TIMEID returned from your query in not presented as a
final result, but it is transformed to some readable information as date,
interval etc. in the next step.
If this is true one possible approach is to change your query to group on
POSTINGDATE and EFFECTIVEDATE (not on TIMEID). This makes the join to the
time table unnecessary, i.e. you have your "runs fine" performance. You may
end with little more result records; it depends on the number of TIMEID in
the TIMEDIMREL table.
The final mapping of the time dimension will be done based on POSTINGDATE
and EFFECTIVEDATE. The big difference is that this happens on the
aggregated data, i.e. is done only once for each combination of your two
date columns.
While keeping your design, you may try rewrite the query to reflect the two
steps described above.
Group by on POSTINGDATE and EFFECTIVEDATE in a subquery and join the result
to the time table.
HTH
Jaromir D.B. Nemec
--
//www.freelists.org/webpage/oracle-l
Other related posts:
