【書評:Oracle查詢優化改寫】第四章
BLOG文檔結構圖
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① check的特殊用法
② sql優化中使用merge語句代替update語句（重點）
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
目標庫：11.2.0.3 RHEL6.5
前3章的鏈接參考相關連接：
【書評:Oracle查詢優化改寫】第一章 http://blog.itpub.net/26736162/viewspace-1652985/
【書評:Oracle查詢優化改寫】第二章 http://blog.itpub.net/26736162/viewspace-1654252/
【書評:Oracle查詢優化改寫】第三章 http://blog.itpub.net/26736162/viewspace-1660422/
今天來寫寫這本書的第四章的內容，第四章主要講了UPDATE語句的正確用法，以及什麼時候UPDATE語句應改寫為MERGE， 第四章的內容目錄如下：
第 4 章 插入、更新與刪除
4.1 插入新記錄
4.2 阻止對某幾列插入
4.3 復制表的定義及數據
4.4 用 WITH CHECK OPTION 限制數據錄入
4.5 多表插入語句
4.6 用其他表中的值更新
4.7 合並記錄
4.8 刪除違反參照完整性的記錄
4.9 刪除名稱重復的記錄
我們知道sysdate不能用於check約束，但是有這種需求的時候怎麼辦呢？如下例子利用視圖加with check option即可解決。
09:39:08 SQL> create table ttt(create_date date check(create_date > sysdate));
create table ttt(create_date date check(create_date > sysdate))
*
ERROR at line 1:
ORA-02436: date or system variable wrongly specified in CHECK constraint
09:41:56 SQL> insert into (select empno,ename,hiredate from scott.emp where hiredate <= sysdate with check option)
09:42:13 2 values ( 9999,'test',sysdate+1);
insert into (select empno,ename,hiredate from scott.emp where hiredate <= sysdate with check option)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
Elapsed: 00:00:00.12
09:42:14 SQL> insert into (select empno,ename,hiredate from scott.emp where hiredate <= sysdate with check option)
09:42:56 2 values ( 9999,'test',sysdate-1);
1 row created.
Elapsed: 00:00:00.03
09:42:57 SQL>
關於update的一個容易出錯的地方就是不寫where子句，這樣的話會更新掉全表的數據，一個技巧就是把set中的值復制到where子句中即可。
另外，建議大家在做多表關聯更新的時候修改為merge語句，因為merge into語句只訪問了一次表：
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 5月 19 10:26:55 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
連接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
10:26:55 SQL> set autot on;
10:28:05 SQL> alter table lhr.emp_bk add dname varchar2(50) default 'noname';
表已更改。
已用時間: 00: 00: 01.23
10:30:04 SQL> update lhr.emp_bk a
10:30:09 2 set a.dname =(select b.dname from lhr.dept_bk b where b.deptno=a.deptno and b.dname in ('ACCOUNTING','RESERCH'))
10:30:09 3 WHERE EXISTS (select 1 from lhr.dept_bk b where b.deptno=a.deptno and b.dname in ('ACCOUNTING','RESERCH' ))
10:30:09 4 ;
已更新3行。
已用時間: 00: 00: 00.05
執行計劃
----------------------------------------------------------
Plan hash value: 3525057516
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 4 | 544 | 28 (18)| 00:00:01 |
| 1 | UPDATE | EMP_BK | | | | |
|* 2 | HASH JOIN SEMI | | 4 | 544 | 8 (13)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP_BK | 14 | 1596 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT_BK | 1 | 22 | 4 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT_BK | 1 | 22 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."DEPTNO"="A"."DEPTNO")
4 - filter("B"."DNAME"='ACCOUNTING' OR "B"."DNAME"='RESERCH')
5 - filter("B"."DEPTNO"=:B1 AND ("B"."DNAME"='ACCOUNTING' OR
"B"."DNAME"='RESERCH'))
Note
-----
- dynamic sampling used for this statement (level=2)
統計信息
----------------------------------------------------------
69 recursive calls
13 db block gets
121 consistent gets
9 physical reads
3012 redo size
837 bytes sent via SQL*Net to client
997 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
3 rows processed
已用時間: 00: 00: 00.00
10:33:13 SQL> merge into lhr.emp_bk a
10:33:32 2 using (select b.dname,deptno from lhr.dept_bk b where b.dname in ('ACCOUNTING','RESERCH')) bb
10:33:32 3 on (bb.deptno=a.deptno)
10:33:32 4 when matched then
10:33:32 5 update set a.dname =bb.dname
10:33:32 6 ;
3 行已合並。
已用時間: 00: 00: 00.03
執行計劃
----------------------------------------------------------
Plan hash value: 1386289611
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 4 | 492 | 8 (13)| 00:00:01 |
| 1 | MERGE | EMP_BK | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 4 | 592 | 8 (13)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT_BK | 1 | 22 | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP_BK | 14 | 1764 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"="A"."DEPTNO")
4 - filter("B"."DNAME"='ACCOUNTING' OR "B"."DNAME"='RESERCH')
Note
-----
- dynamic sampling used for this statement (level=2)
統計信息
----------------------------------------------------------
20 recursive calls
7 db block gets
38 consistent gets
1 physical reads
1872 redo size
838 bytes sent via SQL*Net to client
942 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
3 rows processed
10:33:32 SQL>
另外幾篇關於使用merge語句來優化的案例：
update修改為merge（max+decode） ：http://blog.itpub.net/26736162/viewspace-1244055/
采用merge語句的非關聯形式再次顯神能 ：http://blog.itpub.net/26736162/viewspace-1222423/
采用MERGE 語句的非關聯形式提升性能 ：http://blog.itpub.net/26736162/viewspace-1218671/
采用MERGE語句的非關聯形式提升性能 ---後傳 ：http://blog.itpub.net/26736162/viewspace-1222417/
走了索引為啥還像蝸牛一樣： http://blog.itpub.net/26736162/viewspace-1208814/
到此SQL查詢優化改寫第四章基本over，重點是對merge語句的領悟和掌握，尤其是哥列出的幾個案例，希望對做SQL優化的童鞋有所幫助。
...........................................................................................................................................................................................
本文作者：小麥苗，只專注於數據庫的技術，更注重技術的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1661906/
本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w 提取碼：af2d
QQ：642808185 若加QQ請注明你所正在讀的文章標題
創作時間地點：2015-05-19 09:00~ 2015-05-19 11:20 於外匯交易中心
<版權所有，文章允許轉載，但須以鏈接方式注明源地址，否則追究法律責任!>
...........................................................................................................................................................................................
