Skip to Main Content
  • Questions
  • Key-preserved table concept in join view

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, GAURANG.

Asked: August 14, 2000 - 3:58 pm UTC

Last updated: January 23, 2023 - 6:49 am UTC

Version: 8.1.5

Viewed 50K+ times! This question is

You Asked

Could you explain me about Key-preserved table concept
in join views.i know that a table is ket preserved if every key of the table can also be a key of result of the join.
but i not understand meaning of this.

Thanks

and Tom said...

Key preserved means the row from the base table will appear AT MOST ONCE in the output view on that table.

For example, lets say I have tables T1 and T2 and a view:

create view T
as
select * from t1, t2 where t1.x = t2.y
/

Now, I do not know if for any given row in T1 if it will appear in the output result set 0, 1 or MANY times (eg: if a given t1.x is joined to every row in t2 by y and there are 2 rows in t2 with the same value for y -- then that row in t1 will be output 2 times).

For example, say you have:

T1.X T2.Y
------ --------
1 1
1

That single row T1 in the view will be output 2 times. If we were to issue:

update T set x = x+1;

The outcome would be to set X=3, since it appears in there 2 times. That is not key preserved and we will not allow that to happen (the results can be ambigous depending on an access plan -- the same statements could result in DIFFERENT data in the database depending on how we access the data).

So, if you can assert that a given row in a table will appear at most once in the view -- that table is "key preserved" in the view. It sounds backwards because in our example above -- its T2 that needs a unique constraint on Y if we want T1 to be key preserved (you put the key on T2, not on T1 to key preserve T1!)

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:273215737113 <code>
for more examples....


Rating

  (61 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

OK

Richard, April 21, 2004 - 2:05 am UTC

Hi Tom,
SQL> select rowid from user_tables;
select rowid from user_tables
                  *
ERROR at line 1:
ORA-01445: cannot select ROWID from a join view without a key-preserved table 

What does Oracle Complain about?

 

Tom Kyte
April 21, 2004 - 7:50 pm UTC

there is no rowid that could be returned from that view, that is what it is saying.

user_tables is a view
of many tables
and it is ambigous which tables rowid might be returned from this view
hence, it says "no"

OK

Kumar, May 06, 2005 - 1:34 pm UTC

Hello Tom,
I get an error while inserting into a view.
Please see below.


SQL> create table a(x int primary key)
  2  /

Table created.

SQL> create table b(x references a,y varchar2(30))
  2  /

Table created.

SQL> insert into a values(1)
  2  /

1 row created.

SQL> insert into b values(1,'One')
  2  /

1 row created.

SQL> create or replace view v
  2  as
  3  select a.x,b.x as b_x,y from a,b where a.x = b.x
  4  /

View created.

SQL> select * from v
  2  /

         X        B_X Y                                                         
---------- ---------- ------------------------------                            
         1          1 One                                                       

SQL> insert into v values(2,2,'Two')
  2  /
insert into v values(2,2,'Two')
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table 

How to correct this and 
another way exist to insert a row or update it 
either using SQL or Instead of Triggers???

Please do reply.
Bye!


 

Tom Kyte
May 06, 2005 - 2:12 pm UTC

well if you think about it -- what should that insert do?

insert into a (ok, proabably we can agree on that)

insert into b? no update b? no insert b? no......

you'd have to 'teach' that view to be insertable using your own logic in an instead of trigger.

Thanks

Kumar, May 07, 2005 - 3:01 am UTC

Hi Tom,
Thanks for your reply.
I tried this trigger.But it is not effective.
Any problem with the trigger logic???

SQL> create or replace trigger v_t
  2  instead of insert on v
  3  for each row
  4  begin
  5  if :old.x <> :new.x then
  6   insert into a values(:new.x);
  7  end if;
  8  if :old.b_x <> :new.b_x then
  9   insert into b values(:new.b_x,:new.y);
 10  end if;
 11* end;
SQL> /

Trigger created.

SQL> insert into v values(20,20,'Twenty')
  2  /

1 row created.

SQL> select * from v
  2  /

         X        B_X Y
---------- ---------- ------------------------------
         1          1 One



 

Tom Kyte
May 07, 2005 - 8:08 am UTC

umm, you need to tell me what your logic should be. What would YOU like to happen.


did you peek at your base tables and try to debug this at all? what was in A and B after you were done and would you have expected to see those values in the view V.


In your view V, probably doesn't make sense to have X from A and B, since X is logically the same. it would make your insert "easier" too.




OK

Kumar, May 07, 2005 - 1:09 pm UTC

Hi Tom,
Sorry,I don't understand your point.
After I do an insert into the view,
I never find those data either in the view
or in any of the underlying tables.

Is my understanding wrong to expect those values
in the view or base tables??


If possible,could you please provide the code snippet
for the trigger??



Tom Kyte
May 07, 2005 - 1:46 pm UTC

bug in your trigger,


5 if :old.x <> :new.x then
6 insert into a values(:new.x);
7 end if;
8 if :old.b_x <> :new.b_x then

:old is all NULL in an insert trigger.

you just want to insert the values, no if's

Is "key preserved" sufficient for updatability?

Huy, July 01, 2005 - 6:09 am UTC

Recently I came across a case where "key preserved" is still not updatable.

Suppose I want to update each employee's salary and make it the maximum salary in the department the employee is in. The following query would not work even if "key preserved" is satisfied:

update (select max(a.sal)
over(partition by (a.dept)) max_dept_sal,
a.*
from emp a) b
set b.sal = b.max_dept_sal;

This query would give "ORA-01732: data manipulation operation is not legal on this view".

How would you get around this problem?

Tom Kyte
July 01, 2005 - 10:17 am UTC

that has nothing to do with key preserved??

key preserved kicks in when you join two tables together, there is one table here.

The analytic infers a sort/ordering -- that is what is preventing it.



ops$tkyte@ORA9IR2> select empno, sal from emp;
 
     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300
 
14 rows selected.
 
ops$tkyte@ORA9IR2> merge into emp a
  2  using ( select deptno, max(sal) sal from emp group by deptno ) b
  3  on ( a.deptno = b.deptno )
  4  when matched then update set sal = b.sal
  5  when not matched then insert (empno) values ( NULL);
 
14 rows merged.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select empno, sal from emp;
 
     EMPNO        SAL
---------- ----------
      7369       3000
      7499       2850
      7521       2850
      7566       3000
      7654       2850
      7698       2850
      7782       5000
      7788       3000
      7839       5000
      7844       2850
      7876       3000
      7900       2850
      7902       3000
      7934       5000
 
14 rows selected.


In 10g, the "when not matched" can just be left off 

Knowing the undelying base table/s for a view

Vinci, August 01, 2005 - 6:45 pm UTC

SQL> update all_directories set directory_path='C:\oracle\product\10.1.0\oradata\orcl\new'
  where directory_path = 'C:\oracle\product\10.1.0\oradata\orcl\old';

update all_directories set directory_path='C:\oracle\product\10.1.0\oradata\orcl\new'
                           *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


Above is the error I am getting.
I want to directly update the underlying table ? Is there any way I can know what are the tables used for creating the view ?
 

Tom Kyte
August 01, 2005 - 8:49 pm UTC

OH MY.

you cannot,
you will not,
you shall not,
you should never consider,
you will never consider

UPDATING THE DATA DICTIONARY VIA DML STATEMENTS

the only way, repeat THE ONLY WAY to modify the dictionary is via a DDL statement.

Don't even dream about going there. Ever. Never. Period.

Knowing the underlying base table/s for a view

Vinci, August 02, 2005 - 10:51 am UTC

Why it shouldn't be done ? What are the side effects ?

Just before getting the response I found that dir$ table has the column which is used in the view all_directories . And I updated dir$ with new value and it is reflected in the view all_directories (Using 'UPDATE').

Tom Kyte
August 02, 2005 - 1:54 pm UTC

STOP - right now and forever more just STOP.

Do you edit the inode table to change the size of a file in UNIX???

Do you use a binary editor on your NTFS to erase a file?

Do you pull a word document up in VI and change some bytes to get a different FONT?


STOP IT, don't ever -- repeat DO NOT EVER update the Oracle data dictionary. (support tells you to do so - fine, but unless and until support tells you to do so -- don't even THINK ABOUT IT)

You have NO IDEA what dependencies are hidden down there, what needs to be done. *NO IDEA*. You don't, I don't, they (your friends) don't.

Updating the data dictionary is the worst thing you can do.

You better just shutdown abort and startup now and hope for the best. (you know, we actually cache things in the SGA -- rows fetched from the dictionary. The dictionary is OFF LIMITS TO YOU as far as updates go)

the ONLY WAY to repoint a directory is

a) drop it
b) create it

period, end of story. DDL is the ONLY way you make changes to the dictionary. Hands off.




Knowing the underlying base table/s for a view

Vinci, August 02, 2005 - 5:10 pm UTC

That makes complete sense to me. Thanks for opening my eyes.
I could have easily done by dropping the directory.

The update I did earlier was the following:

update dir$ set os_path='C:\oracle\product\10.1.0\oradata\orcl\new' where os_path='C:\oracle\product\10.1.0\oradata\orcl\old';

In the cmd prompt I copied some files in to this newly created directory.

Tom Kyte
August 02, 2005 - 7:15 pm UTC

never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never never

do that again ;)

please - not to anything in the dictionary. Not unless support says "you must do this, really"



Vinci

Matthias Rogel, August 03, 2005 - 4:26 am UTC

why not use a binary editor to edit the datafile
containting your SYSTEM-tablespace ?

Wouldn't it be more elegant than
update dir$ ...
?

:-D

is it mandatory

Vineet, August 03, 2005 - 8:25 am UTC

Tom,

With reference to your first review.

<quote>

So, if you can assert that a given row in a table will appear at most once in
the view -- that table is "key preserved" in the view. It sounds backwards
because in our example above -- its T2 that needs a unique constraint on Why if we
want T1 to be key preserved (you put the key on T2, not on T1 to key preserve
T1!)

</quote>


Why do we need to put a unique/ primary key constraints on the Table T2, if we are updating a column in table T1.

Can you please explain me the logic behind this?

Tom Kyte
August 03, 2005 - 11:44 am UTC

The optimizer needs to be convinced that when we join T1 to T2, the rows in T1 will appear AT MOST ONCE in the resulting join.

Otherwise, the update of the join would tend to update the row in T1 more than once - leading to non-deterministic results (eg: same data, same udpate, different end results because the rows just happened to get processed in a different order)

why is a group by not inherently key preserved?

Mark, August 31, 2005 - 1:19 pm UTC

I'm trying to update a table from history, recreating the table, but I get the non key preserved table error. I don't understand why a group by is not inherently key preserved, since it is, by definition, unique. Here's the situation:

create table edits (unique_id number, this_field varchar2(255));

insert into edits VALUES (1,'');

create table history (unique_id number, latest_value varchar2(255), mod_time date);

insert into history VALUES (1,'FIRST VAL',to_date('01/01/01','YY/MM/DD'));
insert into history VALUES (1,'SECOND VAL',to_date('02/01/01','YY/MM/DD'));

COMMIT;

update (select h.latest_value, e.this_field from
(select
unique_id,
MAX(latest_value) KEEP (DENSE_RANK LAST ORDER BY MOD_TIME) latest_value
from
history
GROUP BY unique_id) h, edits e
where e.unique_id = h.unique_id)
SET this_field = latest_value;

