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?
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!
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
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??
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?
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 ?
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').
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.
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?
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;
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.
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
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
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
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?
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.
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!
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.
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.
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
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.
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
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>
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)
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>
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
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.
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.
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
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;
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
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?
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...
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 existsHey. 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 ;-) .)
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
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...
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
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.
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?
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.
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
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
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
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.
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?
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.
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?
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