UKOUG Day 1.5

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.