Tom Kyte
August 31, 2005 - 2:16 pm UTC

I don't know.... but it isn't.

10gr1? you can use merge (in 9i you can too - we just have to make sure the insert part never gets fired)

non-(key, whole key and nothing but the key) preserved

Duke Ganote, June 21, 2006 - 10:59 pm UTC

I'd've thought this should work, but it didn't...

create table etl_info (
table_name varchar2(30)
, row_number number
, column_name varchar2(30)
, column_value number
)
/
alter table etl_info
add constraint etl_info_Pk
primary key (
table_name, row_number, column_name
/
create table etl_test (
row_number number primary key
, someid number )
/
insert into etl_info values
( 'ETL_TEST', 1, 'SOMEID', 1 )
/
insert into etl_test values
( 1, null )
/
commit
/
update ( select someid, column_value
from etl_info inner join etl_test
on etl_info.row_number = etl_test.row_number
and etl_info.table_name = 'ETL_TEST'
and etl_info.column_name = 'SOMEID' )
set someid = column_vale
/

set someid = column_value
*
ERROR at line 6:
ORA-01779: cannot modify a column which maps to a non key-preserved table

"Obviously" a unique record on ETL_INFO is specified, since values for all three parts of the key are specified. The join must use all key elements?

alter table etl_info drop primary key
/
alter table etl_info add primary key ( row_number )
/
update ( select someid, column_value
from etl_info inner join etl_test
on etl_info.row_number = etl_test.row_number
and etl_info.table_name = 'ETL_TEST'
and etl_info.column_name = 'SOMEID' )
set someid = column_value
/

1 row updated.

I tried this in 9iR2 and 10gR2.

Tom Kyte
June 22, 2006 - 12:13 pm UTC

all of the columns must be joined to - yes, the constants are "not sufficient"

in 10g, we can use merge (in 9i you can too but have to supply a "when not matched" clause as well)

Merge statment and inserting null

Huy, July 03, 2006 - 6:55 am UTC

On 1 July 2005 you gave an example for 9i:

ops$tkyte@ORA9IR2> merge into emp a
2 using ( select deptno, max(sal) sal from emp group by deptno ) b
3 on ( a.deptno = b.deptno )
4 when matched then update set sal = b.sal
5 when not matched then insert (empno) values ( NULL);

which would insert a row of nulls. Can the merge statement in 9i do nothing when not matched?

Thank you vey much

Tom Kyte
July 07, 2006 - 3:22 pm UTC

it would FAIL - since empno is a primary key.

The "when not matched" was a dummy insert necessary for syntax in 9i (not in 10g) but would *never* happen - since you are of course merging into EMP a selection FROM emp itself (hence the when not matched is a big "no-operation", it never happens)

in 9i, you need a when matched AND a when not matched, you would write the subquery (in the using) to ensure the when not matched NEVER happens if necessary in 9i

MERGE vs key-preservation

Duke Ganote, July 05, 2006 - 2:43 pm UTC

Huy-- What Tom means is that we can make that the insert condition never occurs.  Using my example, I can guarantee that the MERGE only UPDATEs by INNER JOINING to the target table.

Here's the target table before:

select * from etl_test
/

ROW_NUMBER     SOMEID
---------- ----------
         1          9

Here I add a row to my source table (ETL_INFO) that I don't want to INSERT into ETL_TEST.

insert into etl_info values
( 'ETL_TEST', 2, 'SOMEID', 2 )
/

1 row created.

select row_number, column_value from etl_info
/

ROW_NUMBER COLUMN_VALUE
---------- ------------
         1            1
         2            2


But my MERGE statement has an INNER JOIN in the USING clause.  An INSERT cannot occur, even though I had to have the WHEN NOT MATCHED clause.  Only a 1 row UPDATE occurs:

merge into etl_test t
using ( select etl_info.row_number, column_value
          from etl_info INNER JOIN etl_test
            on etl_info.row_number = etl_test.row_number
         where etl_info.table_name = 'ETL_TEST'
           and etl_info.column_name = 'SOMEID' ) e
  on ( e.row_number = t.row_number )
 when matched then
    update set someid = column_value
 when not matched then -- THIS CASE CANNOT HAPPEN!!!
    insert ( row_number, someid )
    values ( e.row_number, e.column_value )
SQL> /

1 row merged.

select * from etl_test
/

ROW_NUMBER     SOMEID
---------- ----------
         1          1
 

RE: Merge statment and inserting null

Duke Ganote, July 05, 2006 - 3:49 pm UTC

How can I resolve it?

Ashish, October 17, 2006 - 10:46 am UTC

Dear Tom,
Don't know whether I should give any more information. Can you tell me why this is happening and how can I resolve it.

SELECT *
FROM LOAN P INNER JOIN LOAN_RENEWAL PR ON P.PIN = PR.PIN
INNER JOIN LOAN_SUBSCRIPTION PSB ON P.PIN = PSB.PIN
INNER JOIN LOANMAS PD ON P.PIN = PD.PIN
INNER JOIN LOAN_SITE PS ON P.PIN = PS.PIN
INNER JOIN LOAN_CATALOGUE PC1 ON PC1.SBN = P.PIN
LEFT JOIN LOAN TF ON P.TRANSFER_FROM_PIN = TF.PIN
--LEFT JOIN LOAN TT ON P.TRANSFER_TO_PIN = TT.PIN
WHERE P.PIN = '6000198361' AND P.STATUS = 'X'

ERROR at line 2:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

I understood the concept of key-preserved but how do I resolve it such is my requirement.

Don't know if it sounds vague :)

Thanks



Tom Kyte
October 17, 2006 - 10:50 am UTC

too vague, i cannot reproduce as I do not have any of YOUR tables.

you should reduce the query to the BARE MINIMUM you need to reproduce (removing all non-essential tables) and give full test cases.

Need some more clarity

Maniappan, November 21, 2006 - 1:52 am UTC

Hi Tom,

Con you please confirm whether my following understanding is correct.

Though a joined view returns only one row from the table (to which update is done), the meta data should confirm that it will return just one per resultset. If meta data is insufficient or not guaranteeing that, then update will fail with Key preserved table error.


Thanks
Mani

Tom Kyte
November 22, 2006 - 3:17 pm UTC

when you have a join view, Oracle needs to understand and have sufficient constraints in place, to be able to update a given record.

you need the metadata (constraints).

Key-preserved problem

Tracy, March 17, 2007 - 6:39 am UTC

I am having problems with the non key-preserved table error.

My sql is this:

UPDATE
(SELECT TD.AMOUNT AMOUNT
,TD.NETAMOUNT NETAMOUNT
,US.PercentShare PercentShare
FROM users.TData TD
,users.userSystems US
WHERE TD.userID = US.userID
AND US.systemtype = 'Trade' )
SET AMOUNT = NETAMOUNT * (PercentShare/100)

The Primary key is on usersystem.userId||usersystems.systemtype so we are guaranteed to return one row at most from the usersystems table.

I have also tried:

WHERE TD.userID ||'Trade' = US.userID||US.systemtype

but get same error. I cannot put a unique constraint on userid alone so how can I avoid the ORA-01779 but retain the same basic sql construct?
Tom Kyte
March 17, 2007 - 5:19 pm UTC

yeah, unfortunately, that won't work - we'll have to use MERGE

merge into tdata
using (select * from userSystems where systemtype = 'Trade' ) us
on ( tdate.userid= us.userid)
when matched then update set amount = ......
/


Help!

Rajesh, March 24, 2007 - 12:00 am UTC

Hello Tom,
I have a problem with this update.
I cannot create any more keys in tables.

SQL> create table prc_temp(cntry varchar2(20) not null,trans_date date,product varchar2(30),
2 old_sales_value number,new_sales_value number)
3 /

Table created.


SQL> create table prd_stg(cntry varchar2(20) not null,trans_date date not null,
2 product varchar2(30),sales_value number)
3 /

Table created.

SQL> insert into prd_stg values('UK',sysdate,'Prod1',100)
2 /

1 row created.

SQL> insert into prd_stg values('UK',sysdate+1,'Prod2',200)
2 /

1 row created.

SQL> insert into prd_stg values('UK',sysdate+2,'Prod3',300)
2 /

1 row created.

SQL> commit
2 /

Commit complete.

SQL> select * from prd_stg
2 /

CNTRY TRANS_DAT PRODUCT SALES_VALUE
-------------------- --------- ------------------------------ -----------
UK 24-MAR-07 Prod1 100
UK 25-MAR-07 Prod2 200
UK 26-MAR-07 Prod3 300

3 rows selected.

SQL> insert into prc_temp values('UK',sysdate,'Prod1',100,150)
2 /

1 row created.

SQL> insert into prc_temp values('UK',sysdate+1,'Prod2',200,250)
2 /

1 row created.

SQL> insert into prc_temp values('UK',sysdate+2,'Prod3',300,350)
2 /

1 row created.

SQL> commit
2 /

Commit complete.

SQL> select * from prc_temp
2 /

CNTRY TRANS_DAT PRODUCT OLD_SALES_VALUE NEW_SALES_VALUE
-------------------- --------- ------------------------------ --------------- ---------------
UK 24-MAR-07 Prod1 100 150
UK 25-MAR-07 Prod2 200 250
UK 26-MAR-07 Prod3 300 350

3 rows selected.

SQL> update ( select sales_value,new_sales_value from prd_stg stg,prc_temp prc
2 where stg.cntry = prc.cntry and stg.trans_date = prc.trans_date
3 and stg.product = prc.product and sales_value = old_sales_value)
4* set sales_value = new_sales_value
SQL> /
set sales_value = new_sales_value
*
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

How to make this update work?
Thanks Tom.
Tom Kyte
March 26, 2007 - 7:15 am UTC

I did not load any rows, that is why 0 rows updated, i just added the unique constraint that is necessary for this to work

ops$tkyte%ORA10GR2> update ( select sales_value,new_sales_value from prd_stg stg,prc_temp prc
  2       where stg.cntry = prc.cntry and stg.trans_date = prc.trans_date
  3       and stg.product = prc.product and sales_value = old_sales_value)
  4   set sales_value = new_sales_value
  5  /
 set sales_value = new_sales_value
     *
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table


ops$tkyte%ORA10GR2> alter table prc_temp add constraint prc_temp_unique unique(cntry,trans_date,product,old_sales_value)
  2  /

Table altered.

ops$tkyte%ORA10GR2> update ( select sales_value,new_sales_value from prd_stg stg,prc_temp prc
  2       where stg.cntry = prc.cntry and stg.trans_date = prc.trans_date
  3       and stg.product = prc.product and sales_value = old_sales_value)
  4   set sales_value = new_sales_value
  5  /

0 rows updated.



Not sure Why I am getting this error

Vikas, April 09, 2007 - 12:12 pm UTC

Hi Tom,

The table IMP_CLK_INVREP_DENORM has a UNIQUE CONSTRAINT UQ_IMP_CLK_INVREP_DENORM on (Impression_tag, Click_tag).We have not created it to be PK since we expect the CLICKS to be NULL sometimes.

The inline view also outputs the combination of Impresion_tag, Click_tag to be unique and the join ensures that.

Update (Select /*+ ORDERED Parallel(m 16) Parallel(n 16) USE_NL(m) USE_NL(n) INDEX(UQ_IMP_CLK_INVREP_DENORM) */
m.invalid_click_report_timestamp m_invalid_click_report_ts, n.invalid_click_report_timestamp n_invalid_click_report_ts,
m.invalid_click_filter_id m_invalid_click_filter_id,n.invalid_log_filter_id n_invalid_log_filter_id,
m.invalid_clk_data_score m_invalid_clk_data_score,n.invalid_data_score n_invalid_data_score,
m.updated_date m_updated_date,
m.inv_clk_upd_flag m_inv_clk_upd_flag
FROM
(SELECT /*+ ORDERED parallel(a) parallel(b) parallel(c) */
a.impression_tag,
a.click_tag,
FROM_TZ(b.invalid_click_report_timestamp,'UTC') AT TIME ZONE 'America/Los_angeles' AS invalid_click_report_timestamp,
c.invalid_log_filter_id AS invalid_log_filter_id,
c.invalid_data_score AS invalid_data_score
FROM
click_data@admw.amazon a,
invalid_click_report_data@admw.amazon b,
invalid_click_suspect_data@admw.amazon c
WHERE
a.insert_date > to_date('09/04/2007','dd/mm/yyyy') -1 and a.insert_date < to_date('09/04/2007') AND
b.insert_date > to_date('09/04/2007','dd/mm/yyyy') -1 and b.insert_date < to_date('09/04/2007') AND
c.insert_date > to_date('09/04/2007','dd/mm/yyyy') -1 and c.insert_date < to_date('09/04/2007') AND
a.click_tag = b.click_tag and
b.click_tag = c.click_tag AND b.update_request_id = c.update_request_id
) n, imp_clk_invrep_denorm m
WHERE
m.invalid_click_report_timestamp IS NULL AND
m.click_tag is NOT NULL AND
m.impression_tag=n.impression_tag AND m.click_tag=n.click_tag)
SET
m_invalid_click_report_ts = n_invalid_click_report_ts,
m_invalid_click_filter_id = n_invalid_log_filter_id,
m_invalid_clk_data_score = n_invalid_data_score,
m_updated_date = sysdate,
m_inv_clk_upd_flag = 1
/
ERROR at line 30:
ORA-01779: cannot modify a column which maps to a non key-preserved table

I checked to find that all the columns of the table are updateable,

Elapsed: 00:00:00.01
dwuser@WADM>Select column_name,UPDATABLE,
insertable,deletable
from user_updatable_columns
where table_name = 'IMP_CLK_INVREP_DENORM'
2 3 4 5 /

COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
REQUEST_TAG YES YES YES
IMPRESSION_TAG YES YES YES
CLICK_TAG YES YES YES
IMPRESSION_TIMESTAMP YES YES YES
CLICK_TIMESTAMP YES YES YES
INVALID_CLICK_REPORT_TIMESTAMP YES YES YES
INVALID_IMP_REPORT_TIMESTAMP YES YES YES
ACCOUNT_ID YES YES YES
AD_CAMPAIGN_ID YES YES YES
AD_ID YES YES YES
AD_CREATIVE_ID YES YES YES
AD_MATCH_ID YES YES YES
AD_MATCH_BID_ID YES YES YES
AD_POSITION YES YES YES
AD_COUNT YES YES YES
AD_COST YES YES YES
IMP_RANK YES YES YES
PUBLISHER_ID YES YES YES
TREATMENT_ID YES YES YES
CHANNEL_ID YES YES YES
REQUEST_COUNT YES YES YES
REPORT_DATE YES YES YES
CREATED_DATE YES YES YES
UPDATED_DATE YES YES YES
INVALID_CLICK_FILTER_ID YES YES YES
INVALID_IMP_FILTER_ID YES YES YES
INV_IMP_UPD_FLAG YES YES YES
INV_CLK_UPD_FLAG YES YES YES
IMP_UPD_FLAG YES YES YES
CLK_UPD_FLAG YES YES YES
DUP_IMP_FLAG YES YES YES
INVALID_IMP_DATA_SCORE YES YES YES
INVALID_CLK_DATA_SCORE YES YES YES

33 rows selected.

Then why I am geting this error. Please do help me!

Tom Kyte
April 09, 2007 - 1:13 pm UTC

ERROR at line 30:
ORA-01779: cannot modify a column which maps to a non key-preserved table


you are missing a primary key/unique constraint on the source table (not the table being updated, the other one)

You may have to use MERGE here instead of update.

this has nothing do with with updatable columns, everything to do with key preserved tables. the update wants to make sure the table being modified has AT MOST one possible match in the source table.


ps$tkyte%ORA10GR2> create table t1 ( x int, t1y int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( x int, t2y int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update (select t1y, t2y from t1, t2 where t1.x = t2.x) set t1y = t2y;
update (select t1y, t2y from t1, t2 where t1.x = t2.x) set t1y = t2y
                                                           *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


ops$tkyte%ORA10GR2> merge into t1 using t2 on (t1.x = t2.x) when matched then update set t1y = t2y;

0 rows merged.

ops$tkyte%ORA10GR2> alter table t2 add constraint t2_unique unique(x);

Table altered.

ops$tkyte%ORA10GR2> update (select t1y, t2y from t1, t2 where t1.x = t2.x) set t1y = t2y;

0 rows updated.



Source is an inline View

Vikas, April 09, 2007 - 2:00 pm UTC

Hi Tom,

How to constraint the inline view, since n is an inline view build which is the source table.

I tried the merge also but it throws an error:

ORA 2064
02064, 00000, "distributed operation not supported"

MERGE INTO imp_clk_invrep_denorm m
USING
(SELECT /*+ ORDERED parallel(b) parallel(d) */
a.impression_tag,b.click_tag,
FROM_TZ(b.invalid_click_report_timestamp,'UTC')
AT TIME ZONE 'America/Los_angeles' AS invalid_click_report_timestamp,
c.invalid_log_filter_id,
c.invalid_data_score
FROM
imp_clk_invrep_denorm a,
invalid_click_report_data@admw.amazon b,
invalid_click_suspect_data@admw.amazon c
WHERE
a.invalid_click_report_timestamp IS NULL
AND a.click_tag is NOT NULL
AND a.click_tag = b.click_tag
AND b.click_tag = c.click_tag
AND b.update_request_id = c.update_request_id
) n
ON (m.click_tag=n.click_tag AND m.impression_tag=n.impression_tag)
WHEN MATCHED THEN
UPDATE SET
m.invalid_click_report_timestamp=n.invalid_click_report_timestamp,
m.invalid_click_filter_id=n.invalid_log_filter_id,
m.invalid_clk_data_score=n.invalid_data_score,
m.updated_date=sysdate,
m.inv_clk_upd_flag=1;

This was the reason I shifted to UPDATE clause.

Plesse help and guide.




Tom Kyte
April 09, 2007 - 2:16 pm UTC

you won't constrain an inline view.

the source tables themselves would all need primary keys - the entire "chain" of them that are ultimately joined to the target table.

although you don't mention the version, you might contact support and see if bug 4311273 has anything to do with the merge issue.

Here are the tables forming an inline view

Vikas, April 09, 2007 - 2:22 pm UTC

Hi Tom,

Here are the tables forming an inline clause:

click_data@admw.amazon a,
PK-> CLICK_TAG
invalid_click_report_data@admw.amazon b,
Partitioned Table by Range Partitioning
PK-> Local PK Index : CONSTRAINT "PK_ICRD" PRIMARY KEY ("INVALID_CLICK_REPORT_TIMESTAMP", "CLICK_TAG
invalid_click_suspect_data@admw.amazon c
CONSTRAINT "PK_ICSD" PRIMARY KEY ("INVALID_DATA_UPDATE_TIMESTAMP", "CLICK_TAG"
, "UPDATE_REQUEST_ID")

So as per their PK definitions that update should succeed, or they have to be necessarily the exact keys.

Can I globally create the UNIQUE Keys to solve the problem.
Tom Kyte
April 09, 2007 - 2:49 pm UTC

(m.click_tag=n.click_tag AND m.impression_tag=n.impression_tag)

that is what the keys would have to be on. the JOIN conditions - not just some arbitrary set of columns....


n - Inline View

Vikas, April 09, 2007 - 3:15 pm UTC

But n represents an inline view:

(SELECT /*+ ORDERED parallel(a) parallel(b) parallel(c) */
a.impression_tag,
a.click_tag,
FROM_TZ(b.invalid_click_report_timestamp,'UTC') AT TIME ZONE 'America/Los_angeles' AS invalid_click_report_timestamp,
c.invalid_log_filter_id AS invalid_log_filter_id,
c.invalid_data_score AS invalid_data_score
FROM
click_data@admw.amazon a,
invalid_click_report_data@admw.amazon b,
invalid_click_suspect_data@admw.amazon c
WHERE
a.insert_date > to_date('09/04/2007','dd/mm/yyyy') -1 and a.insert_date < to_date('09/04/2007') AND
b.insert_date > to_date('09/04/2007','dd/mm/yyyy') -1 and b.insert_date < to_date('09/04/2007') AND
c.insert_date > to_date('09/04/2007','dd/mm/yyyy') -1 and c.insert_date < to_date('09/04/2007') AND
a.click_tag = b.click_tag and
b.click_tag = c.click_tag AND b.update_request_id = c.update_request_id
) n
Tom Kyte
April 09, 2007 - 4:50 pm UTC

right, but inline view inline schmew -- we rewrite all that to be one big thing.

They need keys all of the way down the line... On what we join to. As if "N" did not exist (because it really doesn't - semantically speaking, the optimizer still understands what is joined to what)



ops$tkyte%ORA10GR2> create table t1 ( x int , t1y int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( x int , t2y int );

Table created.

ops$tkyte%ORA10GR2> create table t3 ( x int , t3y int );

Table created.

ops$tkyte%ORA10GR2> create table t4 ( x int , y1 int, y2 int, y3 int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update (select t1y, t2y, t3y, y1, y2, y3
  2            from (select t1.x, t1y, t2y, t3y from t1, t2, t3 where t1.x = t2.x and t2.x = t3.x) n,
  3                         t4
  4                   where n.x = t4.x )
  5     set y1 = t1y, y2 = t2y, y3 = t3y
  6  /
   set y1 = t1y, y2 = t2y, y3 = t3y
       *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table


ops$tkyte%ORA10GR2> alter table t1 add constraint t1pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2> alter table t2 add constraint t2pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2> alter table t3 add constraint t3pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2> update (select t1y, t2y, t3y, y1, y2, y3
  2            from (select t1.x, t1y, t2y, t3y from t1, t2, t3 where t1.x = t2.x and t2.x = t3.x) n,
  3                         t4
  4                   where n.x = t4.x )
  5     set y1 = t1y, y2 = t2y, y3 = t3y
  6  /

0 rows updated.



We have to be able to figure out that when you join N to T4 (in my example), that the rows in T4 will join to AT MOST one row in N.

Gotta have the keys on the join conditions - else, it just will not be permitted.

Vikas, April 10, 2007 - 8:13 am UTC

Thanks Tom!

Appreciate your replies, it gave me an insight about the error faced.


Tom Kyte
April 10, 2007 - 10:58 am UTC

umm, i did??

I told you why you get the key preserved thing, with a nice example right above.

I gave you a possible bug # regarding merge.

The source of ROWID

Martin, May 29, 2007 - 3:50 am UTC

Hi Tom,

quick question for you. If you've got a key-preserved join view which has columns from more than one table which can be updatable etc, then how does Oracle choose which table to select the rowid from?

SQL> CREATE TABLE t_parent ( a varchar2(10) primary key );

Table created.

SQL> CREATE TABLE t_child ( b varchar2(10) primary key, a varchar2(10),
  2                         constraint a_fk foreign key (a) references t_parent(a) );

Table created.

SQL> CREATE TABLE t_child2 ( b VARCHAR2(10) PRIMARY KEY );

Table created.

SQL> CREATE OR REPLACE VIEW t_view
  2  (
  3    parent_a,
  4    child_b,
  5    child2_b
  6  )
  7  AS
  8  SELECT t_parent.a, t_child.b, t_child2.b
  9    FROM t_parent, t_child, t_child2
 10   WHERE t_parent.a = t_child.a
 11     AND t_child.b  = t_child2.b
 12  /

View created.

SQL> SELECT column_name, updatable, insertable, deletable
  2    FROM dba_updatable_columns
  3   WHERE table_name = 'T_VIEW';

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
PARENT_A                       NO  NO  NO
CHILD_B                        YES YES YES
CHILD2_B                       YES YES YES

3 rows selected.

SQL> INSERT INTO t_parent values ( 'X' );

1 row created.

SQL> insert into t_child values ( 'Y', 'X' );

1 row created.

SQL> insert into t_child2 values ( 'Y' );

1 row created.

SQL> SELECT rowid FROM t_view;

ROWID
------------------
AAAo+KAADAACWYKAAA

1 row selected.

SQL> SELECT ROWID FROM t_parent;

ROWID
------------------
AAAo+FAADAACWfqAAA

1 row selected.

SQL> SELECT rowid FROM t_child;

ROWID
------------------
AAAo+HAADAACWf6AAA

1 row selected.

SQL> SELECT rowid FROM t_child2;

ROWID
------------------
AAAo+KAADAACWYKAAA

1 row selected.

In the above example, it comes from t_child2, but why not t_child, or even why can it select rowid at all considering there is an ambiguity?

Thanks in advance
Tom Kyte
May 30, 2007 - 10:30 am UTC

I believe it is the last table in the from clause when there are ambiguities.

like implicit conversions - being explicit is best - if you need a specific rowid, you would want to pull it in the select list.

ORA-01732

A reader, February 25, 2008 - 11:00 am UTC

I am getting the following error while updating view.
It is working on one database and not on other !

SQL> UPDATE ida.vr_instance_status_curr set
  2  UPD_TS_D = sysdate
  3   WHERE TRIM(meas_d) = TO_DATE('06/30/2007', 'mm/dd/yyyy')
  4   AND feed_type_c = 'CHAR'
  5   AND vnd_c = 'FACTSET'
  6   AND instn_id_n = 139001
  7   AND stat_lvl_c = 'T'
  8   AND stat_c = 'REJECT" : "FAIL' AND cntxt_c = 'QIMD'
  9  /
UPDATE ida.vr_instance_status_curr set
           *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL>

Tom Kyte
February 25, 2008 - 1:55 pm UTC

view is different
or
versions are different and one version is accidentally letting you do it.
or
set of constraints is different on base tables

but you don't give us anything to work with. so we can only guess

A reader, February 26, 2008 - 6:47 am UTC

Below is the view body, I checked all constraints looks same for me

SELECT "INSTN_ID_N", "MEAS_D", "FEED_TYPE_C", "FREQ_C", "STAT_LVL_C",
"TS_D", "IX_INSTN_ID_C", "ENTI_TYPE_C", "CNTXT_ID_C", "CNTXT_C",
"IX_CNTXT_ID_C", "VND_C", "PRCS_C", "USR_ID_C", "STAT_C",
"OVRD_RSN_C", "LD_DT_D", "UPD_USR_C", "UPD_TS_D", "INS_USR_C",
"INS_TS_D", "DEL_FLG_C"
FROM ida.vr_instance_status m
WHERE ts_d =
(SELECT MAX (ts_d)
FROM ida.vr_instance_status
WHERE instn_id_n = m.instn_id_n
AND meas_d = m.meas_d
AND feed_type_c = m.feed_type_c
AND freq_c = m.freq_c
AND stat_lvl_c = m.stat_lvl_c
AND ix_instn_id_c = m.ix_instn_id_c)
Tom Kyte
February 26, 2008 - 8:04 am UTC

post the plans for select * from view for both systems, that'll probably be it.

A reader, February 26, 2008 - 10:01 am UTC

---------------------------------------------------------------------------------------------------
| Id  | Operation                             |  Name                | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |     1 |   199 |  1598   (3)|
|*  1 |  HASH JOIN                            |                      |     1 |   199 |  1598   (3)|
|   2 |   NESTED LOOPS                        |                      |     5 |   930 |  1566   (3)|
|*  3 |    HASH JOIN                          |                      |     5 |   895 |  1556   (3)|
|   4 |     VIEW                              | VW_SQ_1              |   403 | 25389 |            |
|   5 |      SORT GROUP BY                    |                      |   403 | 23777 |   947   (3)|
|*  6 |       HASH JOIN                       |                      |   403 | 23777 |   946   (3)|
|   7 |        VIEW                           | V_AUTHORIZATION      |   400 |  5200 |            |
|   8 |         SORT UNIQUE                   |                      |   400 | 17540 |    33  (25)|
|   9 |          UNION-ALL                    |                      |       |       |            |
|  10 |           NESTED LOOPS                |                      |   394 | 17336 |    26   (4)|
|  11 |            TABLE ACCESS BY INDEX ROWID| USR_APPL_SESN        |     1 |    24 |     4  (25)|
|* 12 |             INDEX UNIQUE SCAN         | PK_USR_APPL_SESN     |     1 |       |     3  (34)|
|* 13 |            INDEX RANGE SCAN           | IDX_AUTHORIZATION_1  |   394 |  7880 |    23   (5)|
|  14 |           NESTED LOOPS                |                      |     6 |   204 |     5  (20)|
|* 15 |            TABLE ACCESS BY INDEX ROWID| USR_APPL_SESN        |     1 |    24 |     4  (25)|
|* 16 |             INDEX UNIQUE SCAN         | PK_USR_APPL_SESN     |     1 |       |     3  (34)|
|* 17 |            INDEX FULL SCAN            | PK_INTRADAY_AUTH     |     6 |    60 |     2  (50)|
|* 18 |        HASH JOIN                      |                      | 82022 |  3684K|   913   (3)|
|* 19 |         TABLE ACCESS FULL             | INSTANCE             | 81206 |   555K|   302   (3)|
|* 20 |         TABLE ACCESS FULL             | INSTANCE_STATUS      | 82022 |  3123K|   608   (3)|
|* 21 |     TABLE ACCESS FULL                 | INSTANCE_STATUS      | 82022 |  9291K|   608   (3)|
|* 22 |    TABLE ACCESS BY INDEX ROWID        | INSTANCE             |     1 |     7 |     3  (34)|
|* 23 |     INDEX UNIQUE SCAN                 | PK_INSTANCE          |     1 |       |     2  (50)|
|  24 |   VIEW                                | V_AUTHORIZATION      |   400 |  5200 |            |
|  25 |    SORT UNIQUE                        |                      |   400 | 17540 |    33  (25)|
|  26 |     UNION-ALL                         |                      |       |       |            |
|  27 |      NESTED LOOPS                     |                      |   394 | 17336 |    26   (4)|
|  28 |       TABLE ACCESS BY INDEX ROWID     | USR_APPL_SESN        |     1 |    24 |     4  (25)|
|* 29 |        INDEX UNIQUE SCAN              | PK_USR_APPL_SESN     |     1 |       |     3  (34)|
|* 30 |       INDEX RANGE SCAN                | IDX_AUTHORIZATION_1  |   394 |  7880 |    23   (5)|
|  31 |      NESTED LOOPS                     |                      |     6 |   204 |     5  (20)|
|* 32 |       TABLE ACCESS BY INDEX ROWID     | USR_APPL_SESN        |     1 |    24 |     4  (25)|
|* 33 |        INDEX UNIQUE SCAN              | PK_USR_APPL_SESN     |     1 |       |     3  (34)|
|* 34 |       INDEX FULL SCAN                 | PK_INTRADAY_AUTH     |     6 |    60 |     2  (50)|
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."INSTN_ID_N"="B"."INSTN_ID_N")
   3 - access("INSTANCE_STATUS"."TS_D"="VW_SQ_1"."VW_COL_1" AND
              "VW_SQ_1"."INSTN_ID_N"="INSTANCE_STATUS"."INSTN_ID_N" AND
              "VW_SQ_1"."MEAS_D"="INSTANCE_STATUS"."MEAS_D" AND
              "VW_SQ_1"."FEED_TYPE_C"="INSTANCE_STATUS"."FEED_TYPE_C" AND
              "VW_SQ_1"."FREQ_C"="INSTANCE_STATUS"."FREQ_C" AND
              "VW_SQ_1"."STAT_LVL_C"="INSTANCE_STATUS"."STAT_LVL_C" AND
              "VW_SQ_1"."IX_INSTN_ID_C"="INSTANCE_STATUS"."IX_INSTN_ID_C")
   6 - access("A"."INSTN_ID_N"="B"."INSTN_ID_N")
  12 - access("USR_APPL_SESN"."APPL_ID_C"=TO_CHAR(:B1))
  13 - access("AUTHORIZATION"."AUTH_USR_C"="USR_APPL_SESN"."USR_ID_C" AND
              "AUTHORIZATION"."UNIV_C"="USR_APPL_SESN"."UNIV_C" AND "AUTHORIZATION"."AUTH_STAT_C"='A')
       filter("AUTHORIZATION"."AUTH_STAT_C"='A')
  15 - filter("A"."UNIV_C"='CAPE')
  16 - access("A"."APPL_ID_C"=TO_CHAR(:B1))
  17 - access("A"."UNIV_C"="B"."UNIV_C")
       filter("A"."UNIV_C"="B"."UNIV_C")
  18 - access("INSTANCE_STATUS"."INSTN_ID_N"="A"."INSTN_ID_N")
  19 - filter("A"."DEL_FLG_C" IS NULL)
  20 - filter("INSTANCE_STATUS"."DEL_FLG_C" IS NULL)
  21 - filter("INSTANCE_STATUS"."DEL_FLG_C" IS NULL)
  22 - filter("A"."DEL_FLG_C" IS NULL)
  23 - access("INSTANCE_STATUS"."INSTN_ID_N"="A"."INSTN_ID_N")
  29 - access("USR_APPL_SESN"."APPL_ID_C"=TO_CHAR(:B1))
  30 - access("AUTHORIZATION"."AUTH_USR_C"="USR_APPL_SESN"."USR_ID_C" AND
              "AUTHORIZATION"."UNIV_C"="USR_APPL_SESN"."UNIV_C" AND "AUTHORIZATION"."AUTH_STAT_C"='A')
       filter("AUTHORIZATION"."AUTH_STAT_C"='A')
  32 - filter("A"."UNIV_C"='CAPE')
  33 - access("A"."APPL_ID_C"=TO_CHAR(:B1))
  34 - access("A"."UNIV_C"="B"."UNIV_C")
       filter("A"."UNIV_C"="B"."UNIV_C")

75 rows selected.

SQL> SQL>

A reader, February 26, 2008 - 10:02 am UTC

----------------------------------------------------------------------------------------------
| Id  | Operation                             |  Name                | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    20 |  3940 |   491 |
|   1 |  HASH JOIN                            |                      |    20 |  3940 |   491 |
|   2 |   VIEW                                | V_AUTHORIZATION      |   451 |  5863 |    24 |
|   3 |    SORT UNIQUE                        |                      |   451 | 19844 |    24 |
|   4 |     UNION-ALL                         |                      |       |       |       |
|   5 |      NESTED LOOPS                     |                      |   450 | 19800 |     9 |
|   6 |       TABLE ACCESS BY INDEX ROWID     | USR_APPL_SESN        |     1 |    23 |     2 |
|   7 |        INDEX UNIQUE SCAN              | PK_USR_APPL_SESN     |     1 |       |     1 |
|   8 |       INDEX RANGE SCAN                | IDX_AUTHORIZATION_1  |   450 |  9450 |     7 |
|   9 |      NESTED LOOPS                     |                      |     1 |    44 |     2 |
|  10 |       TABLE ACCESS BY INDEX ROWID     | USR_APPL_SESN        |     1 |    23 |     2 |
|  11 |        INDEX UNIQUE SCAN              | PK_USR_APPL_SESN     |     1 |       |     1 |
|  12 |       INDEX FULL SCAN                 | PK_INTRADAY_AUTH     |     1 |    21 |       |
|  13 |   HASH JOIN                           |                      |   536 | 98624 |   467 |
|  14 |    HASH JOIN                          |                      |   536 | 94872 |   452 |
|  15 |     VIEW                              | VW_SQ_1              |  2088 |   128K|   297 |
|  16 |      SORT GROUP BY                    |                      |  2088 |   120K|   297 |
|  17 |       HASH JOIN                       |                      | 27787 |  1601K|   148 |
|  18 |        VIEW                           | V_AUTHORIZATION      |   451 |  5863 |    24 |
|  19 |         SORT UNIQUE                   |                      |   451 | 19844 |    24 |
|  20 |          UNION-ALL                    |                      |       |       |       |
|  21 |           NESTED LOOPS                |                      |   450 | 19800 |     9 |
|  22 |            TABLE ACCESS BY INDEX ROWID| USR_APPL_SESN        |     1 |    23 |     2 |
|  23 |             INDEX UNIQUE SCAN         | PK_USR_APPL_SESN     |     1 |       |     1 |
|  24 |            INDEX RANGE SCAN           | IDX_AUTHORIZATION_1  |   450 |  9450 |     7 |
|  25 |           NESTED LOOPS                |                      |     1 |    44 |     2 |
|  26 |            TABLE ACCESS BY INDEX ROWID| USR_APPL_SESN        |     1 |    23 |     2 |
|  27 |             INDEX UNIQUE SCAN         | PK_USR_APPL_SESN     |     1 |       |     1 |
|  28 |            INDEX FULL SCAN            | PK_INTRADAY_AUTH     |     1 |    21 |       |
|  29 |        HASH JOIN                      |                      |   754K|    33M|   119 |
|  30 |         TABLE ACCESS FULL             | INSTANCE             | 11963 | 83741 |    13 |
|  31 |         TABLE ACCESS FULL             | INSTANCE_STATUS      |   754K|    28M|    40 |
|  32 |     TABLE ACCESS FULL                 | INSTANCE_STATUS      |   754K|    81M|    40 |
|  33 |    TABLE ACCESS FULL                  | INSTANCE             | 11963 | 83741 |    13 |
----------------------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

41 rows selected.

SQL> SQL> SQL>

Tom Kyte
February 26, 2008 - 3:05 pm UTC

hah, a view of a view, of maybe more views.

Yeah, not going to touch that one. Sorry. Looks like different releases too.

definitely different plans - and one is updatable and the other not.

updating this "view of view of whatever" seems a risky thing at best. Approach this differently.

A reader, February 28, 2008 - 5:50 am UTC

Thanks, we used INSTEAD of trigger

It worked actully one envronment it was missing

kepreserved error

Biswaranjan Behera, August 01, 2008 - 12:35 am UTC

Hi tom ,
i have created two tables as below.
create table parent(a numer(2),b number(2));
create table child(a number(2),c number(2));
then i created the view as
create or replace view parent_child as select p.a pa,p.b pb,c.a ca,c.c cc from parent p,child c where p.a=c.a;

select * from parent_child;
PA PB CA CC
1 2 1 8
1 2 1 10
2 4 2 7
1 2 1 9


so here child is kepreserved table , but why i am not able to insert into it
insert into parent_child(CA,CC)values(1,7);
error-canot modify a column which maps to a non key........

Please suggest to resolve this problem.

waiting for u reply
thanks
Biswaranjan

Tom Kyte
August 03, 2008 - 1:39 pm UTC

there are no primary keys, therefore nothing can be key preserved here. You have to have a primary key in order to say something is key preserved, you have not met the basic requirements.


ops$tkyte%ORA10GR2> create table parent(a number(2) <b>PRIMARY KEY</b>,b number(2));

Table created.

ops$tkyte%ORA10GR2> create table child(a number(2) <b>REFERENCES PARENT</b>,c number(2));

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view parent_child
  2  as
  3  select p.a pa,p.b pb,c.a ca,c.c cc
  4    from parent p,child c
  5   where p.a=c.a;

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into parent (a,b) values ( 1, 1 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into parent_child(ca,cc) values ( 1, 1 );

1 row created.


re:key preserved error

Biswaranjan, August 01, 2008 - 7:01 am UTC

Hi tom ,
i have created two tables as below.
create table parent(a numer(2),b number(2));
create table child(a number(2),c number(2));
then i created the view as
create or replace view parent_child as select p.a pa,p.b pb,c.a ca,c.c cc from parent p,child c
where p.a=c.a;
select * from parent;
A B
1 2
2 4
select * from child;
A C
1 8
1 9
1 10
2 7

select * from parent_child;
PA PB CA CC
1 2 1 8
1 2 1 10
2 4 2 7
1 2 1 9


so here child is kepreserved table , but why i am not able to insert into it
insert into parent_child(CA,CC)values(1,7);
error-canot modify a column which maps to a non key........

Please suggest to resolve this problem.

waiting for u reply
thanks
Biswaranjan


RE:KEYPRESERVED ERROR

BISWARANJAN BEHERA, August 03, 2008 - 6:45 pm UTC

THANK U FOR UR REPLY TOM.
i have another doubt regarding this key preserved.
  1* CREATE VIEW PARENT_CHILD4 AS SELECT P.A PA,P.B PB,C.A CA,C.C CC FROM PARENT P,CHILD C WHERE P.B
SQL> /

View created.
i have created the above tables(parent and cghild)with the primary key and the foreign key.
Now all work fine.
but when i am creating a view like below
SQL>CREATE VIEW PARENT_CHILD4 AS SELECT P.A PA,P.B PB,C.A CA,C.C CC FROM PARENT P,CHILD C WHERE P.B=2;

view created

SQL> SELECT * FROM PARENT_CHILD4;

        PA         PB         CA         CC
---------- ---------- ---------- ----------
         1          2          1          8
         1          2          1          9
         1          2          1         10
         1          2          2          7
         
SQL> INSERT INTO PARENT_CHILD4(CA,CC) VALUES(1,6);
INSERT INTO PARENT_CHILD4(CA,CC) VALUES(1,6)
                          *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

nOW WHY I AM FACING THIS PROBLEM,CAN YOU PLEASE SUGGEST HOW TO RESOLVE THIS.

Tom Kyte
August 04, 2008 - 12:59 pm UTC

"U" didn't reply. Since you mistook me for them, I'll just stop reading - it is not intended for me apparently.



(the error is because the view is NOT key preserved. you have no join condition from P to C - isn't that fairly obvious?)

re:key preserved table

Biswaranjan, August 04, 2008 - 9:55 pm UTC

Hi Tom,
I did not mistake you.please excuse me if i hurt you.Actually i did not aware of the basic requirement for the table before the cosideration of key preserved.now i got from your answer that a join condition should be required for this.Thanks for your valuable answer.
Tom Kyte
August 05, 2008 - 9:11 am UTC

You have to think of what it means to be "key preserved"

for a table to be "key preserved" we have to know that any given row will appear AT MOST ONCE in the result set.

so, for T2 to be key preserved in the query:


select *
from t1, t2
where t1.x = t2.y;

we would need to know that T1.X was UNIQUE (a primary key or a unique key). If you do not join t2 to a UNIQUE key, then rows from T2 could appear more than once in the result set - making T2 "not key preserved"

so, if you look at:

SELECT P.A PA,P.B PB,C.A CA,C.C CC FROM PARENT P,CHILD C WHERE
P.B=2;



You know for a FACT that C cannot be key preserved, that the rows in C appear multiple times in the result set over and over - since P.B is NOT unique, "where p.b = 2" can return many rows (say 2 of them for example), and therefore each row in C would be output 2 times.



re:key pre..

Biswaranjan, August 06, 2008 - 5:04 am UTC

Thank your for your reply.
regards
Biswaranjan

Concept of Key preserved table

Vijay, December 09, 2008 - 11:21 am UTC

Tom,

I have a query regarding Key Preserved(K-P) table.

Take an example of typical EMP and DEPT tables, with their standard structure i.e. there's a primary key on columns "empno" and "deptno" of tables "emp" and "dept" respectively. Further, there's parent-child relationship between emp and dept(on deptno column)with former being the child and later, the parent.

Now, keeping this in perspective, there's a comment on K-P tables as described in one of the Oracle's documents....

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/views.htm#4054

I quote..."The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema. For example, if in the emp table there was at most one employee in each department, then deptno would be unique in the result of a join of emp and dept, but dept would still not be a key-preserved table.
"

This comment is a bit confusing and contradicts your definition as given at the top of this page.

Could you please throw some light on this.

Best Regards,
Vijay
Tom Kyte
December 09, 2008 - 2:24 pm UTC

it does not contradict anything.

I wrote:

... Key preserved means the row from the base table will appear AT MOST ONCE in the output view on that table. ....

In the normal EMP/DEPT relationship when you join them on deptno - knowing that a) empno is unique in EMP, and b) deptno is unique in DEPT we can say:

a) rows in EMP will appear in the result set AT MOST ONCE

and that is it, we cannot make any statements about how many times a DEPT row will appear. Even if you and I know that if in
the emp table there was at most one employee in each department
we can not say a think about how many times a DEPT row will appear in the result set unless and until there is a unique or primary key constraint on EMP(DEPTNO)



What they are saying is

o the data in the table is not relevant when determining key preserved tables
o only constraints are


key preserved tables are tables that join other tables using the other tables PRIMARY or UNIQUE keys.

EMP joined to DEPT by deptno - emp joins to DEPT's primary key, emp is key preserved.

DEPT joined to EMP by deptno - dept joins to EMP's attribute DEPTNO that may or may not be unique - we don't know, all we know is IT DOES NOT HAVE to be unique. Therefore, DEPT is not key preserved in this join.

RE: Concept of Key-preserved table

Duke Ganote, December 09, 2008 - 4:05 pm UTC

Here's how I think of it:

All updates require uniqueness or "key preservation" to be successful. But -- as implemented by Oracle -- the requirement is checked differently by different commands.

"Updating a join" checks database constraints for uniqueness before looking at any data. If Oracle can't guarantee uniqueness beforehand, it throws the exception:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Maybe the data, in practice, is really unique, but that doesn't matter. "Updating a join" takes the 'lazy' approach and says "if there's no guarantee of success, then why try?"

Other forms of updates, like MERGE or UPDATE, check for uniqueness afterward in the result set. For these update statements, a lack of uniqueness results in:
 
ORA-30926: unable to get a stable set of rows in the source tables


So key preservation is always necessary for any SQL statement that does updates, but different SQL statements check for key perservation by different mechanisms.

Crystal clear

Vijay, December 10, 2008 - 5:12 am UTC

Tom,

The concept is now crystal clear.

Wonderful elaboration and explanation, as always.

Thanks a ton,
Vijay

Crystal clear

Vijay, December 10, 2008 - 5:13 am UTC

Tom,

The concept is now crystal clear.

Wonderful elaboration and explanation, as always.

Thanks a ton,
Vijay

Key Preserved table in case of one to one mapping.

Amit, August 27, 2009 - 3:02 am UTC

How do Orcale decide key Preserved Table? In the example mentioned below we are using table EMP and EMP1 both are having EMPNO. According to definition of Key preserved table both EMP and EMP1 shold play a role of key Preserved table but this is not true. Please advise?
Which column should be updated in the following case sal or sal1?

create table emp1
as
select *
from emp;

create or replace view vw_emp
as
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno,
e1.empno empno1, e1.ename ename1, e1.job job1, e1.mgr mgr1, e1.hiredate hiredate1, e1.sal sal1, e1.comm comm1, e1.deptno deptno1
from emp e,
emp1 e1
where e.empno = e1.empno;

update vw_emp e
set e.sal = 1000,
e.sal1 = 2000
where e.empno = 7788;

Tom Kyte
August 28, 2009 - 4:39 pm UTC

... How do Orcale decide key Preserved Table? ...

easy - in a join, a key preserved table is one such that

we know that every row in it will join to at most ONE other row in the other table, the cardinality of the rows in that table will be preserved, its KEY will still be a KEY in the result set. It is KEY preserved.


You have an example such that there are no constraints, we have no way of knowing ANYTHING in that case.


and you can only update a single table regardless.

ops$tkyte%ORA10GR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view v
  2  as
  3  select e1.empno, e1.ename, e2.sal
  4    from emp e1, emp e2
  5   where e1.empno = e2.empno
  6  /

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select COLUMN_NAME, UPDATABLE, INSERTABLE, DELETABLE from user_updatable_columns where table_name = 'V';

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
EMPNO                          NO  NO  NO
ENAME                          NO  NO  NO
SAL                            NO  NO  NO

ops$tkyte%ORA10GR2> alter table emp add constraint emp_pk primary key(empno);

Table altered.

ops$tkyte%ORA10GR2> alter view v compile;

View altered.

ops$tkyte%ORA10GR2> select COLUMN_NAME, UPDATABLE, INSERTABLE, DELETABLE from user_updatable_columns where table_name = 'V';

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
EMPNO                          YES YES YES
ENAME                          YES YES YES
SAL                            YES YES YES

<b>so, with proper constraints - things can be modifiable... but</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into v (empno,ename,sal) values ( 111, 'x', 50 );
insert into v (empno,ename,sal) values ( 111, 'x', 50 )
                           *
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view


ops$tkyte%ORA10GR2> update v set sal = 50, ename = 'x' where empno = 1;
update v set sal = 50, ename = 'x' where empno = 1
                       *
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view


<b>you can only touch a single table - we can insert and we can update V but not in an arbitrary fashion, not without instead of triggers</b>


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into v ( empno, ename ) values ( 111, 'x' );

1 row created.

ops$tkyte%ORA10GR2> update v set sal = 50 where empno = 111;

1 row updated.


Oracle apps custom form

Aiyaz, October 23, 2009 - 5:05 am UTC

Hello TOM,

I am developing a custom form, In which i have selected an oeorder_headers_v view as my data block and selecting few columns in it. but which i compile my form in Oracle applications that block query is taking rowid in it and resulting with query cause no records to be retrived. Kindly help me in this.

I am not selecting rowid in my data block

Thanks
Aiyaz
Tom Kyte
October 23, 2009 - 2:22 pm UTC

I do not use Oracle Apps from a development perspective and I do not use forms at all (not since 1995).. sorry

(and it really doesn't have anything to do with key preserved tables does it?!?!)

Followup: Key Preserved Group By?

A, December 22, 2009 - 3:51 pm UTC

Hi Tom,

This may have been addressed above (I think it may be in the reply that says 'it it unfortunate it does not work...use merge').

Regarding:
Followup August 5, 2008 - 9am Central time zone:
You have to think of what it means to be "key preserved"

for a table to be "key preserved" we have to know that any given row will appear AT MOST ONCE in the result set.


OK A PK or Unique constraint tells the system that it is key preserved but I can also see that a group by clause where the PK is the group by gives us this knowledge.

Example:
-- single key table
create table S ( i number, d number, SVal varchar2(5), 
  CONSTRAINT S_PK  PRIMARY KEY(I,D) 
);

-- a repeating table
create table R ( i number, d number, Rval varchar2(5), 
  CONSTRAINT R_PK  PRIMARY KEY(I,D, RVal) 
);

insert into s (select 1,1,NULL from dual);
insert into s (select 1,2,NULL from dual);
insert into s (select 2,1,NULL from dual);
insert into s (select 2,2,NULL from dual);

insert into r (select 1,1,'A' from dual);
insert into r (select 1,1,'B' from dual);
insert into r (select 1,2,'W' from dual);
insert into r (select 2,1,'A' from dual);

update
( select i, d, sval, rval
  from s 
    inner join (select i,d, max(rval) rval 
                from r
                group by i,d 
                having count(*)=1)
      using(i,d)
)
set sval=rval
where sval is null;

/**********
Result:
 10  set sval=rval;
set sval=rval
    *
ERROR at line 10:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Note removing the having would change result but does not change error and does not that one record is returned.
 **********/


Should--or will--the system be smart enough to know that guarantees one row? (Or is my assumption wrong?)

Is there is a better way to write it to tell the system that it is key preserved?

Tom Kyte
December 31, 2009 - 8:43 am UTC

Use merge instead.

merge into S
using ( select i,d, max(rval) rval
from r
group by i,d
having count(*)=1 ) x
on (s.i = x.i and s.d = x.d )
when matched then update set rval = x.rval;

Updating from aggregated table with change detection

Jo, June 17, 2010 - 9:06 am UTC

I need to update maximum values from a detail table into a master table.

To make things more interesting, I want to avoid the update if the new value is the same as the old.
(There are two reasons for that: (1) Oracle JDBC reports the number of rows touched, not the number of rows that had a value change in one of their fields. I want the latter number in logs. 2) I hope to make Oracle filter unchanged rows more efficiently than a trigger could.)

How would I do that?

Here are the approaches I have tried with 9i and 11g.
(Apologies for posting untested SQL, I have little experience preparing useful test tables and fear I'd muck these up. I'm trying to make up for that by describing what each SQL is supposed to do first, then giving the SQL.)
Tables are "master" with a PK of "id", and "detail" with a FK "master_id" which links to (surprise) "master"."id".

Approach (A): UPDATE a JOIN that uses GROUP BY on the detail table.
I already read that this gives an ORA-01779. It would have been nice if it had miraculously worked for me :-), particularly because filtering the nothing-changed records from the update set would have been straightforward.
UPDATE
( SELECT
    m.id,
    m.lasttxdate,
    MAX (d.txdate) AS new_lasttxdate
  FROM
    master m
    JOIN detail d ON d.masterid = m.id
  GROUP BY d.masterid
)
SET m.lasttxdate = new_lasttxdate
WHERE m.lasttxdate != new_lasttxdate
(Actual production code goes the extra length and detects transitions between NULL and not-NULL.)

Approach (B): MERGE from an aggregated subquery.
This needs to filter the update set on the subquery, so the subquery needs an EXISTS condition to avoid generating non-changing update rows.
There is some awkwardness involved since the JOIN condition needs to be written twice.
Worse, the plan looks like it's doing an extra UNIQUE SCAN on "master"'s PK for the EXISTS condition. Of course, in 20/20 hindsight, I recognize that the extra access could be avoided only if the optimizer knew about GROUP BY and that it provides unique records :-)
MERGE INTO master m
USING
  (
    SELECT
      m.id,
      MAX (d.lasttxdate) AS new_lasttxdate
    FROM detail d
    GROUP BY d.masterid
    HAVING EXISTS (SELECT * FROM master WHERE
      master.id = d.masterid
      AND MAX (d.lasttxdate) != m.lasttxdate
    )
  )
ON (d.masterid = m.id)
WHEN MATCHED THEN UPDATE SET m.lasttxdate = new_lasttxdate
WHEN NOT MATCHED [...dummy INSERT here...]


I didn't have an approach (C), which is why I'm asking here...
Tom Kyte
June 22, 2010 - 12:36 pm UTC

I'd merge, but try using MINUS instead of having exists

Your merge confuses me - you reference m.id in the using ?
using
(
select id, max(d.lasttxdate) from detail group by id
minus
select id, lasttxdate from master
)
.....


A reader, June 23, 2010 - 4:23 am UTC

Getting the minor detail out of the way first:

Your merge confuses me - you reference m.id in the using ?

My bad - it should have been m.id instead of master.id inside the HAVING clause.

----

I'd merge, but try using MINUS instead of having exists

Hey. That's a really good one, it even scales in all the directions that I can foresee a need for:
a) complicated expressions with subselects instead of just max(d.lasttxdate), since it needs the SELECT expressions only once;
b) multi-field updates: the MINUS filters out all records that already have all fields correct... I'd be in trouble if I wanted the update to cancel if some of the fields were already correct, but I don't expect to ever need that; and finally
c) "lastupdate" fields in the master table; in fact, any fields that are to be updated only if there's an update in the main fields.

