One Analytic Function can do more than a thousand lines of code…

4

My colleague Alex Nuijten did a very fine presentation on Oracle Analytical Functions during the recent ODTUG 2006 conference in Washington DC. His paper for this conference was nominated for Best Paper award. The paper has appeared in the Conference Proceedings, was published in the June issue of the Dutch Optimize magazine for Oracle Developers and has now been included in the July issue of the Quest Software’s The Pipelines Newsletter, going out to 28.000 software developers worldwide.

You are probably anxious now to read the paper for yourself....
: "Oracle 8i Enterprise Edition introduced Analytic Functions, since 9iR2 this functionality was included in the Standard Edition. With these analytic functions you can retrieve data from more than one row at the same time without the need for a self join. You can create a ranking based on a value within a group of values. Even though it takes a bit getting used to the syntax, once mastered Analytic Functions can make your life a lot easier. This doesn’t mean they are easy to use. Sometimes it feels your brain is doing cartwheels inside your head, but what a joy it is to solve some of the more challenging programming puzzles. The performance is beyond compare, and it looks so easy when you’re done."

You can find it at: AMIS Technology Corner – Articles

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

4 Comments

  1. True!Analytic functions helps performance compared to traditional queries using joins.

  2. Pieter de Jager on

    Ik ben nu bezig met het artikel in de Optimize te bestuderen. Het artikel maakt de complexe materie op een zeer toegankelijke wijze begrijpelijk.
    Ook wel eens prettig iets Oracle-technisch in het nederlands te lezen.
    Een beetje jammer voor al die nederlandse niet Optimize-lezers dat het nederlandstalige artikel niet op de website staat.

  3. Totally agree;
    I found Analytic very usefull for processing a lager amount of rows. Alway when this was done by PL/SQL row by row logic the applcation runned in dramatically perfomrance problems.
    So i prefer the use of naalytic functions.
    The only thing is that logic behind is not so easy to understand – it’s more a pipelined process then a clear and modular and easy to understand PL/SQL Code.

    Karl