Comments on: Learninghttps://jonathanlewis.wordpress.com/2012/11/06/learning/
Just another Oracle weblogTue, 03 Mar 2015 01:11:37 +0000hourly1http://wordpress.com/By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2012/11/06/learning/#comment-52088
Thu, 13 Dec 2012 17:24:24 +0000http://jonathanlewis.wordpress.com/?p=9856#comment-52088The 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.

]]>By: srivenu kadiyalahttps://jonathanlewis.wordpress.com/2012/11/06/learning/#comment-51431
Tue, 13 Nov 2012 18:48:06 +0000http://jonathanlewis.wordpress.com/?p=9856#comment-51431I presume one of the condition in the fifth thought would include the RELY or NORELY constraint state.
]]>By: Flaviohttps://jonathanlewis.wordpress.com/2012/11/06/learning/#comment-51391
Sun, 11 Nov 2012 14:35:34 +0000http://jonathanlewis.wordpress.com/?p=9856#comment-51391That’s why after 14 years with Oracle one still feels an advanced learner and sometimes not even that.
]]>By: Uwe Hessehttps://jonathanlewis.wordpress.com/2012/11/06/learning/#comment-51351
Fri, 09 Nov 2012 07:31:12 +0000http://jonathanlewis.wordpress.com/?p=9856#comment-51351Reminds 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 :-)
]]>By: David Pyke Le Brunhttps://jonathanlewis.wordpress.com/2012/11/06/learning/#comment-51310
Wed, 07 Nov 2012 23:27:04 +0000http://jonathanlewis.wordpress.com/?p=9856#comment-51310and that is EXACTLY why you manage to be so good at avoiding errors.

becuase not only have you come up with a list of interesting options.. you are now actually going to test them.