(I wasn't thinking of MINUS because some queries I tried with it on 9i were horribly misoptimized. Also, I probably need more table-level thinking to routinely consider MINUS... it's easy to overlook it if you do record-level thinking all day.)

Wrapping it all up, I get this:
using (
  select
    id, -- PK
    (select ...) as new_firstsale, -- complicated subselect
    max(d.lasttxdate) as new_lasttxdate -- second field
  from detail group by id
  minus
  select
    id, -- PK
    firstsale, -- name of field #1 to be updated
    lasttxdate -- name of field #2 to be updated
  from master
) on (d.masterid = m.id)
when matched then update set
  m.firstsale = new_firstsale,
  m.lasttxdate = new_lasttxdate,
  m.lastupdate = sysdate
...
Any conceptual errors in that?
Room for improvement?

(I hate fan clubs, but the Ask Tom fan club membership seems to be mandatory - count me in ;-) .)
Tom Kyte
June 24, 2010 - 6:43 am UTC

the only other thing I would consider would be to NOT MINUS, to update all of the rows


using (
  select
    id, -- PK
    (select ...) as new_firstsale, -- complicated subselect
    max(d.lasttxdate) as new_lasttxdate -- second field
  from detail group by id
) on (d.masterid = m.id)
when matched then update set
  m.firstsale = new_firstsale,
  m.lasttxdate = new_lasttxdate,
  m.lastupdate = case when decode(m.firstsale,new_firstsale,0,1)=0
                           or 
                           decode(m.lasttxdate,new_lasttxdate,0,1)=0 
                      then sysdate
                      else m.lastupdate
                   end



