【書評:Oracle查詢優化改寫】第四章

【書評:Oracle查詢優化改寫】第四章

BLOG文檔結構圖

<a href="http://s3.51cto.com/wyfs02/M00/6D/05/wKioL1VarZbTZGFhAABkIErUbAQ308.jpg"></a>

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① check的特殊用法

② sql優化中使用merge語句代替update語句（重點）

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

目标庫：11.2.0.3  RHEL6.5

前3章的連結參考相關連接配接：

今天來寫寫這本書的第四章的内容，第四章主要講了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&gt; create table ttt(create_date  date  check(create_date &gt; sysdate));

create table ttt(create_date  date  check(create_date &gt; sysdate))

                                                        *

ERROR at line 1:

ORA-02436: date or system variable wrongly specified in CHECK constraint

09:41:56 SQL&gt; insert into (select empno,ename,hiredate from scott.emp where hiredate &lt;= sysdate with check option)

09:42:13   2  values ( 9999,'test',sysdate+1);

insert into (select empno,ename,hiredate from scott.emp where hiredate &lt;= sysdate with check option)

                                                    *

ORA-01402: view WITH CHECK OPTION where-clause violation

Elapsed: 00:00:00.12

09:42:14 SQL&gt; insert into (select empno,ename,hiredate from scott.emp where hiredate &lt;= 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&gt;

關于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&gt; set autot on;

10:28:05 SQL&gt; alter table lhr.emp_bk add dname varchar2(50) default 'noname';

表已更改。

已用時間:  00: 00: 01.23

10:30:04 SQL&gt; 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&gt; 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 |

   3 - access("DEPTNO"="A"."DEPTNO")

         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  sorts (memory)

10:33:32 SQL&gt; 

另外幾篇關于使用merge語句來優化的案例：

到此SQL查詢優化改寫第四章基本over，重點是對merge語句的領悟和掌握，尤其是哥列出的幾個案例，希望對做SQL優化的童鞋有所幫助。

...........................................................................................................................................................................................

     本文轉自lhrbest 51CTO部落格，原文連結：http://blog.51cto.com/lhrbest/1652718，如需轉載請自行聯系原作者