select * from T_PEEKING a where b = :V
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.01 0.07 0 406 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.08 0 406 0 1000
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS FULL T_PEEKING (cr=406 pr=0 pw=0 time=5052 us)
但是,绑定变量窥视对一条语句只会使用一次。就是说,在第一次解析语句时,将绑定变量值考虑进去计算成本生成查询计划。以后在执行该语句时都采用这个查询计划,而不再考虑以后绑定变量的值是什么了。 SQL> conn sys/sys as sysdba
以下是引用片段:
Connected.
SQL>
SQL>
SQL> set autot trace
SQL>
SQL> alter session set sql_trace = true;
Session altered.
SQL>
SQL> var v char(1)
SQL>
SQL> exec :v := 'B';
PL/SQL procedure successfully completed.
SQL>
SQL> select * from T_PEEKING a where b = :V;
1000 rows selected.
SQL>
SQL> alter session set sql_trace = false;
Session altered.
再用Tkprof分析生成的trace文件,看到尽管这里的值是"B",选择索引扫描会更优,但分析结果中查询计划还是使用全表扫描: select *
以下是引用片段:
from
T_PEEKING a where b = :V
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 340 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 340 0 2
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS FULL T_PEEKING (cr=340 pr=0 pw=0 time=1005 us)
因此,这种情况下使用绑定变量也会导致无法选择最优的查询计划。
综上所述,我们可以得出一个结论:在对建有索引的字段(包括字段集),且字段(集)的集的势非常大时,使用绑定变量可能会导致查询计划错误,因而会使查询效率非常低。