Oracle SQL 查詢最佳化.Part4

標籤：oss   insert   class   img   text   ext   first   資訊   str   

一、插入 insert 操作：

1. 複製表結構但不新增資料：

-- 複製表結構但不插入資料create table emp_new as select * from emp where 1 = 2;select * from emp_new;

2. 利用 with check option，配合視圖，能夠為插入資料操作做一些限制：

-- with check optiom 限制資料的插入insert into (select empno, deptno, empname, empsalary, empdesc               from emp              where emp.deptno <> 'dept02'-- with check option            )values('emp008', 'dept02', 'Ross', 7000, '對行業發展趨勢有較強的洞察力。有統籌全域能力');

運行報錯：ORA-01402:視圖 WITH CHECK OPTIDN違反 where子句

3. 多表插入：

這裡講三種多表插入：a. 無條件 insert；b. 有條件 insert；c. insert first。

• 無條件 insert all

先將 emp 和 emp_bak 清空，再運行下邊 sql：

-- 無條件 insert allinsert allinto emp_new1into emp_new2select * from emp;

此語是將 emp 的資料同一時候插入到 emp_new1、emp_new2 表裡，運行 select * from emp_new1 的結果例如以下（emp_new2 的資料集也是如此）：

• 有條件 insert all
  

-- 有條件 insert allinsert allwhen empsalary < 5000   then into emp_new1when empsalary > 3000   then into emp_new2select * from emp;

運行上邊 sql 後，工資（empsalary）小於 5000 的員工資訊插入 emp_new1。工資（empsalary）大於 3000 的員工資訊插入 dept_new2。當中 empsalary 為 4000 的同一時候插入了 emp_new1 和 emp_new2，有時候須要插入指定的表。這個在下邊講：

• insert first

insert first  假設前邊有條件符合。後邊的表就不會插入相應的行：

-- insert firstinsert firstwhen empsalary < 5000   then into emp_new1when empsalary > 3000   then into emp_new2when empsalary > 3000   then into emp_new3select * from emp;

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" >

emp_new2 不會插入 empsalary 為 4000 的這條記錄，emp_new3 沒有記錄插入。

二、更新 update 操作：

1. 利用 select 子查詢進行 update。須要注意避免全表更新：

資料準備，先將 emp_new1 中全部記錄的 empdesc 置為 “未填寫”

如今準備依據 emp_new1 表中的記錄去更新 emp 表中 empno 相應記錄的 empdesc，好多人會寫成例如以下 sql：

-- 利用 select 進行 update 進行了全表更新update emp   set emp.empdesc =        (select empdesc          from emp_new1         where emp.empno = emp_new1.empno       );

上邊的結果說明，這個 update 操作的 sql 進行了全表掃描。

對 empno 沒有匹配到的記錄，均被更新為 null。

其實應該加上 where 語句才是正確的：

update emp   set emp.empdesc =        (select empdesc          from emp_new1         where emp.empno = emp_new1.empno       ) where exists (select 1          from emp_new1         where emp.empno = emp_new1.empno       )

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" >

2. merge into 語句：

針對上邊的語句，能夠用 merge into 語句

-- merge into 的實現方法merge into empusing (select *          from emp_new1      ) e   on (e.empno = emp.empno)when matched then  update set emp.empdesc = e.empdesc;

執行結果和 1 中結果一樣，假設做推斷。推薦使用 merge into 方法。

由於 merge into 僅僅訪問一次 emp_new1。

三、刪除 delete 操作

1. 刪除反覆記錄：

方法有非常多種，這裡僅僅說一種，利用分析函數分組。推斷 分組序號是否大於 1.

delete  from emp where rowid in (select rid                    from (select rowid as rid,                                row_number() over(partition by empsalary order by empno asc) as seq                           from emp                         )                   where seq > 1                 );

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" >

可是，假設須要刪除全然同樣的兩條資料中的一條，須要在 partition by 後邊加上全部列名，否則刪除哪條資料並不確定，刪掉記錄是和 order by 語句相關的。

Oracle SQL 查詢最佳化.Part4