直方图对绑定变量sql的影响

来源：互联网发布：虚拟网络传销中的三虚编辑：程序博客网时间：2024/04/30 14:32

SQL> begin
2 for i in 1 .. 10000 loop
3 insert into t2 values(i,2);
4 end loop;
5 end;

6 /

SQL> insert into t2 values(10001,1);

已创建 1 行。

SQL> commmit;

无直方图信息

exec dbms_stats.gather_table_stats(ownname => 'baixyu',tabname => 'T2',method_opt => 'for all columns size 1');

SQL> create index i_t2_b on t2(b);

索引已创建。

SQL> select * from t2 where b=2;

已选择10000行。

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 35007 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 5001 | 35007 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------

看到无直方图的时候，oracle认为列是均匀的，返回的rows是5001行

收集下直方图

exec dbms_stats.gather_table_stats(ownname => 'baixyu',tabname => 'T2',method_opt => 'for all columns size 2');

SQL> select * from t2 where b=2;

已选择10000行。

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 70000 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 10000 | 70000 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------

这回返回的数据量是对的了，

SQL> select * from t2 where b=1;

执行计划
----------------------------------------------------------
Plan hash value: 4098184276

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

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

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 2 (0)| 00:0
0:01 |

|* 2 | INDEX RANGE SCAN | I_T2_B | 1 | | 1 (0)| 00:0
0:01 |

=1的时候也是走了索引的

下面在绑定变量的sql中看下执行计划的情况

SQL> var x number;
SQL> exec :x:=1;

PL/SQL 过程已成功完成。

SQL> print :x

SQL> set serveroutput on
SQL> print :x

SQL> exec select 1 into :x from dual;

PL/SQL 过程已成功完成。

SQL> print :x;

SQL> set autotrace off
SQL> print :x;

X
----------
1

SQL> select * from t2 where b=:x;

A B
---------- ----------
10001 1

SQL> explain plan for select * from t2 where b=:x;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 35007 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 5001 | 35007 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

1 - filter("B"=TO_NUMBER(:X))

已选择13行。

SQL> exec :x:=2;

PL/SQL 过程已成功完成。

SQL> print :x;

X
----------
2

SQL> explain plan for select * from t2 where b=:x;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 35007 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 5001 | 35007 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

1 - filter("B"=TO_NUMBER(:X))

看到绑定变量的情况下，直方图没有起作用。

0 0