Oracle Scratchpad

October 18, 2013

Deferrable RI

Filed under: Bugs,Execution plans,Oracle — Jonathan Lewis @ 6:08 pm GMT Oct 18,2013

Here’s a lovely little example that just came up on the OTN database forum of how things break when features collide. It’s a bug (I haven’t looked for the number) that seems to be fixed in 12.1.0.1. All it takes is a deferrable foreign key and an outer join. I’ve changed the table and column names from the original, and limited the deferability to just the foreign key:


create table parent(id_p date primary key);
create table child(id_c date not null references parent(id_p) deferrable);

alter session set constraints = deferred;

insert into child values(sysdate);
insert into child values(sysdate);
insert into child values(sysdate);

select
	par.id_p, chi.id_c
from
	child chi
left join
	parent par
on 	par.id_p = chi.id_c
where	par.id_p is null
and	chi.id_c is not null
;

select
	chi.id_c
from
	child chi
left join
	parent par
on	par.id_p = chi.id_c
where	par.id_p is null
and	chi.id_c is not null
;

You’ll notice that the difference between the two queries is that the first one selects columns from both the parent and child tables; the second selects only from the child. Since the join is across a parent/child referential integrity constraint, and the primary key is a single column, and no columns from the parent appear in the select list, the optimizer is able to invoke “join elimination” in the second case – except that it shouldn’t because the side effect is to produce the wrong answer. Here are the two sets of results when running 11.2.0.4:

ID_P      ID_C
--------- ---------
          18-OCT-13
          18-OCT-13
          18-OCT-13

3 rows selected.

no rows selected

In 12.1.0.1 both queries produce the same (first) set of results.

In the second query 11g Oracle (incorrectly) removes the join to the parent table and replaces it with the predicate “id_c is null” (since the only effect of the join would normally be to eliminate any rows where id_c is null), this predicate is then combined with the existing “id_c is not null” predicate to produce “null is not null” – which is why we get no rows returned.

The problem, of course, is that the removal and substitution is only valid if the constraint check is in a valid state, and at this point we have deferred the constraint and got some bad data into the child table – Oracle should not do join elimination. (As a side note, this is why you do not see join elimination occurring when the critical constraints are in the state: “enable novalidate” – the tables may contain invalid data which means the predicate substitution may change the result.)

Here are the two sets of plans, first 11.2.0.4


------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |    82 |  1476 |     2   (0)| 00:00:01 |
|*  1 |  FILTER             |              |       |       |            |          |
|   2 |   NESTED LOOPS OUTER|              |    82 |  1476 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| CHILD        |    82 |   738 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0010228 |     1 |     9 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("PAR"."ID_P" IS NULL)
   4 - access("PAR"."ID_P"(+)="CHI"."ID_C")

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     9 |     0   (0)|          |
|*  1 |  FILTER            |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL| CHILD |    82 |   738 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

And now 12.1.0.1


------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |    82 |  1476 |     2   (0)| 00:00:01 |
|*  1 |  FILTER             |              |       |       |            |          |
|   2 |   NESTED LOOPS OUTER|              |    82 |  1476 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| CHILD        |    82 |   738 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0011569 |     1 |     9 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("PAR"."ID_P" IS NULL)
   4 - access("PAR"."ID_P"(+)="CHI"."ID_C")

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |    82 |  1476 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |              |    82 |  1476 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CHILD        |    82 |   738 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C0011569 |     1 |     9 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PAR"."ID_P"="CHI"."ID_C")

It’s interesting to note that 12c is able to convert the second query into an anti join (in other words it has changed an outer join to a (not exists) subquery, and then transformed it back into a different type of join).

One little aside – the first thought I had about the error was that it might be a side effect of the ANSI style join and something that the optimizer was messing up in the transformation to “traditional” style, so I have repeated the test using traditional Oracle syntax, and the problem persists.

Footnote:

According to MoS Document ID 858120.1 Join elimination will work for multi-column referential integrity constraints in version 12.2

Update 20/10/2013:

Corrected thanks to a twitter comment, I had swapped the ‘id_c is null’ and ‘id_c is not null’ in my explanation of the error.

6 Comments »

  1. Jonathan,
    Just wanted to share my results… This does not reproduced in 10g R2 EE but reproduced in 11g R2 XE seems to be optimization related issue…
    Regards,
    Jagdeep Sangwan

    Comment by Jagdeep Sangwan — October 22, 2013 @ 7:48 am GMT Oct 22,2013 | Reply

    • Jagdeep,

      Which specific version of 10gR2 ? Join elimination appeared at some point in the 10g time line, and the hint “ELIMINATE_JOIN” exists in 10.2.0.1 but I don’t recall exactly when the feature really worked. Could you check whether join elimination appears in your version when the constraint is NOT deferrable.

      Comment by Jonathan Lewis — October 22, 2013 @ 9:11 am GMT Oct 22,2013 | Reply

      • Jonathan,

        My exact version details are as following:

        BANNER
        —————————————————————-
        Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Prod
        PL/SQL Release 10.2.0.2.0 – Production
        CORE 10.2.0.2.0 Production
        TNS for 32-bit Windows: Version 10.2.0.2.0 – Production
        NLSRTL Version 10.2.0.2.0 – Production

        Tried to use the hint also without constraint deferred also as you requested. But could not reproduce the results in 10g

        Regards,
        Jagdeep Sangwan

        Comment by Jagdeep Sangwan — October 22, 2013 @ 12:04 pm GMT Oct 22,2013 | Reply

        • I’ve just tested 10.2.0.4
          It looks as if that version won’t do join elimination on an outer join – so the opportunity for error doesn’t appear.

          Comment by Jonathan Lewis — October 22, 2013 @ 3:29 pm GMT Oct 22,2013

        • Jagdeep,

          There are some interesting and relevant notes – especially regarding the evolution from 10g to 11g – in Maria Colgan’s article on join elimination at this URL.

          Comment by Jonathan Lewis — October 22, 2013 @ 6:31 pm GMT Oct 22,2013

  2. Thanks for the update and sharing the informative link Jonathan.

    Comment by Jagdeep Sangwan — October 23, 2013 @ 5:29 am GMT Oct 23,2013 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,429 other followers