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