That would be useful if the MINUS takes a very long time and the added update of the rows in M takes significantly less time than the MINUS.

Or, more simply put:

if the minus removes relatively few rows, then skip the minus - update the extra rows, but take care to not change the lastupdate value if the values did not change.



A reader, June 24, 2010 - 8:45 am UTC

Yes, my typical MINUS will filter out anything between 50% and all rows except a handful, but it's something to keep in mind for those cases where the situation is different.

I now have a valuable new tool in my toolbox, plus instructions how to use it, and no questions left.
I'm closing this exchange with my thanks; this is how online help should work and often doesn't.

Pit, August 13, 2010 - 2:46 am UTC


Wouldn't it be enough to filter the records to be changed in the "on" clause?

For example:
using (
  select
    id, -- PK
    (select ...) as new_firstsale, -- complicated subselect
    max(lasttxdate) as new_lasttxdate -- second field
  from detail
  group by id
) new
on (
  m.id = new.id
  and (
    m.firstsale != new.firstsale
    or m.lasttxdate != new.lasttxdate
  )
)
when matched then
  update set
    m.firstsale = new.firstsale,
    m.lasttxdate = new.lasttxdate,
    m.lastupdate = sysdate

Tom Kyte
August 19, 2010 - 12:28 am UTC

you cannot modify a column that was referenced in the ON clause.

