SQL优化经典案例----RLS(ROW LEVEL SECURITY)

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