从ORA-01752的错误，透过现象看本质

这几天开发同学反映了一个问题，有一个Java写的夜维程序，用于每天定时删除历史过期数据，3月10日之前经过了内测，但这两天再次执行的时候，有一条SQL语句一直报ORA-01752的错误，由于近期做过一次开发库的迁移，从一个机房搬迁至另一个机房，而且开发同学确认这期间未变代码逻辑，所以怀疑是否和数据迁移有关，这个错误被测试同学提为了bug，卡在版本测试中，有可能造成进度延误，所以属于比较紧急的问题。

再来捋一下这问题的信息，
(1).报错的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).3月10日之前这条SQL可以执行，现在再执行就会报错，
ORA-01752: cannot delete from view without exactly one key-preserved table
(3).3月10日左右做过数据迁移，exp/imp导出导入数据。

首先，我们看下ORA-01752错误是什么意思，

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.

含义是不能从一个没有明确key-preserved表的视图中执行delete操作。
原因有三个，没有key-preserved表，多于一张key-preserved表，或者key-preserved表是一个非合并视图。
解决方法是重新定义视图，或者从基表中执行delete操作。

这解释比较懵，什么叫key-preserved表？为什么执行delete语句删除这些select多表关联形成的view需要有一个key-preserved表？

看看Oracle官方文档对key-preserved表是如何定义的，
(http://docs.oracle.com/cd/E11882_01/server.112/e25494/views.htm#i1006318)

key-preserved表是理解限制修改连接视图join view的基础。如果一张表的主键/唯一键是join连接结果集的主键/唯一键，那么这张表就叫做key-preserved表。因此key-preserved表的主键/唯一键在join连接过程中会被一直保留。

并非这张表的主键/唯一键一定要出现在select子句中，但若其出现在join连接的结果集中，则必须要满足作为这个结果集主键/唯一键的要求。

表的key-preserving属性不依赖于表中的实际数据。例如，如果emp表最多有一个雇员位于每一个部门，那么deptno字段在emp和dept的连接结果集中将会是一个唯一值，但是不会因为若存在这样的数据就定义dept是一张key-preserved表。

在这张视图中，emp是一张key-preserved表，因为empno是emp表的主键，也可以看出其是join连接结果集的主键。dept不是一张key-preserved表，因为尽管deptno是dept表中的主键，但他不是join连接结果集中的主键。

(http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT1514)

如果定义视图的FROM子句中有多张表或视图，那么这张视图就叫做join view连接视图。

可更新的join view连接视图，也叫做可修改的join view连接视图，包括两张或更多张基表或视图，允许执行DML操作。可更新视图的FROM子句中会有SELECT语句包含多张表，并且不会有WITH READ ONLY子句限制。

为了继承可更新，视图必须满足一些标准。例如，一条通用的规则就是，INSERT、UPDATE或DELETE操作一次只能影响一张基表。

USER_UPDATABLE_COLUMNS数据字典会返回上面创建的这张join view连接视图是可更新的，join view连接视图中所有可更新的列必须映射至key-preserved表的列上。key-preserved表是每行数据最多在结果集中出现一次的基表。department_id是departments表的主键，所以employees表中每行数据在结果集中最多只会出现一次，因此employees表是一张key-preserved表。departments表不是key-preserved表，因为其每行数据可能多次出现于结果集中。

简单用一个实验说明下，
实验一-假设emp表有以下数据，

employee_id, department_id 1,1 2,1 3,2

假设dept表有以下数据，且department_name字段不是主键，

department_id,department_name  1,'a'  1,'b' 2,'a'

那么，emp和dept组成的视图包含如下记录，

employee_id,department_id,department_name 1,1,'a'  1,1,'b'  2,1,'a'  2,1,'b' 3,2,'a'

可以看出emp表每条数据是可能重复的，因此emp表不是key-preserved表。

实验二-假设emp表有如下数据，

employee_id, department_id 1,1 2,1 3,2

假设dept表有如下数据，且department_name字段是主键，

department_id,department_name  1,'a'  2,'a'

那么，emp和dept表组成的视图有如下记录，

employee_id,department_id,department_name  1,1,'a'  2,1,'a' 3,2,'a'

可以看出emp表每条数据是不会重复的，因此emp表是一张key-preserved表。

(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语句，如果join结果集中有多张key-preserved表，则Oracle会删除FROM子句第一张表，不管其视图是否用WITH CHECK OPTION创建。

DML语句必须仅会影响一张基表。

从以上文档，总结下主要观点，多表关联的一个updatable join view视图，如果语法上允许删除，则Oracle只会删除其中一张基表，这张表就是key-preserved表，如果一张表的主键/唯一键是updatable join view视图连接结果集的主键/唯一键，那么这张表就叫做key-preserved表。

用实验来说明，

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 (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 ----------  1

此时可以执行删除语句，删除的是第一张表t_a的记录。

SQL> rollback; Rollback complete.  SQL> delete from (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 selected

此时删除语句可以执行，删除的是第一张表t_b的记录。

以上实验说明了，
(1).不像ORA-01752错误提示中所说的，“more than one key-preserved table”会导致这种错误，delete可以删除两张不同的key-preserved表。
(2).delete会删除select … from子句跟着的第一张表，如上例中，t_a或t_b谁是第一张表，则谁会被删除。

实验和ORA-01752的描述自相矛盾了，Tom其实在这篇文章中提到了这个问题(https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:184812348071)，指出这是从9i之后的DELETE行为，为此记录了bug，

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

这个链接已经失效了，但通过检索，其来源就是《Database Administrator’s Guide》的DELETE Statements and Join Views章节。

再看11.2、12.1和12.2三个版本这一章节的说明，叙述上略不同。
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指出join连接中有且仅有一张key-preserved表，才能执行连接视图的delete删除。

12.1和12.2叙述相同，对于大多数连接视图，join连接中有且仅有一张key-preserved表，才能执行连接视图的delete删除。

如下是11.2的原文，

HuangYong大师的一段解释很形象，

“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.“

这个bug则记录了此问题，“Bug 24921723 : DOCUMENTATION BUG - DELETE STATEMENTS AND JOIN VIEWS”

bug进一步说明了这个问题，文档中应该反映出这种行为。

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.

这个bug在11.2.0.3提出，12.1.0.2修复，就是在文档中增加了“For most join views”这一句，说明是有例外。

既然上面介绍了什么是ORA-01752错误，什么是key-preserved表，接下来我们模拟下开始的问题，

(1) 创建三张测试表，

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) 创建模拟数据，

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 PM

单独执行select … from子句，可以知道会返回一条记录，

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 PM

执行DELETE语句会报错ORA-01752，

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 table

此时为表TBL_B增加唯一约束，执行DELETE语句，继续报错，

SQL> alter table tbl_b add constraint unique_tbl_b_01 unique (b_a, b_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; (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 table

此时为表TBL_C增加唯一约束，执行DELETE语句，可正常删除，并且可知删除的是表TBL_A数据，

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 PM

此时为表TBL_C增加唯一约束，不同的是唯一约束包含了id字段，执行DELETE语句(未包含id字段)报错ORA-01752，说明不能确定key-preserved表，

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 table

换一种方式，创建和上面DELETE相同语义的视图，

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.

从user_updatable_columns视图中可以看出，原始视图所有列均不可增删改，

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.

增加TBL_C唯一约束，创建视图，

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.

此时user_updatable_columns视图中显示TBL_A表的a_id字段允许增删改了，

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.

通过以上实验，可以推测出这条DELETE语句是否执行成功，取决于TBL_B和TBL_C表是否有主键或者唯一键，进而取决于要删除的TBL_A表记录是否可以唯一确定结果集中的记录，是否是key-preserved表。

我们对开发库的每次变更，都是使用自己开发的一套数据库变更工具执行的，会自动记录变更历史，而且变更的细节，结合Confluence会有记录，TBL_C表3月10日之前，曾经创建过一个唯一约束，字段是(C_A, C_B)，3月10日，开发提出了一次变更需求，向这个唯一约束中增加了另外一个字段C_C，此时(C_A, C_B, C_C)可以唯一确定一条记录，(C_A, C_B)不能唯一确定一条记录。

举个例子，

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.

此时不能根据TBL_A、TBL_B或TBL_C的主键来确定join连接结果集的主键，因此无key-preserved表，Oracle不能明确需要删除的基表，所以报错ORA-01752。

解决方案：
(1). 为了删除TBL_A的数据，需要让其成为key-preserved表，因此除了TBL_B有主键限定唯一值，还需要让TBL_C的条件限定唯一值，例如可以再次修改唯一约束为(C_A，C_B)这两个字段，但这可能和业务需求不一致。
(2). 改写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;
或者
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;
至于使用IN还是EXISTS，需要结合子表可能返回的结果集记录大小再选择。

然而，开发同学提出了一种建议，

请将约束改为(C_A, C_B, C_C)，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 and c.c_c is null where c.c_date <= trunc(sysdate)-1) where ROWNUM <= 10;
其中c_c列允许为空。

这条SQL报错ORA-01752，原因就是因为null是一个特殊的值，我们使用条件的时候，会用is null/is not null，不会用=null，换句话说，null和null不是等价的，因此允许这样的数据，

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 b

这种写法符合ORA-01752错误的范围。

总结：
(1) ORA-01752错误从描述看会有些晦涩，主要是能理解key-preserved表的含义，才能逐步理解错误的原因。
(2) Oracle官方文档中任何一处细节的变化，可能蕴含着一些改进，只看文档是不能理解的，唯有实际操作才能理解含义。
(3) 认为对的就要坚持，即使是文档，辩证地看待问题。

要谢谢lastwinner和HuangYong两位大师的指点迷津，透过现象看本质，是这次学习到的一点经验。

欢迎关注我的个人微信公众号：bisal的个人杂货铺