Joining two key-preserved views on the same table

Tony Killen, March 18, 2011 - 11:45 am UTC

Hi Tom,
This seemed like the best thread on which to ask this - my apologies if you've covered this elsewhere.

I'm trying to functionally split a single table with two key-preserved views and then join the two views on the primary key of the base table. I'm suprised that the CBO does not merge the two views together into a single select on the base table:

create table test_view_split
as select *
from dba_objects
where object_id is not null
and rownum < 101

begin
    dbms_stats.gather_table_stats(user,'test_view_split');
end;

alter table test_view_split
add (constraint pk_test_view_split
primary key (object_id))

create or replace view view1
as select OBJECT_ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME
from test_view_split

create or replace view view2
as select OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME
from test_view_split

And then join the two views together:

explain plan for 
select  v1.OBJECT_ID, v1.OWNER, v2.DATA_OBJECT_ID, v2.OBJECT_TYPE
from view1 v1, view2 v2
where v1.OBJECT_ID = v2.OBJECT_ID

select * from table(dbms_xplan.display) 
...
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |   100 |  2300 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |                 |   100 |  2300 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST_VIEW_SPLIT |   100 |   800 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST_VIEW_SPLIT |   100 |  1500 |     2   (0)| 00:00:01 |
...

The same thing happens if you join one view to itself:

