One Analytic Function can do more than a thousand lines of code... Oracle Headquarters Redwood Shores1 e1698667100526

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

July 25, 2006 Data Warehousing & BI, Database, Development Tools, IT, Oracle 4 Comments

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

Related Posts

About The Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

4 Comments

  1. Sunil D July 29, 2006

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

  3. Pieter de Jager July 26, 2006

    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.

  4. Carl R. July 25, 2006

    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