Search

Top 60 Oracle Blogs

Recent comments

I wish

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.