explain plan for 
select  v1.OBJECT_ID, v1.OWNER, v2.OBJECT_NAME
from view1 v1, view1 v2
where v1.OBJECT_ID = v2.OBJECT_ID

select * from table(dbms_xplan.display)
...
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |   100 |  2100 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |                 |   100 |  2100 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST_VIEW_SPLIT |   100 |   800 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST_VIEW_SPLIT |   100 |  1300 |     2   (0)| 00:00:01 |
...

And before you ask why... I want to fix a bad design between two tables which are often joined by PK for example (conceptually):

TABLE_A
a1_id   --pk
a2_meta --metadata

TABLE_B
b1_id   --pk
b2_meta --metadata
b3_data --data in a varchar2(4000) 

Most of the time our queries join the two tables by PK to put together the two parts of the metadata without selecting the data field, so I want to put the metadata parts in one table (and save the join overhead) and the data part in another (like it should have been from the start!):

TABLE_A_NEW
a1_id 
a2_meta
b2_meta 

TABLE_B_NEW
b1_id
b3_data

In order to help the code migration I want to make two views over TABLE_A_NEW with the same names (and function) as the original tables:

create view TABLE_A
as select 
a1_id,
a2_meta
from table_a_new

create view TABLE_B
as select
a1_id b1_id,
b2_meta 
from table_a_new

