Path: newssvr20.news.prodigy.com!newsmst01a.news.prodigy.com!prodigy.com!news-FFM2.ecrc.net!newsfeed.stueberl.de!newsr1.ipcore.viaginterkom.de!news-peer1!news-peer0-test!btnet-feed5!btnet!news.btopenworld.com!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: Why isn't table A always key-preserved if equijoined to the key of table B? Date: Wed, 5 May 2004 19:58:28 +0000 (UTC) Organization: BT Openworld Lines: 69 Message-ID: References: NNTP-Posting-Host: host217-42-79-77.range217-42.btcentralplus.com X-Trace: sparta.btinternet.com 1083787108 26184 217.42.79.77 (5 May 2004 19:58:28 GMT) X-Complaints-To: news-complaints@lists.btinternet.com NNTP-Posting-Date: Wed, 5 May 2004 19:58:28 +0000 (UTC) X-Newsreader: Microsoft Outlook Express 6.00.2800.1409 X-MSMail-Priority: Normal X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:260890 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" wrote in message news:gnci90ljpaushcuvctcea69quqdhu3q9l9@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.