> 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 [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
