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

4
Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

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 this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

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 & PL/SQL), Service Oriented Architecture, BPM, ADF, JavaScript, Java in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on conferences such as JavaOne and Oracle OpenWorld. 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