Re: How do you like this SQL?
Date: Mon, 17 Dec 2012 21:07:48 +0000 (UTC)
Message-ID: <kao1j3$8g7$1_at_solani.org>
On Mon, 17 Dec 2012 19:57:35 +0000, Jonathan Lewis wrote:
> I don't really need to say it for present company, of course, but
> they're not a silver bullet. Analytics sometimes rock - and Oracle has
> had a built-in transformation since 10g to convert an aggregate subquery
> (of exactly this type) into an analytic approach - but, as ever, it's
> very much dependent on the data that's there, the data you want, and the
> indexes you have in place. The subquery strategy often depends on the
> number of times the subquery has to run, or how Oracle unnests the
> subquery; the analytic strategy often depends on how large a volume of
> data goes into the analytic sort. (see:
> http://jonathanlewis.wordpress.com/2009/09/07/analytic-agony/ )
As a production DBA, I've seen this problem several times. This is a classic pattern. My preferred solution is to have a separate table maintained by triggers. If the table is large enough, analytic functions are usually a good solution because they require only a single pass through the table, as opposed to self-join.
There are other solutions as well. The table can be made into an IOT, in which case there would be a trivial ordering and max(timestamp) could be calculated in a very easy way. My point is that the most of the performance improvement can achieved by fixing the schema itself. Tricks like this one do not solve the general case, despite the fact that this has solved John's problem.
-- http://mgogala.byethost5.comReceived on Mon Dec 17 2012 - 22:07:48 CET