Oracle绑定变量窥探(2)

zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T1',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);

 

PL/SQL procedure successfully completed.

执行如下两个sql并查看Oracle对SQL的解析情况

zx@MYDB>select count(*) from t1 where object_id between 999 and 1000;

 

  COUNT(*)

----------

     2

 

zx@MYDB>select count(*) from t1 where object_id between 999 and 60000;

 

  COUNT(*)

----------

     58180

 

zx@MYDB>col sql_text for a80

zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1 %';

 

SQL_TEXT                                     SQL_ID               VERSION_COUNT EXECUTIONS

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

select count(*) from t1 where object_id between 999 and 1000           5gu397922cuqd                     1    1

select count(*) from t1 where object_id between 999 and 60000           b8xxw70vja3tn                     1    1

从查询结果可以看出，Oracle在执行上述SQL时都使用了硬解析。Oracle分别为上述两个SQL各自生成了一个Parent Cursor和一个Child Cursor。

再查看执行计划：

从执行计划可以看出between 999 and 1000条件的SQL走的是索引范围扫描，而between 999 and 60000走的执行计划是索引快速全扫描。

现在我们将全面的两个SQL改造成使用绑定变量的等价形式。定义两个绑定变量x和y，并分别给它们赋值999和1000。

zx@MYDB>var x number;

zx@MYDB>var y number;

zx@MYDB>exec :x := 999;

 

PL/SQL procedure successfully completed.

 

zx@MYDB>exec :y := 1000;

 

PL/SQL procedure successfully completed.

显然，此时用绑定变量x和y的改写形式“between :x and :y”与原来的“between 999 and 1000”是等价的。而且只要将y重新赋值为60000，则又和“between 999 and 60000”等价了。

现在x和y的值分别为999和100，执行改写后的sql

zx@MYDB>select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

     2

 

zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1 %';

 

SQL_TEXT                                     SQL_ID               VERSION_COUNT EXECUTIONS

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

select count(*) from t1 where object_id between 999 and 1000           5gu397922cuqd                     1    1

select count(*) from t1 where object_id between 999 and 60000           b8xxw70vja3tn                     1    1

select count(*) from t1 where object_id between :x and :y           9dhu3xk2zu531                     1    1

从上述查询结果可以看到，Oracle在第一次执行上述等价SQL时也是用的硬解析