Oracle SQL 查詢最佳化.Part4

標籤：

一、插入 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;

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       )

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                 );

但是，如果需要刪除完全相同的兩條資料中的一條，需要在 partition by 後邊加上所有列名，否則刪除哪條資料並不確定，刪掉記錄是和 order by 語句相關的。

Oracle SQL 查詢最佳化.Part4