The development of students in recent days reflects a problem, there is a Java night program, used to delete historical expired data regularly every day, before March 10, after internal testing, but these two days again, there is a SQL statement has been reported ORA-01752 error, due to a recent development library migration, from one computer room to another, and the development of students are sure. Recognize that the code logic has not changed during this period, so I doubt whether it is related to data migration. This error was referred to as a bug by the test students, and was stuck in the version test, which may cause schedule delays, so it is an urgent problem.
Let me give you some more information about this problem.
(1). Error-reporting SQL
delete FROM (select * from TBL_A a inner join TBL_B b on a.a_id = b.id inner join TBL_C c on b.b_a = c.c_a and b.b_b = c.c_b where c.c_date <= trunc(sysdate)-1) where ROWNUM <= 10;(2) This SQL can be executed before March 10, and it will report an error if it is executed again now.
ORA-01752: cannot delete from view without exactly one key-preserved table
(3) Data migration was done around March 10, exp/imp exported and imported data.
First, let's look at what the ORA-01752 error means.
01752, 00000, "cannot delete from view without exactly one
key-preserved table"
// *Cause: The deleted table had
// - no key-preserved tables,
// - more than one key-preserved table, or
// - the key-preserved table was an unmerged view.
// *Action: Redefine the view or delete it from the underlying base tables.
The implication is that delete operations cannot be performed from a view without a clear key-preserved table.
There are three reasons, no key-preserved table, more than one key-preserved table, or a key-preserved table is a non-merged view.
The solution is to redefine the view or perform delete operations from the base table.
This explanation is rather confusing. What is the key-preserved table? Why does a key-preserved table need to be used to delete view s formed by these select multi-table associations by executing delete statements?
See how Oracle official documents define key-preserved tables.
(http://docs.oracle.com/cd/E11882_01/server.112/e25494/views.htm#i1006318) 
The key-preserved table is the basis for understanding restrictions on modifying join views of join views. If the primary/unique key of a table is the primary/unique key of the join join join result set, the table is called the key-preserved table. Therefore, the primary/unique key of the key-preserved table will be retained throughout the join process.
Not the primary key/unique key of this table must appear in the select clause, but if it appears in the result set of join join join, it must satisfy the requirement of being the primary key/unique key of this result set.
The key-preserving attribute of a table does not depend on the actual data in the table. For example, if the emp table has at most one employee in each department, the deptno field will be a unique value in the connection result set of emp and dept, but Dept will not be defined as a key-preserved table because such data exists.
In this view, emp is a key-preserved table, because empno is the primary key of the emp table, and you can see that it is the primary key of the join join join result set. Dept is not a key-preserved table, because although deptno is the primary key in the dept table, it is not the primary key in the join join result set.
(http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT1514) 
If there are multiple tables or views in the FROM clause that defines the view, then this view is called join view connection view.
An updated join view connection view, also known as a modified join view connection view, includes two or more base tables or views that allow DML operations to be performed. There will be SELECT statements containing multiple tables in the FROM clause of the updatable view, and there will be no WITH READ ONLY clause restriction.
In order to inherit updatability, views must meet some criteria. For example, a general rule is that INSERT, UPDATE, or DELETE operations affect only one base table at a time.
The USER_UPDATABLE_COLUMNS data dictionary returns that the join view connection view created above is updatable, and all updatable columns in the join view connection view must be mapped to the column of the key-preserved table. The key-preserved table is the base table where the row data appears at most once in the result set. department_id is the primary key of the departments table, so each row of data in the employees table will only appear once in the result set at most, so the employees table is a key-preserved table. Departments tables are not key-preserved tables because each row of data may appear multiple times in the result set.
Simply illustrated by an experiment,
Experiment 1 - Suppose emp table has the following data.
employee_id, department_id 1,1 2,1 3,2
Suppose the dept table has the following data and the department_name field is not the primary key.
department_id,department_name
1,'a'
1,'b'
2,'a'Then, the view consisting of emp and dept contains the following records.
employee_id,department_id,department_name
1,1,'a'
1,1,'b'
2,1,'a'
2,1,'b'
3,2,'a'It can be seen that each data in emp table is likely to be duplicated, so emp table is not a key-preserved table.
Experiment 2 - Suppose emp table has the following data.
employee_id, department_id 1,1 2,1 3,2
Suppose the dept table has the following data, and the department_name field is the primary key.
department_id,department_name
1,'a'
2,'a'Then, the view composed of emp and dept tables has the following records.
employee_id,department_id,department_name
1,1,'a'
2,1,'a'
3,2,'a'It can be seen that each data in emp table is not duplicated, so emp table is a key-preserved table.
(http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8004.htm#SQLRF01504)
A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
If you want a join view to be updatable, then all of the following
conditions must be true:
● The DML statement must affect only one table underlying the join.
● For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
● For an UPDATE statement, the view must not be created WITH CHECK OPTION, and all columns updated must be extracted from a key-preserved table.
● For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.
DELETE statement, if there are multiple key-preserved tables in the join result set, Oracle deletes the first table of the FROM clause, regardless of whether its view is created with WITH CHECK OPTION or not.
DML statements must affect only one base table.
From the above documents, the main point of view is summarized. An updatable join view associated with multiple tables can only delete one of the base tables if deletion is allowed grammatically. This table is a key-preserved table. If the primary/unique key of a table is the primary/unique key of the updatable join view connection result set, then this table is called a key-preserved table.
Experiments show that,
SQL> create table t_a (x number primary key);
Table created.
SQL> create table t_b (x number primary key);
Table created.
SQL> insert into t_a values(1);
1 row created.
SQL> insert into t_a values(2);
1 row created.
SQL> insert into t_b values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_a;
X
----------
1
2
SQL> select * from t_b;
X
----------
1
SQL> delete from
2 (select a.x, b.x from t_a a, t_b b where a.x = b.x);
1 row deleted.
SQL> select * from t_a;
X
----------
2
SQL> select * from t_b;
X
----------
1The deletion statement can be executed a t this time, deleting the record of the first table t_a.
SQL> rollback;
Rollback complete.
SQL> delete from
2 (select a.x, b.x from t_b b, t_a a where a.x = b.x);
1 row deleted.
SQL> select * from t_a;
X
----------
1
2
SQL> select * from t_b;
no rows selectedThe deletion statement can be executed at this time, and the record of the first table t_b is deleted.
The above experiments show that,
(1) Unlike the ORA-01752 error prompt, "more than one key-preserved table" can cause this error, delete can delete two different key-preserved tables.
(2) delete deletes select... The first table followed by the from clause, as in the example above, will be deleted if t_a or t_b is the first table.
The experiment contradicts the description of ORA-01752, which Tom actually mentioned in this article.( https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:184812348071 It is pointed out that this is a DELETE behavior after 9i, for which bug s are recorded.
that is the behavior of DELETE since version 9i, I filed a documentation bug against that a while ago and it is updated in the current doc set:
http://docs.oracle.com/docs/cd/E11882_01/server.112/e10595/views001.htm#ADMIN11786
This link has expired, but by retrieving it, the source is the DELETE Statements and Join Views chapters of the Database Administrator's Guide.
Looking at the explanations of this chapter in 11.2, 12.1 and 12.2, the narrative is slightly different.
11.2
http://docs.oracle.com/cd/E11882_01/server.112/e25494/views.htm#ADMIN11781
You can delete from a join view provided there is one and only one key-preserved table in the join.
12.1
http://docs.oracle.com/database/121/ADMIN/views.htm#ADMIN-GUID-367FAA9B-269C-41AD-A429-631D144CF36F
For most join views, a delete is successful only if there is one and only one key-preserved table in the join.
12.2
http://docs.oracle.com/database/122/ADMIN/managing-views-sequences-and-synonyms.htm#ADMIN11781
For most join views, a delete is successful only if there is one and only one key-preserved table in the join
11.2 indicates that only one key-preserved table in a join connection can delete the join view.
12.1 and 12.2 describe the same thing. For most join views, delete of join views can be performed only if there is and only one key-preserved table in the join connection.
The following is the original version of 11.2. 
Master Huang Yong's explanation is very vivid.
"Indeed the documentation is not complete. It does talk about two key-preserved tables. There's a Note box saying "If the DELETE statement uses the same column in its WHERE clause that was used to create the view as a join condition, then the delete operation can be successful when there are different key-preserved tables in the join."
But that doesn't explain your case, which I still have trouble completely understanding. Here's my thinking. The join condition in the in-line view, "a.a_id = b.id", causes tbl_b to act as the parent and tbl_a as a child because tbl_b.id is PK. I know you didn't explicitly define the foreign key on tbl_a, so I say "act as". Then you have the join conditions "b.b_a = c.c_a and b.b_b = c.c_b". But these don't establish a parent-child relation between tbl_b and tbl_c because there's no key involved. As soon as you create the unique key on (c_a,c_b) of tbl_c, tbl_c acts like the parent and tbl_b the child. Now the relationship among all 3 tables are clear: tbl_c acts like the parent of tbl_b, which acts like the parent of tbl_a. The bottom table (tbl_a) is the most detailed and is the key-preserved table."
This bug records the problem, "Bug 24921723: DOCUMENTATION BUG - DELETE STATEMENTS AND JOIN VIEWS" 
Bugs further illustrate this problem, which should be reflected in the document.
For the first point above, it is not true that you can delete from a join view provided there is one and only one key-preserved table in the join. The delete will work even though there are two different key preserved tables in the join. we can see that the delete is working even though there are two different key-preserved tables and its not necessarily same table should be repeated. The documentation should be changed to reflect this behavior.
Again as said in point 1) this should be changed to reflect that the delete table operates on the first table in the list and the tables in the from list doesn't necessarily to be same key-preserved table and it can be different tables. If the DELETE statement uses the same column in its WHERE clause that was used to create the view as a join condition, then the delete operation can be successful when there are different key-preserved tables in the join. In this case, the DELETE statement operates on the first table in the FROM list, and the tables in the FROM list can be different from the tables in the WHERE clause.
The bug was proposed in 11.2.0.3 and fixed in 12.1.0.2. This is to add the phrase "For most join views" to the document, indicating that there are exceptions.
Now that we've described what an ORA-01752 error is and what a key-preserved table is, let's simulate the initial problem.
(1) Create three test tables.
SQL> create table tbl_a (id number primary key, a_id number);
Table created.
SQL> create table tbl_b (id number primary key, b_a varchar2(1), b_b varchar2(1));
Table created.
SQL> create table tbl_c (id number primary key, c_a varchar2(1), c_b varchar2(1), insert_time timestamp);
Table created.(2) Create simulated data.
SQL> insert into tbl_a values(1, 1);
1 row created.
SQL> insert into tbl_a values(2, 2);
1 row created.
SQL> insert into tbl_b values(1, 'a', 'a');
1 row created.
SQL> insert into tbl_b values(2, 'b', 'b');
1 row created.
SQL> insert into tbl_b values(3, 'c', 'c');
1 row created.
SQL> insert into tbl_c values(1, 'a', 'a', sysdate-1);
1 row created.
SQL> insert into tbl_c values(2, 'b', 'b', sysdate);
1 row created.
SQL> insert into tbl_c values(3, 'c', 'c', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tbl_a;
ID A_ID
---------- ----------
1 1
2 2
SQL> select * from tbl_b;
ID B B
---------- - -
1 a a
2 b b
3 c c
SQL> select * from tbl_c;
ID C C INSERT_TIME
---------- - - -----------------------------------------
1 a a 27-MAR-17 05.11.58.000000 PM
2 b b 28-MAR-17 05.12.11.000000 PM
3 c c 28-MAR-17 05.12.27.000000 PMExecute select separately. from clause, you can know that a record will be returned.
SQL> select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate);
ID A_ID ID B B ID C C INSERT_TIME
---------- ---------- ---------- - - ---------- - - ------------
1 1 1 a a 1 a a 27-MAR-17 05.11.58.000000 PMExecuting the DELETE statement will result in an error of ORA-01752.
SQL> delete from
2 (select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c
3 on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate))
4 where rownum <=2;
(select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c
*
ERROR at line 2:
ORA-01752: cannot delete from view without exactly one key-preserved tableAt this time, add unique constraints to table TBL_B, execute DELETE statement, and continue to report errors.
SQL> alter table tbl_b add constraint unique_tbl_b_01 unique (b_a, b_b);
Table altered.
SQL> delete from
2 (select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c
3 on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate))
4 where rownum <=2;
(select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c
*
ERROR at line 2:
ORA-01752: cannot delete from view without exactly one key-preserved tableAt this time, add unique constraints to table TBL_C, execute DELETE statement, can be deleted normally, and it is known that table TBL_A data is deleted.
SQL> alter table tbl_c add constraint unique_tbl_c_01 unique (c_a, c_b);
Table altered.
SQL> delete from
(select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c
on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate))
where rownum <=2;
1 row deleted.
SQL> select * from tbl_a;
ID A_ID
---------- ----------
2 2
SQL> select * from tbl_b;
ID B B
---------- - -
1 a a
2 b b
3 c c
SQL> select * from tbl_c;
ID C C INSERT_TIME
---------- - - ---------------------------------------------------------------------------
1 a a 27-MAR-17 05.11.58.000000 PM
2 b b 28-MAR-17 05.12.11.000000 PM
3 c c 28-MAR-17 05.12.27.000000 PMIn this case, a unique constraint is added to table TBL_C. The difference is that the unique constraint contains the id field. DELETE statement (excluding the id field) is executed to report an error of ORA-01752, indicating that the key-preserved table cannot be determined.
SQL> alter table tbl_c add constraint unique_tbl_c_01 unique (c_a, c_b, id);
Table altered.
SQL> delete from
(select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c
on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate))
where rownum <=2;
(select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c
*
ERROR at line 2:
ORA-01752: cannot delete from view without exactly one key-preserved tableIn another way, create a view with the same semantics as DELETE above.
SQL> create view v_abc as select a.a_id, b.id, b.b_a, b.b_b, c.c_a, c.c_b, c.insert_time from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate);
View created.As you can see from the user_updatable_columns view, all columns in the original view can not be added or deleted.
SQL> select * from user_updatable_columns where table_name='V_ABC';
OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
BISAL V_ABC A_ID NO NO NO
BISAL V_ABC ID NO NO NO
BISAL V_ABC B_A NO NO NO
BISAL V_ABC B_B NO NO NO
BISAL V_ABC C_A NO NO NO
BISAL V_ABC C_B NO NO NO
BISAL V_ABC INSERT_TIME NO NO NO
7 rows selected.Add TBL_C unique constraints to create views.
SQL> alter table tbl_c drop constraint unique_tbl_c_01;
Table altered.
SQL> alter table tbl_c add constraint unique_tbl_c_01 unique(c_a, c_b);
Table altered.
SQL> create or replace view v_abc as select a.a_id, b.id, b.b_a, b.b_b, c.c_a, c.c_b, c.insert_time from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate);
View created.At this point, the a_id field of the TBL_A table displayed in the user_updatable_columns view allows for additions and deletions.
SQL> select * from user_updatable_columns where table_name='V_ABC';
OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
BISAL V_ABC A_ID YES YES YES
BISAL V_ABC ID NO NO NO
BISAL V_ABC B_A NO NO NO
BISAL V_ABC B_B NO NO NO
BISAL V_ABC C_A NO NO NO
BISAL V_ABC C_B NO NO NO
BISAL V_ABC INSERT_TIME NO NO NO
7 rows selected.Through the above experiments, it can be inferred that whether the DELETE statement is successfully executed depends on whether the TBL_B and TBL_C tables have primary or unique keys, and then on whether the records of the TBL_A tables to be deleted can uniquely determine whether the records in the result set are key-preserved tables.
Every change we make to the development library is executed using a set of database change tools developed by ourselves, which automatically records the change history, and the details of the change will be recorded in conjunction with Confluence. Before March 10, TBL_C table created a unique constraint, field is (C_A, C_B). On March 10, development proposed a change requirement, adding to this unique constraint. There is another field C_C, at which time (C_A, C_B, C_C) can uniquely determine a record, (C_A, C_B) can not uniquely determine a record.
For instance,
SQL> select * from tbl_a;
ID A_ID
1 1
2 2
SQL> select * from tbl_b;
ID B B
1 a a
2 b b
3 c c
SQL> select * from tbl_c;
ID C C INSERT_TIME
1 a a 27-MAR-17 06.46.09.000000 PM
2 b b 27-MAR-17 06.46.09.000000 PM
3 c c 27-MAR-17 06.46.09.000000 PM
4 a a 27-MAR-17 06.46.09.000000 PM
5 a a 27-MAR-17 06.46.09.000000 PM
SQL> select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate);
ID A_ID ID B B ID C C INSERT_TIME
1 1 1 a a 1 a a 27-MAR-17 06.46.09.000000 PM
2 2 2 b b 2 b b 27-MAR-17 06.46.09.000000 PM
1 1 1 a a 4 a a 27-MAR-17 06.46.09.000000 PM
1 1 1 a a 5 a a 27-MAR-17 06.46.09.000000 PM
SQL> insert into tbl_a values(3, 1);
SQL> select * from tbl_a a inner join tbl_b b on a.a_id = b.id inner join tbl_c c on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate);
ID A_ID ID B B ID C C INSERT_TIME
1 1 1 a a 1 a a 27-MAR-17 06.46.09.000000 PM
3 1 1 a a 1 a a 27-MAR-17 06.46.09.000000 PM
2 2 2 b b 2 b b 27-MAR-17 06.46.09.000000 PM
1 1 1 a a 4 a a 27-MAR-17 06.46.09.000000 PM
3 1 1 a a 4 a a 27-MAR-17 06.46.09.000000 PM
1 1 1 a a 5 a a 27-MAR-17 06.46.09.000000 PM
3 1 1 a a 5 a a 27-MAR-17 06.46.09.000000 PM
7 rows selected.At this time, the primary key of join join join result set can not be determined according to the primary key of TBL_A, TBL_B or TBL_C. Therefore, there is no key-preserved table, Oracle can not clearly delete the base table, so the ORA-01752 error is reported.
Solution:
(1) In order to delete TBL_A data, we need to make it a key-preserved table. Therefore, in addition to TBL_B having a primary key to qualify a unique value, we also need to make TBL_C conditional to qualify a unique value. For example, we can modify the unique constraints to (C_A, C_B) fields again, but this may not be consistent with business requirements.
(2) Rewrite SQL,
delete from
(select * from tbl_a a where a.a_id in (select b.id from tbl_b b inner join tbl_c c
on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate))) where rownum <= 2;
perhaps
delete from
(select * from tbl_a a where exists (select 1 from t_b b inner join tbl_c c
on b.b_a = c.c_a and b.b_b = c.c_b where c.insert_time <= trunc(sysdate))) where rownum <= 2;
As for whether to use IN or EXISTS, you need to select the record size of the result set that the subtable may return.
However, a suggestion was put forward by the development students.
Please change the constraint to(C_A, C_B, C_C)，SQL Then change to
delete FROM (select * from TBL_A a inner join TBL_B b on a.a_id = b.id inner join TBL_C c on b.b_a = c.c_a and b.b_b = c.c_b and c.c_c is null where c.c_date <= trunc(sysdate)-1) where ROWNUM <= 10;
//Where the c_c column is allowed to be null.
This SQL error ORA-01752 is due to the fact that null is a special value. When we use conditions, we use is null/is not null instead of = null. In other words, null and null are not equivalent, so such data is allowed.
create table tbl (a varchar2(1), b varchar2(1), c varchar2(1));
insert into tbl values('a', 'b', '');
insert into tbl values('a', 'b', '');
select * from tbl;
a b
a bThis writing corresponds to the range of ORA-01752 errors.
Summary:
(1) ORA-01752 errors are somewhat obscure from the description, mainly because they can understand the meaning of key-preserved tables, so that they can gradually understand the causes of errors.
(2) Any change in details in Oracle's official documents may imply some improvements, which can not be understood only by looking at the documents, but only by actual operation.
(3) If you think it's right, you should insist on it, even if it's a document, and look at it dialectically.
Thank you lastwinner and Huang Yong for their guidance. Seeing the essence through phenomena is a little experience learned this time.
Welcome to my personal Wechat Public Number: bisal's personal grocery store 