Re: Why isn't table A always key-preserved if equijoined to the key of table B?

Your analysis is correct - the table is key-preserved, and Oracle 'ought' to recognise it.

There are other, cases where the optimizer fails to spot the key preservation. One day, no doubt, they will be covered.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Optimising Oracle Seminar - latest dates
http://www.jlcomp.demon.co.uk/seminar.html



"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message
news:gnci90ljpaushcuvctcea69quqdhu3q9l9_at_4ax.com...


> Hi,

>

> It seems to me table A should always be key-preserved if equijoined to

> the key of table B, but Oracle seems to disagree. Example:

>

> SQL> create table jvd_t1 (c1 number, c2 number,c3 number);

>

> Table created.

>

> SQL> create table jvd_t2 (d1 number, d2 number);

>

> Table created.

>

> SQL> alter table jvd_t2 add primary key (d1);

>

> Table altered.

> *********** the following update works:

> SQL> update (select t1.*,t2.*

> 2 from jvd_t1 t1,jvd_t2 t2

> 3 where c1 = d1 )

> 4 set c2=d2;

>

> 0 rows updated.

>

> ******* but this update does not:

> SQL> update (select t1.*,t2.*

> 2 from jvd_t1 t1,jvd_t2 t2

> 3 where c1||c3 = d1) -- this concatenation is the only difference

> 4 set c2=d2;

> set c2=d2

> *

> ERROR at line 4:

> ORA-01779: cannot modify a column which maps to a non key-preserved

> table

>

> Why does the key-preserverdness of a table depend on the way I use the

> columns to equijoin to the key of another table? Oracle can conclude

> that for every record of jvd_t1 I will still fetch only one record (at

> the most) from the lookup-table, so jvd_t1 is still key-preserved.

>

> Oracle 9.2 on Sun OS.

>

> Jaap.