Oracle 11g Release 2 Analytics

Mark and Venkat have already been blogging about OWB 11g Release 2, but that was not the only new release to slip past Oracle's doors in recent days; 11g Release 2 of the database is also out.

I have long loved analytic functions in Oracle - they can give a simple way to avoid sub-queries and can help reduce some fairly difficult ETL problems to a simple SQL statement, one that can be exposed as a database view or matrialized view and then accesses by an ETL tool such as OWB or ODI. Of course, analytic functions are not a silver bullet, wrongly used they can cause problems; by necessity the PARTITION BY and ORDER BY clauses will require data sorts and, as I mentioned in a UKOUG talk a few years back, for large sort operations that may not be a cheap operation, especially if many analytic functions are used each with differing sorting requirements.

It was nice to see in the New Features Guide for Oracle 11gR2 two new analytic functions and a useful enhancement to two existing ones: LAG and LEAD were particular favourites of mine, the ability to "copy" a value from the next or previous row is so useful when, say, dealing with opening and closing stock values. The big failing (to my mind) was how this handled NULL values; this is now addressed by the option to IGNORE NULLS.

One of the new analytic functions also looks at rows around the current row; NTH_VALUE which is really a more general version of FIRST_VALUE and LAST_VALUE. The final new Analytic function is LISTAGG. This allows the concatenation of a measure column, that is a way of pivoting a column of data but presenting the results a single column. Here is the example in the Oracle 11g Data Warehousing Guide 11gR2

SELECT time_id, prod_id, MIN(amount_sold), LISTAGG(min(amount_sold),';')
WITHIN GROUP (ORDER BY prod_id) OVER (PARTITION BY time_id) cust_list
FROM sales WHERE time_id > '20-DEC-01' AND prod_id BETWEEN 120 AND 125
GROUP BY prod_id, time_id;

TIME_ID   PROD_ID   MIN(AMOUNT_SOLD)  CUST_LIST
-------   -------   ----------------  -----------
21-DEC-01     120            51.36    51.36;10.81
21-DEC-01     121            10.81    51.36;10.81
22-DEC-01     120            51.36    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     121            10.81    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     122            20.23    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     123            56.12    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     124            17.79    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     125            15.67    51.36;10.81;20.23;56.12;17.79;15.67
...