Oracle Scratchpad

January 6, 2012

I wish

Filed under: CBO,Execution plans,Oracle,Wishlist — Jonathan Lewis @ 5:38 pm BST Jan 6,2012

A few days ago I published an example of the optimizer failing to handle an updateable join view because it didn’t recognise that a particular type of aggregate subquery would guarantee key-preservation.  Here’s another example where the human eye can see key-preservation, but the optimizer can’t (even in 11.2.0.2). As usual we’ll start with some sample data – in this case two tables since I want to update from one table to the other.

create table t1 (
	id1	number,
	id2	number,
	val	number,
	constraint t1_pk primary key (id1, id2)
);

insert into t1 values (1,1,99);
commit;

create table t2 (
	id1	number,
	id2	number,
	id3	number,
	val	number,
	constraint t2_pk primary key (id1, id2, id3)
);

insert into t2 values (1,1,1,200);
insert into t2 values (1,1,2,200);
commit;

Note, particularly, that t1 has a two-part key, and t2 has a three-part key; and it’s perfectly reasonable to write a query like the following – and then I might use the query to define a view:

select
	t1.val	v1,
	t2.val	v2
from
	t1,t2
where
	t1.id1 = t2.id1
and	t1.id2 = t2.id2
and	t2.id3 = 1
;

You’ll note that table t2 is key-preserved. If I pick a row from t2, I use the primary key of table t1 to find a match – so any row that gets picked from t2 can appear at most once in the result set.

However, although the join itself doesn’t include all the columns in the primary key of t2, table t1 is also key-preserved in the view. If I pick a row from t1 the join condition may find several rows in t2 that match – but once the predicate t2.id3 = 1 is applied this will reduce the possible matches to at most one – so each row from t1 can appear at most once in the result set.

So what happens when you try these two updates ?

update
	(
	select
		t1.val	v1,
		t2.val	v2
	from
		t1,t2
	where
		t1.id1 = t2.id1
	and	t1.id2 = t2.id2
	and	t2.id3 = 1
)	iv
set
	iv.v2 = iv.v1
;

update
	(
	select
		t1.val	v1,
		t2.val	v2
	from
		t1,t2
	where
		t1.id1 = t2.id1
	and	t1.id2 = t2.id2
	and	t2.id3 = 1
)	iv
set
	iv.v1 = iv.v2
;

The first one works, you can update table t2 through the view; the second one fails with Oracle error “ORA-01779: cannot modify a column which maps to a non key-preserved table.”

You might want to try something clever with function-based indexes – after all, if we only want to do this update for the special case where id3 = 1 (or perhaps a limited number of special cases) we can create a unique index to help:

create unique index t2_fbi on t2(
	case when id3 = 1 then id1 end,
	case when id3 = 1 then id2 end
);

update
	(
	select
		t1.val	v1,
		t2.val	v2
	from
		t1,t2
	where
		case when t2.id3 = 1 then t2.id1 end = t1.id1
	and	case when t2.id3 = 1 then t2.id2 end = t1.id2
	)	iv
set
	iv.v1 = iv.v2
;

Even though the execution plan for the underlying query shows Oracle doing a unique scan on a unique index in a nested loop join, the update still fails with Oracle error ORA-01779.

I have to say that I’m not as disappointed with the example as I was with the aggregate subquery example. The aggregate example looks like a reasonable requirement, this one looks like an application design flaw (essentially, it has the flavour of an application that has stuck several entities into a single table) so I’ve not worried about it too much in the past.

Recently, though, I’ve seen an increasing number of people thinking about keeping old copies of data in the same table as the current copy – and one strategy for this is to have a flag that marks the current copy and uses the FBI trick I’ve just shown as a way to enforce uniqueness. The side effect may cause problems to a few people.

