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))
看到绑定变量的情况下,直方图没有起作用。