November 6, 2012

Learning

Possibly an item to file under “philosophy”, but a question came up in the seminar I was presenting today that prompted me to blog (very briefly) about why I manage to be so good at avoiding errors and inventing workarounds to problems. You probably know that you may see an execution plan change when you add a foreign key constraint to a table – but today someone in the class asked me if this would still work if the constraint were to be disabled. The immediate response that sprang to my mind was “surely not” – but the second thought was that I didn’t really know the answer and would have to check; and the third thought was that maybe it wouldn’t if disabled, but what about novalidate; and the fourth thought was whether the setting for query_rewrite_integrity would make a difference; and the fifth thought was to wonder if there were other conditions that mattered.

So hey-ho for the weekend, when I have to set up a test case for a query that changes plan when I add a foreign key constraint – and then try (at least) eight different combinations of circumstances to check what it really takes to make the change happen.

Reminds me of numerous cases when I got a question: If it isn’t really trivial or asked often before, many times I end up with creating a test case to find out. Because of the meanwhile tremendous complexity of the Oracle Database (and because things I used to know have changed over time), even apparently obvious answers may need such research. But after all, that’s what keeps my job being interesting :-)

The obvious thing to experiment with is the “join elimination” feature of Oracle 11g. If a single column join to primary key is not going to eliminate data from the join, then Oracle can eliminate the table completely. This depends largely on referential integrity (i.e. primary key / foreign key) being declared between the tables.

Here’s a gem – which I will have to write up as a proper blog when I get the time: if the primary key is deferrable, you can create duplicate rows in the parent table (temporarily), which should duplicate data in the join; but the optimizer doesn’t notice when the constraint has been deferred and allows join elimination to take place: so you can get results that are arguably wrong when you defer a primary key.

When we disable join elimination the result changes.
The parent id for Sally and Simon exists twice in the parent table, and it’s legal for it to exist twice because I’ve made the primary key constraint on parent deferrable and deferred it.