bind peeking(绑定变量窥视)

1.创建测试表
SQL> create table system.wh(num1 number(10),num2 number(10));


Table created.


SQL> begin
  2          for i in 1 .. 10000 loop        
  3                 if i < 9997 then
  4                   insert into system.wh values(100,i);
  5                   commit;
  6            else
  7                   insert into system.wh values(1000,i);   
  8                   commit; 
  9               end if;         
 10         end loop;
 11      end;
 12      /


PL/SQL procedure successfully completed.


SQL> create index system.ll on system.wh(num1);


Index created.


SQL> execute dbms_stats.gather_table_stats('SYSTEM','WH');


PL/SQL procedure successfully completed.


2.测试情况一


当 num1=100 的情况
原理上应该是走全表扫更优


我们设置变量为 100 


SQL> variable n number;  
SQL> exec :n := 100;  




 select  count(num2) from system.wh n100 where num1=:n;


COUNT(NUM2)
-----------
          4


查看执行计划


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bch86dxzkn3rz, child number 0
-------------------------------------
select   count(num2) from system.wh n100
where num1=:n


Plan hash value: 1206455612


--------------------------------------------------------------------------------
--------------


| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ers | Reads  |


--------------------------------------------------------------------------------
--------------


|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |
 22 |     21 |


|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |
 22 |     21 |




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| WH   |      1 |   5000 |      4 |00:00:00.01 |
 22 |     21 |


--------------------------------------------------------------------------------
--------------




Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("NUM1"=:N)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------




20 rows selected.


SQL> SQL> exec :n := 1000;  


PL/SQL procedure successfully completed.


SQL> 
SQL> select count(num2) from system.wh n100 where num1=:n;


COUNT(NUM2)
-----------
          4


查看执行计划

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bch86dxzkn3rz, child number 0
-------------------------------------
select  count(num2) from system.wh n100
where num1=:n


Plan hash value: 1206455612


--------------------------------------------------------------------------------
-----


| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ers |


--------------------------------------------------------------------------------
-----


|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |
 22 |


|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |
 22 |




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| WH   |      1 |   5000 |      4 |00:00:00.01 |
 22 |


--------------------------------------------------------------------------------
-----




Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("NUM1"=:N)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------




20 rows selected.


大家可以看到 当num1 为 100时，table access full 是正确的,但当 num1 为 1000时，本应走索引的,却也 table access full
相同道理 当num1为1000时,走索引是正确的,但当num1 为100时，本应走全表,却表了索引.
这个就是绑定变量窥视

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7569309/viewspace-2134402/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7569309/viewspace-2134402/