One Analytic Function can do more than a thousand lines of code…
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
- Analytic Functions and the Oracle Data Cartridge
- Smart denormalization and Enhanced PL/SQL Function Call performance – Comparing Function Based Index and Materialized Views
- Oracle 10g's Finest – The Top 3 SQL and PL/SQL Features new in 10g – paper for ODTUG 2005
- Courtesy of Tom Kyte: Generating rows in SQL with the CUBE statement – no dummy table or table function required
- Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines)