with as的寫法, 有一個專有名詞, 叫common table expression, 簡稱CTE,很多開發人員把使用這種寫法當成一個提高SQL執行效率的方法, 事實究竟如何, 請往下看.
在 oracle優化教育訓練之<SQL寫法與改寫>第二期 的課程中, 我給學員講了with as的幾個用途,下面逐一介紹:
1. 通過materilize生成臨時表, 達到優化SQL目的.
比如一個大表(或一段查詢塊), 在SQL中被多次使用, 每次通路表, 都有一些共同的過濾條件, 過濾之後, 結果集變小, 這種情況就比較适合使用with as的寫法. 注意, 不要以為使用了with as, 就能提高性能, 使用不當反而會降低性能. 隻在特定情況下才能提升效率.
我的另一篇公衆号文章 記一個Enq: ss - contention性能問題處理 , 就是這樣一個案例, 不過這個案例的最佳解決方案是進一步改寫, 表隻需要被通路一次.
相關知識點:
如果with as 查詢塊被使用2次或兩次以上, 會自動做materialize, 如果不想被物化, 可以使用/*+ inline */的hint ;
如果查詢塊隻被調用一次, 預設不會做materialize, 可以用/*+ materialize*/的hint來使其物化生成臨時表.
注意:如果傳回列中含有lob字段, 不能被materialize.
在12.2及以上版本中, materialize 有個新特性, 叫CURSOR DURATION MEMORY, 可以把結果集儲存在記憶體中, 而不是寫到temp表空間, 可以提升效率.
2.提高代碼可讀性
使代碼有層次感, 增強可讀性, 下面是對連續編号進行分組的一個寫法:
with t as
( select rownum+1 as id from xmltable('1 to 5')
union
select rownum+10 from xmltable('1 to 4')
union
select rownum+20 from xmltable('1 to 2')
union
select rownum+30 from xmltable('1 to 3')
)
,mid1 as (select id,nvl(id-1-lag(id) over (order by id),0) as cal_id from t)
,mid2 as (select id,sum(cal_id) over (order by id) as sum1 from mid1)
select id,dense_rank() over (order by sum1) as group_no
from mid2
order by id;
對複雜的SQL, 都可以考慮使用這種寫法, 增強代碼的可讀性.
3.實作遞歸算法
下面是計算9以内階乘的寫法
WITH temp (n, fact) AS
(SELECT 0, 1 from dual
UNION ALL
SELECT n+1, (n+1)*fact FROM temp
WHERE n < 9
)
SELECT * FROM temp;
connect by層級查詢文法,很多都可以使用with as的遞歸寫法替代實作.
4. 12c新特性 inline function, SQL裡面可以内嵌函數
如下面示例(這個with , 沒有as):
WITH
function cal_new_sal(p_sal number, p_hiredate date)
return number
is
begin
return p_sal*(1+ceil((sysdate-p_hiredate)/365)*0.05);
end;
select empno,ename,sal,hiredate
,cal_new_sal(sal,hiredate) as new_sal
,1+ceil((sysdate-hiredate)/365)*0.05 as factor
from emp;
/
function代碼沒有儲存在資料庫中.
5.構造測試用例用臨時資料
前面提高代碼可讀性的示例已經用到了, 下面示例是在19c之前, 使用listagg并去重的一種寫法, with as的作用是生成了幾行測試記錄, 避免了建表和插入記錄的麻煩:
with test_data(col1,col2,created_by) as
(
select 1, 2, 'Smith' from dual
union all select 1, 2, 'John' from dual
union all select 1, 3, 'Ajay' from dual
union all select 1, 4, 'Ram' from dual
union all select 1, 5, 'Jack' from dual
union all select 2, 5, 'Smith' from dual
union all select 2, 6, 'John' from dual
union all select 2, 6, 'Ajay' from dual
union all select 2, 6, 'Ram' from dual
union all select 2, 7, 'Jack' from dual
)
SELECT col1 ,
listagg(col2 , ',') within group (order by col2 ASC) AS orig_value,
listagg(CASE WHEN rn=1 THEN col2 END , ',') within group (order by col2 ASC) AS distinct_value
from
(
select a.*,row_number() over (partition by col1,col2 order by 1) as rn from test_data a
) a
GROUP BY col1;
總結:
用的最多的功能應該是提高代碼可讀性; 利用 materialize 特性優化SQL, 也是開發人員需要考慮的.