“Ask Tom” Oracle Seminar in Prague
Today and tomorrow I am at an Ask Tom Live Seminar in Prague. Tom Kyte works for Oracle and has written many books on Oracle. For me he's a bit of a "star", so when I heard he was talking in Europe, I had to come. (But is that wrong? Normal people are in to stars like Robbie Williams surely, not Oracle experts.)
Wow there is so much I don't know about Oracle evidently. I mean the solutions that he is presenting concerning Oracle 11g, are solutions to problems in features I've never used, such as partitioning, etc.
Some fun things I learnt:
- In Oracle 6 "sql plus" used to be offered as a for-cost extra, as it was considered so good! (Apparently there was a previous tool, and it was worse.) Sql Plus still doesn't support e.g. the "up" arrow key to get the previous command.
- In Oracle 3 "insert into x select from x" would be an infinite statement as it would constantly re-select then insert the data from x that it had just inserted. This was fixed in Oracle 4.
- Oracle 11 can create "virtual columns", i.e. "create table x(a number, b as (a+1))". You can then use these virtual columns for indexes (like functional indexes), foreign key constraints, etc. A statement like "select .. from x where a+1=?" will be rewritten as "where b=?" which will then use the index. Audience member asked "can I write an update statement to update b?"
- You can do "select .... as of [date]" and if the data is still available in the rollback segment, it will be returned.
- Adding a column is now always instantaneous, i.e. "alter table x add y number default 4" will not alter the data in the table, but when a row is read which was created before the alter table statement happened, the column will be added with its default value. (But presumably altering the column's default value then causes the long write?)
- Apparently Oracle was the first RDBMS. Well that's what Tom Kyte claims anyway.
P.S. I should have brought my Oracle One on One with me for him to autograph... OK that would have been ridiculous.