Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Analytics Performance was RE: Tricky query question

Analytics Performance was RE: Tricky query question

From: Larry Elkins <elkinsl_at_flash.net>
Date: Sat, 25 Jan 2003 07:48:48 -0800
Message-ID: <F001.0053A723.20030125074848@fatcity.com>


> The solution Larry suggested is quite
> a bit easier to take. :)
> .......
> It also performs better.
>
> Jared

Issue of performance is something I didn't go into detail in the original response since it was already so long. I said they, analytics, *can* make a big difference in performance, not *will*, and also noted the cost of the sort while eliminating the extra accesses on the table.

There's a cost associated with analytics, the sort and the internal going's on, etc. And there's a cost associated with the extra accesses from the self-joins, sub-query, whatever you are looking to replace. And I don't know that the cost associated with the analytics is always going to be lower. Though I've never built test cases showing the counter examples, I'm sure it would be pretty easy to build some where the analytics is going to be slower. FWIW, another person on the list *is* working on such counter examples ;-)

Sure, we've got lots of examples from real work where we've taken a SQL statement and by using analytics provided a massive increase in performance. And it's more of a no-brainer, usually, in cases where procedural logic using row at a time processing and comparisons can be replaced with a pure SQL approach using analytics, with the big gain due to getting away from the row at a time cursor based processing. We've dropped hours long running procedural code to minutes or less. And without the analytic functions, or some huge really nasty SQL statement with a gazillion self-joins and sub-queries (which would be slow), we couldn't have done that, couldn't have avoided the procedural processing.

But I haven't had the time to build test cases and guidelines on when one will be better than the other. Like I said earlier, we have seen dramatic performance improvements, but I'm sure opposite examples can be built as well.

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sat Jan 25 2003 - 09:48:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US