10 Comments »

  1. sokrates@11.2.0.2 > update
      2      (
      3      select
      4          t1.val  v1,
      5          t2.val  v2
      6      from
      7          t1,t2
      8      where
      9          t1.id1 = t2.id1
     10      and t1.id2 = t2.id2
     11      and t2.id3 = 1
     12  )   iv
     13  set
     14      iv.v1 = iv.v2
     15  ;
        iv.v1 = iv.v2
        *
    ERROR at line 14:
    ORA-01779: cannot modify a column which maps to a non key-preserved table
    
    
    sokrates@11.2.0.2 > REM so, we do the equivalent
    sokrates@11.2.0.2 > update
      2      (
      3      select
      4          t1.val v1,
      5          (select t2.val from t2
      6           where
      7               t1.id1 = t2.id1
      8           and t1.id2 = t2.id2
      9           and t2.id3 = 1
     10          ) v2
     11      from
     12          t1
     13      where (t1.id1, t1.id2) in
     14      (
     15         select t2.id1, t2.id2
     16         from t2
     17         where t2.id3 = 1
     18      )
     19  )   iv
     20  set
     21      iv.v1 = iv.v2
     22  ;
    
    1 row updated.
    

    Comment by Sokrates — January 6, 2012 @ 8:34 pm BST Jan 6,2012 | Reply

    • Sokrates,
      Thanks for the example.
      One thought, though – the main reason for liking the updateable join view is that it ought to make the “double” subquery unnecessary, i.e. code like:

      update t1
      set column X = (subquery)
      where exists (very similar subquery)
      

      Comment by Jonathan Lewis — January 26, 2012 @ 8:41 pm BST Jan 26,2012 | Reply

  2. Jonathan,

    here is another very simple case where ORA-01779 is raised unjustifiedly:

    create table t(
       id int,
       attr varchar2(10),
       constraint t_pk primary key(id)
    );   
    
    create table l(
       id int,
       k int,
       s varchar2(10),
       constraint l_pk primary key(id, k),
       constraint l_fk foreign key(id) references t(id)
    );   
    
    insert into t(id, attr) values(1, 'aaa');
    
    update
    (
       select t.id, t.attr
       from t, l
       where l.id(+)=t.id
       and l.k(+)=1
       and l.id is null
    )
    set attr = 'bbb';
    

    gives (11.2.0.2) ORA-01779: cannot modify a column which maps to a non key-preserved table

    a workaround would be

    update
    (
       select t.id, t.attr
       from t
       where not exists(select null from l where l.id = t.id and l.k=1)
    )
    set attr = 'bbb'
    

    Matthias

    Comment by Sokrates — January 9, 2012 @ 1:33 pm BST Jan 9,2012 | Reply

    • Matthias,
      That’s an interesting one.
      Easily visible to the human eye that T is key-preserved (it can only produce one row in the join), but not implemented.
      It makes me wonder if there are any other variants of a similar nature – i.e. key-preservation logically created by a predicate even though it isn’t implied by the underlying constraints.

      Comment by Jonathan Lewis — January 26, 2012 @ 8:47 pm BST Jan 26,2012 | Reply

      • Hi Jonathan,

        When you say “i.e. key-preservation logically created by a predicate even though it isn’t implied by the underlying constraints” do you mean to say even thought the key-preservation is logically evident from —where l.id(+)=t.id and l.k(+)=1 and l.id is null— but will not be implied if tables t and l are joined based upon the underlying constraints (pk-fk) i.e., —where l.id=t.id—?

        Thanks,
        Girish Singhal

        Comment by Girish Singhal — August 13, 2014 @ 1:28 pm BST Aug 13,2014 | Reply

        • Girish,

          In effect, yes. The optimizer seems to be considering only the columns used in the referential integrity constraints to derive key-preservation, but doesn’t allow for predicates completing the uniqueness requirement. (This comment is, of course, out of date for some cases as indicated by the pingback in comment 5.)

          Comment by Jonathan Lewis — August 15, 2014 @ 10:43 am BST Aug 15,2014

  3. virtual column:

    alter table t1 add (id3 as (1));
    
    update 
        ( 
        select 
            t1.val  v1, 
            t2.val  v2 
        from 
            t1,t2 
        where 
            t1.id1 = t2.id1 
        and t1.id2 = t2.id2 
        and t2.id3 = t1.id3 
    )   iv 
    set 
        iv.v1 = iv.v2 
    ;
    

    Comment by James Su — January 11, 2012 @ 7:21 pm BST Jan 11,2012 | Reply

  4. The virtual column is a very nice trick.
    My case is the history one, with a current version whose values I want to copy.
    Thank you for pointing me to this discussion,

    Comment by David Penington — June 3, 2013 @ 11:01 pm BST Jun 3,2013 | Reply

  5. […] Constant involved in key-preservation – fixed […]

    Pingback by 12c Join Views | Oracle Scratchpad — August 9, 2013 @ 6:37 pm BST Aug 9,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,267 other followers