[20190825]Join View and delete.txt

[20190825]Join View and delete.txt

--//看了連結裡面提到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.

--//這樣就沒有問題了.