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 220.127.116.11). 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.