I'll have to amend the insert, update and delete parts of the legacy code but I want the select parts (the majority) to use the two views and the select code will still join the two views together and I'll still take the hit on the join.

So it looks like my scaffolding won't work and I'll have to migrate all the select code after all.

Any thoughts greatly appreciated...
Tom Kyte
March 18, 2011 - 11:55 am UTC

11g does this

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key(object_id);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace view a as select object_id, object_name  from t;

View created.

ops$tkyte%ORA11GR2> create or replace view b as select object_id, owner  from t;

View created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select a.object_id, a.object_name from a, b where a.object_id = b.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 71457 |  2093K|   291   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T    | 71457 |  2093K|   291   (1)| 00:00:04 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off



I assume you must be in 10g or before.

Joining two key-preserved views on the same table

Tony Killen, March 18, 2011 - 12:25 pm UTC

Wow! You know Tom, next week would have been fine for a reply... Next month even... But 20 minutes later... How many of you are there?

I'm on 10.2.0.4. The good news is that this work is planned concurrent with an 11g migration (to use reference partitioning between the two tables in fact). I get my 11G sand box in a fortnight and I'm just investigating the issue on 10G until then. I should have waited...

Thanks. Really, properly - thanks.

Dynamic Sampling

A reader, March 20, 2011 - 2:29 am UTC

Hi Tom,

Why the dynamic sampling for your query has kicked-in? Is this because you did not gather stats? What is the reason for level=2?
Thanks
Tom Kyte
March 20, 2011 - 9:26 am UTC

no stats + 10g or above = dynamic sampling by default.

It is the way 10g and above work, dynamic sampling is set to level 2 out of the box by default.

key preserved <> unique constraint exists and is satisfied

visitor, May 18, 2011 - 8:22 am UTC

This example is copied from:
http://forums.oracle.com/forums/thread.jspa?threadID=378144



create table a(
id_ INT,
col_ DATE
);

create table b(
id_ INT,
type_ INT,
col_ DATE
);

alter table a add constraint pk_a primary key(id_);

alter table b add constraint pk_b primary key(id_, type_);

alter table b add constraint fk_b_a foreign key(id_) references a(id_);

insert into a(id_) values(1);

insert into b(id_, type_, col_) values(1, 1, trunc(sysdate)-2);

insert into b(id_, type_, col_) values(1, 7, trunc(sysdate)-3);

update(select a.id_, a.col_ "a_col_", b.col_ "b_col_"
from a, b
where a.id_ = b.id_
and b.type_=7
)
set "a_col_" = "b_col_";


Why this is not key preserved table ?
Table b is referenced by unique index.
Tom Kyte
May 18, 2011 - 10:39 am UTC

because the JOIN columns between A and B need to be unique. You know and I know that by adding in the filter "b.type=7", it makes it unique - but the software doesn't recognize this.

You can and should use MERGE instead.

merge into a
using (select * from b where type = 7) b
on (a.id = b.id)
when matched then .....;


Run time vs compile time error..????

Karan Chadha, February 19, 2012 - 8:24 am UTC

First of All.. Thanks a lot for running this site.. The information provided here is very very useful...

My question is - Why is "ORA-01779: cannot modify a column which maps to a non key-preserved table" a Run time error???

I created 2 tables as below -

1. First table - T1

Col1 Col2
----- -----
1 a
2 b

2. Second table - T2

Col1 Col2
----- -----
1 c
2 d

The below query fails with error - ORA-01779 at run time (I am using it inside a procedure)

UPDATE (SELECT a.col2 as col2_T1
, b.col2 as col2_T2
FROM T1 a, T2 b
WHERE a.col1 = b.col1)
set col2_T1 = col2_T2

but the query run perfectly fine if i apply Primary key constraints on Col1 of both the tables..

So, I was just wondering why Oracle kept this as Run time error? If Oracle is not considering the data and is just deciding based on constraints, then it should have been a compile time error (my personal thoughts). And any modifications to the constraints on these tables should make my procedure INAVLID...

Can you please let me know your thoughts on this?
Tom Kyte
February 19, 2012 - 6:10 pm UTC


The technical reason is for the fact that the procedure is dependent on the TABLE (specifically in 11g and above, the columns of the table it references) - not on the subordinate objects surrounding the table such as constraints, triggers and the like. We do a syntax and security check at compile time and set of a dependency to the tables being referenced.

Reader, April 24, 2012 - 2:57 am UTC

I have to update a column based on the same column but different rows. I wrote two versions of the query but both of them are not working and resulting into ORA:xxxx errors.

Can you please suggest what is the workaround and how to get it running.

Use Case: We have two tables table_metadata and table_col_metadata and based on table type being 'FACT' we need to update the rows which have table type 'EXTRACTOR'

The join condition is also derived out of both tables.

x.target_table_name = y.source_table_name AND
x.table_column_name = y.table_column_name AND
x.column_position = y.column_position

where target_table_name and source_table_name are attributes of table_metadata and the rest ones are col_metadata.

MERGE into
(
Select a.job_id , a.target_table_name, b.table_column_name , b.column_position,b.is_dimension Ext_dimension_flg
from table_metadata a , col_metadata b
Where a.job_id = b.job_id AND table_type = 'EXTERNAL'
) x
USING
(
Select a.job_id , a.source_table_name, b.table_column_name , b.column_position,b.is_dimension Transf_dimension_flg
from table_metadata a , col_metadata b
Where a.job_id = b.job_id AND table_type = 'FACT'
) y
ON (x.target_table_name = y.source_table_name AND x.table_column_name = y.table_column_name AND x.column_position = y.column_position)
WHEN MATCHED THEN
UPDATE SET Ext_dimension_flg = Transf_dimension_flg

[Error] Execution (2: 1): ORA-38106: MERGE not supported on join view or view with INSTEAD OF trigger.

UPDATE
(
Select * FROM
(
Select a.job_id , a.target_table_name, b.table_column_name , b.column_position,b.is_dimension Ext_dimension_flg
from table_metadata a , col_metadata b
Where a.job_id = b.job_id AND table_type = 'EXTERNAL'
) x,
(
Select a.job_id , a.source_table_name, b.table_column_name , b.column_position,b.is_dimension Transf_dimension_flg
from table_metadata a , col_metadata b
Where a.job_id = b.job_id AND table_type = 'FACT'
) y
WHERE
x.target_table_name = y.source_table_name AND
x.table_column_name = y.table_column_name AND
x.column_position = y.column_position
)
SET
Ext_dimension_flg = Transf_dimension_flg


[Error] Execution (20: 1): ORA-01779: cannot modify a column which maps to a non key-preserved table

Can you suggest some help.

Tom Kyte
April 24, 2012 - 7:46 am UTC

wow, when I try to run them, I get a different error message:

        from table_metadata a , col_metadata b
                                *
ERROR at line 5:
ORA-00942: table or view does not exist


can you help me figure that out and solve that problem first?


(hint hint, big old hint here - if you want a sql statement, one that works, you sort of need to provide simple create tables and inserts)

and please explain in the form of a requirement specification what you are trying to do. I am not a reverse compiler. It is really difficult (and not really sensible actually) to try to figure out what must be accomplished by reverse engineering a statement *that does not work, that does not make sense*.



key-preserved but can't update, can we update 2 table @ the same time ?

A reader, June 28, 2012 - 3:18 pm UTC

hi tom,

just a simple example as below

------------------------------------
create table test
(ID NUMBER unique,
NAME VARCHAR2(20));

