One of the deficiencies of SQL is the lack of support for analytic calculations like moving averages, cumulative sums, ranking, percentile and lead and lag which are critical for OLAP applications. These functions work on ordered sets of data. Expressing these functions in current SQL is not elegant, requires self-joins and is difficult to optimize. To address this issue, Oracle has introduced SQL extensions called Analytic functions in Oracle8i. In the presentation, I would discuss the motivation behind these functions, their structure and semantics, their usage for answering reasonably complex business questions and various algorithms for their optimization in the RDBMS server. The scope of OLAP support afforded by these new functions far exceeds existing SQL functionality. Oracle and IBM have proposed these SQL extensions to ANSI to be included in the SQL-99 standard.
Abhinav is a senior member in the RDBMS OLAP group at Oracle. He is involved in enhancing the analytic capabilities of the database server. Prior to joining Oracle, he did B.Tech in CS from IIT-Delhi in 1996 followed by MS in CS from University of Winsconsin-Madison in 1997.