Sql最佳化(十一) 避免對資料的重複掃描（1）

對資料的讀取操作是非常消耗資源的,如何減少對資料的掃描,是提升sql效率的一個重要方面,例如物化檢視技術。本篇介紹幾種sql寫法，分別是CASE expression/DML with returning clause /multitable insert。[@more@]

一、 用CASE EXPRESSION將多句查詢組合在一起
SELECT COUNT (*)
FROM employees
WHERE salary < 2000;
SELECT COUNT (*)
FROM employees
WHERE salary BETWEEN 2000 AND 4000;
SELECT COUNT (*)
FROM employees
WHERE salary>4000;

改寫成
SELECT COUNT (CASE WHEN salary < 2000
THEN 1 ELSE null END) count1,
COUNT (CASE WHEN salary BETWEEN 2001 AND 4000
THEN 1 ELSE null END) count2,
COUNT (CASE WHEN salary > 4000
THEN 1 ELSE null END) count3
FROM employees;

二、 DML with returning clause
有時候DML操作後緊跟著對這些行的select操作。DML with returning clause將insert/update/delete操作和select操作合併在一起，避免了重複掃描。
例子：
UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;
如果修改的是多行記錄，則returning clause返回到陣列變數中。

三、 multitable insert
一句sql包含多條insert的寫法。相比於多句insert語句，前者只需掃描源table一次，而後者要掃描多次。這在資料量很大的情況下效能差別非常大。Multitable insert可分為conditional(有條件的),unconditional,insert all（執行所有insert）,insert first(只執行第一個滿足條件的insert)等不同用法
有時候表裡面一行記錄包含了多類資訊，如某一話單表中包含了一個裝置多種話務型別的費用,這種表我們稱為非關係表。下面例子應用oracle multitable insert技術將非關係錶轉換為關係型表。
create table test_table (id1 number,val_1 number,id2 number,val_2 number); --非關係表
insert into test_table values(1,101,1,202);
insert into test_table values(6,666,7,777);

--基表
create table id_type (a number);
insert into id_type values(1);
insert into id_type values(2);
insert into id_type values(3);
--目標表（將非關係錶轉換為兩張關係表）
create table id1_table (id number,val number);
create table id2_table (id number,val number);

--普通方法，用多條insert語句
insert INTO id1_table select id1,val1 from test_table;
insert INTO id2_table select id2,val2 from test_table;

--用multiple insert，將非關係型表資料一條記錄拆成兩條,分別插入兩張目標表
insert all
INTO id1_table values(id1,val_1)
INTO id2_table values(id2,val_2)
select id1,val_1,id2,val_2 from test_table;

--有判斷條件的multiple insert
insert all
WHEN id1 =1 then
INTO id1_table values(id1,val_1)
when ID2 =5 then
INTO id2_table values(id2,val_2)
select id1,val_1,id2,val_2 from test_table;

--multiple insert還有更復雜的寫法，判斷條件可以用子查詢
insert all
WHEN id1 in(select a from id_type) then
INTO id1_table values(id1,val_1)
when ID2 in(select a from id_type) then
INTO id2_table values(id2,val_2)
select id1,val_1,id2,val_2 from test_table;