CREATE TABLE TEST2
(COLOUR VARCHAR2(20),
ID number unique);

INSERT INTO TEST VALUES (1, 'APPLE');
INSERT INTO TEST2 VALUES('RED',1);

update (select * from test join test2 using (ID))
set name='APPLES', colour='VERY RED';

-----------------------------

The view in the UPDATE statment are key preserved, both table will only have 1 row in the view but i got the error

"
Error report:
SQL Error: ORA-01776: cannot modify more than one base table through a join view
01776. 00000 - "cannot modify more than one base table through a join view"
*Cause: Columns belonging to more than one underlying table were either
inserted into or updated.
*Action: Phrase the statement as two or more separate statements.
"


Any idea why so ?

Please advice
Regards,
Noob
Tom Kyte
June 29, 2012 - 10:03 am UTC

this has nothing to do with key preservation, the actual error is self explanatory:

ops$tkyte%ORA11GR2> update (select * from test join test2 using (ID))
  2  set name='APPLES', colour='VERY RED';
set name='APPLES', colour='VERY RED'
                   *
ERROR at line 2:
ORA-01776: cannot modify more than one base table through a join view

ops$tkyte%ORA11GR2> !oerr ora 1776
01776, 00000, "cannot modify more than one base table through a join view"
// *Cause: Columns belonging to more than one underlying table were either
//         inserted into or updated.
// *Action: Phrase the statement as two or more separate statements.




did you read the error message? It says pretty clearly "you can only modify ONE base table through a join view" - period. You are trying to modify TWO base tables.

A reader, July 02, 2012 - 2:37 pm UTC

hi tom,

Noted. So it is a oracle rule that only 1 base table can be updated at any one time.

I was initially thinking that if we could update more then 1 table @ a time in a statement. just curious

thanks for the confirmation.

Rgds,
Noob

Non-Updateable, yet Usable

Eashwer Iyer, August 03, 2012 - 6:18 pm UTC

There are so many index "states" that we keep referencing...
INVISIBLE
NON-UPDATEABLE
OFFLINE
etc











Today our batch job slowed down as a result of an index on a table that was being written to by several processes, and it was temporarily dropped.



Would be nice if Oracle Can make Indexes NON-UPDATEABLE and yet, be utilized by a SELECT query...although ?


We wish to build the indices the night before and keep them offline, even as new entries make their way into a table.
We would like the queries to utilize the offline index though

Tom Kyte
August 17, 2012 - 10:04 am UTC

how would that work??????


if it were non-updatable and the table were updated, the index would return what it known as "a wrong answer".

If you employee partitioning - we can do some magic though. If you were to partition by day - so the new inserts go into today, batch works on yesterday - you can index or not each partition and query plans will or will not use indexes at run time.


http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21asktom-312223.html

see "Partition Plans"

sub select with group by is unique - so why is it not considered key-preserved

Ian, September 09, 2015 - 9:41 am UTC

Hi

I am trying to update a table with a unique key from a sub select which has a group by - which I would have thought must also be logically unique. But I am still getting an ORA-01779: cannot modify a column which maps to a non key-preserved table

10.2.0.4

CREATE TABLE ib_test_2
AS
SELECT id
      ,SYSDATE - 1 reading_date
  FROM (SELECT rownum id
          FROM dba_objects
         WHERE rownum <= 100);

INSERT INTO ib_test_2
  SELECT id
        ,SYSDATE
    FROM ib_test_2;

COMMIT;

CREATE TABLE ib_test_3
AS
SELECT id
      ,to_date(NULL) reading_date
  FROM ib_test_2
 GROUP BY id;

CREATE UNIQUE INDEX ib_test_3_u1 ON ib_test_3(id);

UPDATE (SELECT a.reading_date
              ,b.new_reading_date
          FROM ib_test_3 a
              ,(SELECT id
                      ,MAX(reading_date) new_reading_date
                  FROM ib_test_2
                 GROUP BY id) b
         WHERE a.id = b.id)
   SET reading_date = new_reading_date;




SQL> l
  1  CREATE TABLE ib_test_2
  2  AS
  3  SELECT id
  4        ,SYSDATE - 1 reading_date
  5    FROM (SELECT rownum id
  6            FROM dba_objects
  7*          WHERE rownum <= 100)
SQL> /

Table created.

Elapsed: 00:00:00.03
SQL>
SQL> l
  1  INSERT INTO ib_test_2
  2    SELECT id
  3          ,SYSDATE
  4*     FROM ib_test_2
SQL> /

100 rows created.

SQL>
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL>
SQL> l
  1  CREATE TABLE ib_test_3
  2  AS
  3  SELECT id
  4        ,to_date(NULL) reading_date
  5    FROM ib_test_2
  6*  GROUP BY id
SQL> /

Table created.

Elapsed: 00:00:00.03
SQL>
SQL> CREATE UNIQUE INDEX ib_test_3_u1 ON ib_test_3(id);

Index created.

Elapsed: 00:00:00.02
SQL>
SQL>
SQL> l
  1  UPDATE (SELECT a.reading_date
  2                ,b.new_reading_date
  3            FROM ib_test_3 a
  4                ,(SELECT id
  5                        ,MAX(reading_date) new_reading_date
  6                    FROM ib_test_2
  7                   GROUP BY id) b
  8           WHERE a.id = b.id)
  9*    SET reading_date = new_reading_date
SQL> /
   SET reading_date = new_reading_date
       *
ERROR at line 9:
ORA-01779: cannot modify a column which maps to a non key-preserved table


So my question is - how come the sub select is not considered to be key-preserved when the group by clearly makes it unique?

Thanks.

Ian

PS - When I try to preview my review I get "Error during rendering of region "Question Review". ORA-01403: no data found"

Using Chrome Version 45.0.2454.85 m on Win XP

Re sub select with group by is unique - so why is it not considered key-preserved

Ian, September 09, 2015 - 9:57 am UTC

Hi

Update - this works in 12.1.0.2

So a bug in 10.2.0.4?

Regards

Ian
Connor McDonald
September 14, 2015 - 2:15 am UTC

I would say less 'bug' more just 'growth' :-)

The reality is, with each release of the database, the query transformations, optimizer, and the like all get more sophisticated, so we can pick up more opportunities to know things like key-preservation.

Hope this helps.

Key preserved tables from 19c and 21c

Rajeshwaran, Jeyabal, September 14, 2022 - 8:33 am UTC

Team,

the below test case was run from ATP 19c and 21c database.

when run from 19c we see this & it looks fine.

demo@ATP19C> create table emp as select * from scott.emp;

Table created.

demo@ATP19C> create table dept as select * from scott.dept;

Table created.

demo@ATP19C> alter table dept add constraint dept_pk primary key(deptno);

Table altered.

demo@ATP19C> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept;

Table altered.

demo@ATP19C> create or replace view v
  2  as
  3  select e.empno,e.ename, e.deptno,d.dname,d.loc
  4  from emp e, dept d
  5  where e.deptno = d.deptno;

View created.

demo@ATP19C> update v set dname ='X' where empno = 7499;
update v set dname ='X' where empno = 7499
             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


the same update does not get errored in 21c database
demo@ATP21C> update v set dname ='X' where empno = 7499;

1 row updated.

is that the Key preserved concept got changed in Oracle 21c? why we dont see any error message there, please help us to understand it better.
Connor McDonald
September 27, 2022 - 5:44 am UTC

Based on the updatable status, I think ATP is probably correct here, ie

SQL> create table emp as select * from scott.emp;

Table created.

SQL> create table dept as select * from scott.dept;

Table created.

SQL> alter table dept add constraint dept_pk primary key(deptno);

Table altered.

SQL> alter table emp
  2      add constraint emp_fk
  3      foreign key(deptno)
  4      references dept;

Table altered.

SQL>
SQL> create or replace view v_join
  2      as
  3      select e.empno,e.ename, e.deptno,d.dname,d.loc
  4      from emp e, dept d
  5      where e.deptno = d.deptno;

View created.

SQL>
SQL> update v_join set dname ='X' where empno = 7499;

1 row updated.

SQL>
SQL> select * from user_UPDATABLE_COLUMNS
  2  where table_name = 'V_JOIN'
  3  @pr
==============================
OWNER                         : MCDONAC
TABLE_NAME                    : V_JOIN
COLUMN_NAME                   : EMPNO
UPDATABLE                     : YES
INSERTABLE                    : YES
DELETABLE                     : YES
==============================
OWNER                         : MCDONAC
TABLE_NAME                    : V_JOIN
COLUMN_NAME                   : ENAME
UPDATABLE                     : YES
INSERTABLE                    : YES
DELETABLE                     : YES
==============================
OWNER                         : MCDONAC
TABLE_NAME                    : V_JOIN
COLUMN_NAME                   : DEPTNO
UPDATABLE                     : YES
INSERTABLE                    : YES
DELETABLE                     : YES
==============================
OWNER                         : MCDONAC
TABLE_NAME                    : V_JOIN
COLUMN_NAME                   : DNAME
UPDATABLE                     : NO       <<<======
INSERTABLE                    : NO
DELETABLE                     : NO
==============================
OWNER                         : MCDONAC
TABLE_NAME                    : V_JOIN
COLUMN_NAME                   : LOC
UPDATABLE                     : NO
INSERTABLE                    : NO
DELETABLE                     : NO

PL/SQL procedure successfully completed.


so I'll need to ask around internally on that one

Key preserved tables from 19c and 21c

Rajeshwaran, Jeyabal, September 27, 2022 - 7:44 am UTC

....Based on the updatable status, I think ATP is probably correct here....

since both the version (19c and 21c) from the above demo was from ATP only.
so when you say "ATP is correct" which version are you referring to? ATP19c or ATP 21c?
Connor McDonald
September 29, 2022 - 1:53 am UTC

Ah - my ATP 19 shows the error (which I think is correct), so yours is perhaps still getting patched.

In any event, I've logged a bug

Key preserved tables from 19c and 21c

Rajeshwaran, Jeyabal, September 29, 2022 - 8:03 am UTC

...Ah - my ATP 19 shows the error (which I think is correct), so yours is perhaps still getting patched....

Even my ATP 19c shows the error.
demo@ATP19C> update v set dname ='X' where empno = 7499;
update v set dname ='X' where empno = 7499
             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

but the problem is with 21c, which is incorrectly getting updated.
demo@ATP21C> update v set dname ='X' where empno = 7499;

1 row updated.

so hope you have logged a bug with 21c database and not with 19c database.
Connor McDonald
September 30, 2022 - 4:17 am UTC

There more info to come on this one - so I'll be doing a full blog post on it.

Stay tuned.

Key preserved tables from 19c and 21c

Rajeshwaran, Jeyabal, January 22, 2023 - 6:36 am UTC

I was watching over this video https://www.youtube.com/watch?v=rA0K0PP5L18 at 01:01:30 you talk about "updatable joins are broken" can i consider this video as a response to the above follow up? or as mentioned should i wait for full blog post.

couple of questions:

a) do we have docs link for 21c database that go over this "enhancement" to updatable joins in detail.

b) that bug on data dictionary for "updatable joins" are fixed in 23c?

Connor McDonald
January 23, 2023 - 6:49 am UTC

a) no :-)

b) no :-)

With (b) its a tough one because "NO" is partially correct in that they are not the same as key-preserved columns. Ideally we 'd change the column names in the dictionary but that creates all sorts of backward compatible criticisms