Introduced with Oracle 8i release 2 (and extended in Oracle 9) it is now possible to perform complex analytic tasks without the need for complex sub queries or PL/SQL programming.
The analytic features are defined as part of the ANSI SQL 1999 standard and have also been implemented by IBM in DB2. This new functionality can often result in order of magnitude gains in performance.
Grouping Sets (GROUPING, CUBE and ROLLUP) - Instead of a UNION ALL statement that requires multiple table scans, define a grouping set which will result in only a single pass over the base table.
The WITH Clause - Define and re-use a query block when it occurs more than once within a complex query.
This will improve performance and will often make the SQL statement easier to read.
Top n Ranking - Rank, Dense_Rank, row_number, ntile, percent_rank and cume_dist functions.
Provides answers to questions like "Show the top 10 of x" or "Show the bottom 20% of y"
Aggregate Window - All aggregate functions (SUM, MAX, AVG, FIRST_VALUE, LAST_VALUE…)
Easy calculation of moving averages, cumulative sums.
FIRST and LAST - Aggregate function for the first or last value of an ordered set.
e.g. find the largest sale during the first month of each year.
Reporting Functions - Like Aggregate Window Functions with the difference that the Window ranges from the beginning to the end of the partition, so each row in the partition is reported with an aggregate over the whole partition.
Lag and Lead - Lag/Lead functions make it possible to access values in rows other than the current row without the need to perform a self join.
CASE and Width_Bucket Functions
Group data into even or unevenly sized buckets.
"When a team outgrows individual performance and learns team confidence,
excellence becomes a reality” ~ Joe Paterno
See also
Materialised Views which can provide similar
performance benefits.