SQL優化經典案例----RLS(ROW LEVEL SECURITY)
---轉載http://blog.sina.com.cn/s/blog_61cd89f60102efd2.html
最近看到一個SQL優化牛人部落格，上面例子堪稱經典，唯一不足的時候，好多分析都是點到為止，并沒有說其中的原理，有點像EYLE網站，不過這樣也好，畢竟網上得來終覺淺，看過後自己進行分析，掌握的更加牢固，看一個經典例子。
具體前後說明詳見：http://blog.csdn.net/robinson1988/article/details/8644565 從落落的這個部落格上隻能看到這個查詢變快了，但是他并沒有說這個做的原因，原理就在hint上，明白該hint的原理也就了解為什麼要這樣做了，說一下hint no_unnest原理：
hint no_unnest和unnest是一對，unnest是強制子查詢進行展開，就是讓子查詢不在孤單的嵌套在nest loop裡面，那麼no_unnest剛好和它相反，強制子查詢嵌套在nest loop裡面，原理就這麼簡單，關鍵在于在什麼場合适用，為了搞清楚，我做了如下實驗
create table wxw1 as select * from dba_objects;
create table wxw2 as select * from dba_objects;
SQL> exec dbms_stats.gather_table_stats(user,'wxw1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'wxw2');
PL/SQL procedure successfully completed.
select wxw1.object_id
from wxw1
where exists
(select 1 from wxw2 where wxw1.object_id = wxw2.object_id * 10);
7009 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 120165691
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | | 692 (1)| 00:00:09 |
|* 1 | HASH JOIN SEMI | | 1 | 10 | 1200K| 692 (1)| 00:00:09 |
| 2 | TABLE ACCESS FULL| WXW1 | 71998 | 351K| | 287 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| WXW2 | 71999 | 351K| | 287 (1)| 00:00:04 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("WXW1"."OBJECT_ID"="WXW2"."OBJECT_ID"*10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2523 consistent gets --注意此處的邏輯讀
0 physical reads
0 redo size
127923 bytes sent via SQL*Net to client
5656 bytes received via SQL*Net from client
469 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7009 rows processed
這裡子查詢自動展開（unnest），即wxw2和wxw1 hash join在一起，接下來如果我們不希望wxw2展開，想先讓它單獨的執行完，然後再來和外部查詢進行一種叫做FILTER的操作，那麼我們加入hint no_unnest:
select wxw1.object_id
from wxw1
where exists (select 1
from wxw2
where wxw1.object_id = wxw2.object_id * 10);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 122K (1)| 00:24:31 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| WXW1 | 71998 | 351K| 287 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| WXW2 | 1 | 5 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "WXW2" "WXW2"
WHERE "WXW2"."OBJECT_ID"*10=:B1))
3 - filter("WXW2"."OBJECT_ID"*10=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
67282878 consistent gets --邏輯讀大了N倍
0 physical reads
0 redo size
127923 bytes sent via SQL*Net to client
5656 bytes received via SQL*Net from client
469 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7009 rows processed
對比兩個執行結果中的邏輯讀，由添加前的2523--> 67282878，看來該hint并不是所有場合都适用，為什麼邏輯讀變得如此大？因為這裡wxw1和wxw2進行了一種FILTER操作，他其實很像我們熟悉的neested loop，但它的獨特之處在于會維護一個hash table，舉例，如果wxw1裡取出object_id=1，那麼對于wxw2來說即select 1 from wxw2 where wxw2.object_id*10=1,如果條件滿足，那麼對于子查詢，輸入輸出對，即為(（wxw1.object_id）,1（常量）)，他存儲在hash table裡，并且由于條件滿足，wxw1.object_id=1被放入結果集，然後接着從wxw1取出object_id=2，如果子查詢依舊條件滿足，那麼子查詢産生另一個輸入和輸出，即(2,1)，被放入hash table裡；并且wxw1.object_id=2被放入結果集。接着假設wxw1裡有重複的object_id，例如我們第三次從wxw1取出的object_id=2,那麼由于我們對于子查詢來說，已經有輸入輸出對(2,1)在hash table裡了，是以就不用去再次全表掃描wxw2了，那麼在這種情況下，filter和neested loop相比,省去了一次全表掃描wxw2。這個hash table是有大小限制的，當被占滿的時候，後續新的wxw.object_id的FILTER就類似neested loop了。
經過上面的分析我們得出一個這樣的結論：從buffer gets層面上來看，FILTER是應該優于neested loop的，尤其當外部查詢需要傳遞給子查詢的輸入（此例中為wxw1.object_id）的distinct value非常小時，FILTER就會顯得更優，即使在我這個例子中wxw1.object_id的distinct value上萬，其結果應該是neested loop和FILTER，FILTER仍然略優，接下來我們進行實驗驗證。
SQL> select wxw1.object_id from wxw1,wxw2 where wxw1.object_id=wxw2.object_id*10;
7009 rows selected.
Elapsed: 00:16:58.99
Execution Plan
----------------------------------------------------------
Plan hash value: 3886856525
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71999 | 703K| 20M (1)| 68:29:04 |
| 1 | NESTED LOOPS | | 71999 | 703K| 20M (1)| 68:29:04 |
| 2 | TABLE ACCESS FULL| WXW1 | 71998 | 351K| 287 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| WXW2 | 1 | 5 | 285 (0)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("WXW1"."OBJECT_ID"="WXW2"."OBJECT_ID"*10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
74159892 consistent gets
0 physical reads
0 redo size
127923 bytes sent via SQL*Net to client
5656 bytes received via SQL*Net from client
469 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7009 rows processed
我們對比一下實驗二和實驗三的邏輯讀，發現
實驗二邏輯讀為：67282878
實驗三邏輯讀為：74159892
對比發現，實驗二添加hint no_unnest邏輯讀略優于nested loop，驗證了我們前面的猜測
是以我們再返過來看看落落的案例，子查詢是查詢使用者權限，權限個數必定很少，因為FILTER的效率要好于nested loop，這也就為什麼落落如選擇此hint的原因。連接配接中優化後的SQL如下：
SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (select b.*
from os_form_base b, ttwo_wo_info t
where t.form_id = b.form_id
and site_code in
(select
resCode
from (select *
from (select dept_code as resCode,
dept_name as resName
from sec_dept)
WHERE 1 != 1
OR ((1 = 1 and
resCode in
('0001030102',
'0001030105',
--此處省略N行..........
'000103010501',
'00010301190701')) or (1 != 1))
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)