11 October 2016

OTN Appreciation Day: Analytic Functions

This is my contribution to the OTN Appreciation Day, which was initiated by Tim Hall.

One of my favorite features of the Oracle Database are Analytic Functions. They were introduced with Oracle Database 8.1.6 Enterprise Edition, and have been in the Standard Edition since version 9.

With analytic functions you can add inter-row calculations, aggegrate over multiple dimensions, rank assignments based on values. All this without a GROUP BY clause.
The syntax might take some getting used to, but you'll find that it's not that hard.

While I was still working at AMIS, I followed their 7UP training (all new features starting from Oracle 7 onwards) and that's when I first learned about Analytic Functions.
A little bit after that training, while doing consulting work for T-Mobile, I was triggered by a colleague to come up with an analytic function solution to the problem at hand. That was the moment that I really fell in love with Analytic Functions. It provided a very elegant and extremely performant solution.
The blog I wrote back then is still available at the AMIS-site: Analytic Power

#ThanksOTN

2 comments: