[20190825]Join View and delete.txt

[20190825]Join View and delete.txt --//看了链接https://jonathanlewis.wordpress.com/2019/08/20/join-view/,里面提到delete可能引起的错误,自己也测试看看. --//顺便说一下,我自己也在维护中也喜欢这样方式,但是我从来不是多表连接操作.我一般操作单表. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING          VERSION    BANNER                                                                       CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0 create table source as select level n1 from dual connect by level <= 10 / create table search as select level n1 from dual connect by level <= 10 / alter table source modify n1 not null; alter table search modify n1 not null; create unique index search_idx on search(n1); -- create unique index source_idx on source(n1); --//分析略. 2.测试1: --//测试脚本如下: $ cat jv1.txt prompt  =============================== prompt  Source referenced first in ANSI prompt  =============================== prompt delete from (select * from source s join search s1 on s.n1 = s1.n1); delete from (select * from source s join search s1 on s.n1 = s1.n1); select count(1) source_count from source; select count(1) search_count from search; rollback; $cat jv2.txt prompt  =============================== prompt  Search referenced first in ANSI prompt  =============================== prompt delete from (select * from search s join source s1 on s.n1 = s1.n1); delete from (select * from search s join source s1 on s.n1 = s1.n1); select count(1) source_count from source; select count(1) search_count from search; rollback; --//开始测试: SCOTT@test01p> @ jv1.txt =============================== Source referenced first in ANSI =============================== delete from (select * from source s join search s1 on s.n1 = s1.n1) 10 rows deleted. SOURCE_COUNT ------------            0 SEARCH_COUNT ------------           10 Rollback complete. SCOTT@test01p> @ jv2.txt =============================== Search referenced first in ANSI =============================== delete from (select * from search s join source s1 on s.n1 = s1.n1) 10 rows deleted. SOURCE_COUNT ------------            0 SEARCH_COUNT ------------           10 Rollback complete. --//可以发现在这样情况下删除的表search.主要是表search有唯一索引.source没有索引. 3.测试2: SCOTT@test01p> create unique index source_idx on source(n1); Index created. SCOTT@test01p> @ jv1.txt =============================== Source referenced first in ANSI =============================== delete from (select * from source s join search s1 on s.n1 = s1.n1) 10 rows deleted. SOURCE_COUNT ------------            0 SEARCH_COUNT ------------           10 Rollback complete. SCOTT@test01p> @ jv2.txt =============================== Search referenced first in ANSI =============================== delete from (select * from search s join source s1 on s.n1 = s1.n1) 10 rows deleted. SOURCE_COUNT ------------           10 SEARCH_COUNT ------------            0 Rollback complete. --//可以发现对source建立唯一索引后,脚本jv1.txt中删除的表source. --//脚本jv2.txt中删除的表search. 4.测试3: --//加入提示: prompt  ============================================ prompt  Source hinted as leading table in join order prompt  ============================================ prompt delete from ( select /*+ leading(s1, s) */ * from search s, source s1 where s.n1 = s1.n1) ; delete from ( select /*+ leading(s1, s) */ * from search s, source s1 where s.n1 = s1.n1) ; select count(1) source_count from source; select count(1) search_count from search; rollback; prompt  ============================================ prompt  Search hinted as leading table in join order prompt  ============================================ prompt delete from ( select /*+ leading(s, s1) */ * from search s, source s1 where s.n1 = s1.n1) ; delete from ( select /*+ leading(s, s1) */ * from search s, source s1 where s.n1 = s1.n1) ; select count(1) source_count from source; select count(1) search_count from search; rollback; SCOTT@test01p> @ jv3.txt ============================================ Source hinted as leading table in join order ============================================ delete from ( select /*+ leading(s1, s) */ * from search s, source s1 where s.n1 = s1.n1) 10 rows deleted. SOURCE_COUNT ------------           10 SEARCH_COUNT ------------            0 Rollback complete. ============================================ Search hinted as leading table in join order ============================================ delete from ( select /*+ leading(s, s1) */ * from search s, source s1 where s.n1 = s1.n1) 10 rows deleted. SOURCE_COUNT ------------           10 SEARCH_COUNT ------------            0 Rollback complete. --//我开始以为这样测试结果会不同,实际上这两个提示删除的都是表search.视乎是连接时那个表在前面dml操作的就是那个表. --//总之这样连接操作要小心,我一般不这样写dml语句. 5.不过当我测试时还是无法理解一些细节: $ cat jv4.txt prompt  =============================== prompt  Source referenced first in ANSI prompt  =============================== prompt delete from (select s1.* from source s join search s1 on s.n1 = s1.n1); delete from (select s1.* from source s join search s1 on s.n1 = s1.n1); select count(1) source_count from source; select count(1) search_count from search; rollback; --//我仅仅提取的是search表(s1)内容,而实际上还是删除表source.视乎这样操作与提取字段无关. SCOTT@test01p> @ jv4.txt =============================== Source referenced first in ANSI =============================== delete from (select s1.* from source s join search s1 on s.n1 = s1.n1) 10 rows deleted. SOURCE_COUNT ------------            0 SEARCH_COUNT ------------           10 Rollback complete. --//而实际上依旧还是操作的是表source. 6.看来以后这类操作要小心,我一般个人喜欢单表操作: $ cat jv5.txt prompt  =============================== prompt  using exists and delete search prompt  =============================== prompt delete from (select s1.* from search s1 where exists ( select 1 from source s where s.n1 = s1.n1)); delete from (select s1.* from search s1 where exists ( select 1 from source s where s.n1 = s1.n1)); select count(1) source_count from source; select count(1) search_count from search; rollback; prompt  =============================== prompt  using exists and delete source prompt  =============================== prompt delete from (select s.* from source s where exists ( select 1 from search s1 where s.n1 = s1.n1)); delete from (select s.* from source s where exists ( select 1 from search s1 where s.n1 = s1.n1)); select count(1) source_count from source; select count(1) search_count from search; rollback; SCOTT@test01p> @ jv5.txt =============================== using exists and delete search =============================== delete from (select s1.* from search s1 where exists ( select 1 from source s where s.n1 = s1.n1)) 10 rows deleted. SOURCE_COUNT ------------           10 SEARCH_COUNT ------------            0 Rollback complete. =============================== using exists and delete source =============================== delete from (select s.* from source s where exists ( select 1 from search s1 where s.n1 = s1.n1)) 10 rows deleted. SOURCE_COUNT ------------            0 SEARCH_COUNT ------------           10 Rollback complete. --//这样就没有问题了.