Oracle Scratchpad

November 6, 2012

Learning

Filed under: Oracle,Troubleshooting,Tuning — Jonathan Lewis @ 5:47 pm GMT Nov 6,2012

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.

5 Comments »

  1. and 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.

    all I have to say is… “Well done that man!”

    Comment by David Pyke Le Brun — November 7, 2012 @ 11:27 pm GMT Nov 7,2012 | Reply

  2. 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 :-)

    Comment by Uwe Hesse — November 9, 2012 @ 7:31 am GMT Nov 9,2012 | Reply

  3. That’s why after 14 years with Oracle one still feels an advanced learner and sometimes not even that.

    Comment by Flavio — November 11, 2012 @ 2:35 pm GMT Nov 11,2012 | Reply

  4. I presume one of the condition in the fifth thought would include the RELY or NORELY constraint state.

    Comment by srivenu kadiyala — November 13, 2012 @ 6:48 pm GMT Nov 13,2012 | Reply

  5. 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.

    Here’s a cut-n-paste from a session running 11.2.0.2:

    
    SQL> set autotrace on explain
    SQL> select  chi.name from child chi , parent par where par.id = chi.id_p;
    
    NAME
    ----------
    Simon
    Sally
    Jack
    Jill
    
    4 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2406669797
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     4 |    36 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| CHILD |     4 |    36 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("CHI"."ID_P" IS NOT NULL)
    
    SQL> select  /*+ no_eliminate_join(par) */ chi.name from child chi , parent par where par.id = chi.id_p;
    
    NAME
    ----------
    Simon
    Simon
    Sally
    Sally
    Jack
    Jill
    
    6 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1687613841
    
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |     4 |    48 |     3   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS      |        |     4 |    48 |     3   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| CHILD  |     4 |    36 |     3   (0)| 00:00:01 |
    |*  3 |   INDEX RANGE SCAN | PAR_PK |     1 |     3 |     0   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("PAR"."ID"="CHI"."ID_P")
    
    SQL> spool off
    
    SQL>
    
    
    

    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.

    Comment by Jonathan Lewis — December 13, 2012 @ 5:24 pm GMT Dec 13,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,521 other followers