Oracle 11g R2: 3 slick new features
I was reading up on the Oracle Magazine – the Ask Tom section in the November/December issue (posted here) has a quick summary of some of the new features in 11g. Two of them really caught my attention as they look really promising in dealing with some of the day-to-day scenarios we tend to tackle in warehousing.
The first is under the "Analytics" section of the article. It’s called the LISTAGG function and it’s super-cool. I can’t think of how many times we’ve wanted to concatenate a list of values into a delimited string – and now it’s really easy to do in 11gR2! And it’s very functional, allowing the list to be ordered by other columns (whether or not those columns exist in the SELECT clause). The SQL Language page for LISTAGG has a few simple examples, and Tom’s article has even more, including a comparison with the painful way we had to accomplish the same task in 10g (and 11gR1).
The second new analytic function Tom writes about is also very nice. NTH_VALUE lets you pick out the Nth entry from a window’s result set. It’s a nice compliment to the FIRST_VALUE and LAST_VALUE functions (see his example for all three). Again, the SQL Language page has more details on its usage and a great example.
Finally, one of my favorite new features because it’s one I wish we had in our recent engagements. At a couple of projects, we were tasked with identifying user sessions across a set of web logs (pretty common and basic stuff – mark a set of records with a common Session ID based on a user cookie’s time on the site, ordering by time, and so on). Everything in creating sessions is typically centered around a user cookie, so updating the records could benefit from parallelization – we could update a full set of records belonging to a single user cookie as a "chunk", and execute several chunks at the same time. Now, in our case, the "update" was a bit more complex than a simple DML statement, but if you wanted to chunk data and execute DML per chunk in parallel, DBMS_PARALLEL_EXECUTE may be a huge help. The package gives us the ability to create chunks (segments of data, based on ROWID or user-defined SQL), and then execute DML (or other) operations on those chunks in parallel, using the Scheduler to perform execution, logging, and error handling. The Doc page has all the details and examples. I haven’t tried this myself yet so it may already be possible, but it would be a huge bonus if, instead of basic DML statements, we could execute a Stored Procedure or a dynamic PL/SQL block on each chunk – that would be a huge win in simplifying parallel ETL inside the DB.
There are a bunch of other new features in 11g (R1 and R2) that we haven’t covered yet – several great enhancements that can help in the DW world. Definitely worth checking out.
