Day 1 for me continued with a great session by Riyaj Shamsudeen on analytical SQL. This was exactly my sort of presentation - packed with examples, demonstrations and proof of what he was saying. In particular Riyaj did a great job on comparing the performance of old style queries that one could use to answer analytical questions and the newer syntax. Of particular benefit is the fact that in general if one needs to add another analytical metric then there is pretty much no further io cost to be incurred.

Next up was a session chairing duty for Phil Marshal's presentation on Index Compression, This was a nice straightforward and clear introduction to the subject, again evidence based. Two particular highlights for me were, the fact that a compressed index can end up larger than an uncompressed index - especially if the cardinality of the base table is high - and that most tests that demonstrate the space benefits of index compression overstate the case because they compare an index before being compressed with the same index uncompressed - however since this sort of test builds a compressed index by a rebuild operation the correct comparitor would be with a freshly rebuilt index (no compression).

I ended my conference day with Tom Kyte again talking - and this time demoing - 11g new features. First up Tom talked about tablespace level encryption in 11g, this feature overcomes two disadvantages of column encryption in earlier versions. These were the fact that you can't do an index range scan against an encrypted column (since adjacent data values when encrypted don't get stored next to each other so there is no range to scan). Perhaps rather more importantly referential integrity doesn't work because the encrypted value in a pk will not the the same as that same value encrypted in the fk column. In addition Tom neatly demonstrated the possibility of data theft - say from backup media - by the simple expedient of running strings against a datafile - complex hacking at its best!  Tom also covered virtual columns - giving you for example the ability to create a foreign key on a function, improvements to datapump import/export including two neat additions that make scripting and using a lot easier - the ability to reuse dumpfiles and the ability to compress the dump file - especially important if you are in the habit of transferring the dump either directly or indirectly - via an intermediate file - across a network link. Finally we had that rare opportunity the experience of Tom looking entirely stumped. The last feature Tom was demonstrating was the automatic creation of interval partitions on the arrival of a new row. Tom's code looked like this

create table t(ts timestamp,audit_text varchar2(10));

insert into t values ('02-Dec-2007','xx');

Unfortunately for Tom the partition created was for the year 2020 not the expected result. Fortunately, as I make this class of mistake frequently enough to recognise it was easy enough to show that Tom was relying on a default format for the timestamp value rather than using a to_timestamp function. Failing to do this meant that Oracle was interpreting the timestamp supplied as 7am on the morning of December 2nd 2020.  I have to say that Tom handled this surprise fantastically well - both generating laughs and promising to post the full explanation - or the bug number - on his blog.

 The evening was mostly spent at the Blogger's dinner, being introduced to Russian beer by Alex Gorbachev who impressively managed to blog about the event at 2am the morning before a presentation.

As a number of others have mentioned UKOUG 2007 is in full swing at the ICC in Birmingham.  I arrived last night and after the usual night before catch up with old friends and a surprisngly nice burger courtesy of all-bar-one it was off to bed and then this morning I broke the habit of a lifetime and sat in on the keynotes. - well the second keynote. This was Tom Kyte who was using the theme of unconventional innovation to talk about the history of innovation in the Oracle Database and the latest innovations in 11g. It has also to be the only time I have ever seen Tom present without sqlplus.

The first part of the keynote made me feel pretty old really. Tom asked the entire hall to stand and then to sit down when he called out the release at which they started working with Oracle. From my vantage point at the back it was pretty clear to see that there have been waves of fresh adopters/users both of 9i and 8i - the vast majority of the hall however went back to 7.3.4. We did have a smattering of Oracle 5 and 4 users withus and even one delegate who worked on 3. I had to sit down at 6 (which was released nearly 20 years ago in 1988!). The majority of the presentation though was a look at the new features in 11g. Highlights for me included :

• truly transparent access to the Oracle OLAP engine in the database via the query rewrite mechanism
• data mining access again through sql
• the data recovery advisor designed to answer the three core questions you'll encounter in a recovery scenario:
  Can I fix it?
  How do I fix it?
  How long will it take?

Incidentally Tom asked how many people had done a recovery in the last 30 days - maybe I misinterpreted the question but I was surprised at how few people put their hands up - we do test recoveries - or at least database duplications from the backup media - at least monthly. 

Next off I put my E-Business suite hat on and went to a session on devising a strategy for an upgrade to R12. I have to say that I was rather disappointed with this since approximately half the presentation showed off improvements in R12, and the remaining half essentially suggested that the drivers behind an upgrade strategy were much as they had always been. There was an interesting snippet though suggesting that in 2003 - 4 years after the release of 11i 60% of Oracle's customers were still on 10.7. Given the message that applications support will continue up to and beyond the fusion time frame and the suggestion by Larry Ellison that fusion apps may arrive in 2008 I was left a little bit wondering if R12 will be the release that never was for many people.

Finally before lunch it was off to hear the excellent Toon Koppelaars talking about the theory and practice of database constraint design. Starting with set theory and using just the emp and dept tables Toon took us through the implementation of database integrity constraints from theory to practice. Sadly we had to cut the session short, but an excellent discussion was held around set theory, logic, requirements specifications, mid-tier application design and so on.