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.

I know the rules for Oracle row locking well. A row can be locked for write if one updates it, or if one “select for update”s it.

1. create table a (x number); (and equivalent in MySQL for InnoDB)
2. Session A: insert into a values (9);
3. Session A: commit;
4. Session A: start transaction (in mysql)
5. Session A: select * from a where x=9 for update;
6. Session B: start transaction (in mysql)
7. Session B: select * from a where x=9 for update;
8. Session B hangs, waiting for row-level lock to be release from the row by Session A
9. Session A: update a set x=4;
10. Session A: commit;
11. At this point, Session B returns no rows. Lock has been released, and row no longer confirms to where, so is not returned.
12. Session A: update a set x=5;

This is where the difference occurs:

• Oracle returns the row to session A. The command “for update” in session B did not return any rows, and thus no rows were locked, and thus session A has nothing to wait on.
• MySQL (version 4.1.18) blocks session A waiting on the transaction in session B to be ended. That means A requires a lock owned by B. But what is this lock? Is it a row-level lock on the row which was not selected? Some other type of lock?

The reason I tested this was it just occurred to me that in a “select for update” the where clause is applied twice:

1. Firstly to determine which rows to return. A lock is requested for those rows, which can involve waiting until the lock is released if it is owned by some other transaction.
2. After the lock has been acquired, the rows is checked again to see if it still confirms to the where clause. If not, it is not returned (although the session blocked waiting for it)

I suspect that both databases simply do step 1 involving waiting and acquiring the lock on the rows which initially conform to the where clause. And both apply the where in step 2, as in both cases no rows were actually returned dispite the wait. But Oracle, upon noticing the row will not be returned, actually releases the lock as the command implies only rows returned will be locked. But MySQL just leaves the lock form step 1 hanging around, which is wrong.

From our Oracle test instance:

1360965 rows created.
Elapsed: 00:01:17.90

That's 1¼ minutes to insert (and index) over 1¼ million rows.

And this is a very old test instance. I think the hardware was last updated 2-3 years ago.

That's pretty quick!

Database error messages in general are very bad. Why?

Oracle Version 8 had lots of messages such as "Invalid column name" where they meant:

1. Column name not found in the table in question. The word "invalid" is the wrong word as it implies illegal characters or something like that.
2. Which column? Which table? The parser surely knows this at the time it generates the error message. But it helpfully chooses not to inform the user.

Thankfully Oracle 10 has improved its error messages a lot. They include the statement in question and the point in the statement producing the problem. And error messages contain which foreign key constraint has been violated, and so on.

But I have the following problem with MySQL. I try to create a table with InnoDB with a foreign key constraint and it says:

ERROR 1005 (HY000):
Can't create table './myschema/mytable.frm' (errno: 150)

What it means is: the statement has an error in it. But what is the error? In this case, there was a foreign key constraint and the column didn't exist in the referenced table. But why couldn't it tell me this?