-----
![\]()
|
0 | SELECT STATEMENT | | | |
11 (
100) |
![\]()
|
1 | TABLE ACCESS BY INDEX ROWID| T1 |
1365 |
28665|
11 (
0) |
00:
00:
01
![\]()
|*
2 | INDEX RANGE SCAN | IDX_T1 |
1365 | |
3 (
0) |
00:
00:
01
![\]()
--------------------------------------------------------------------------------
![\]()
......
![\]()
hr@ora10g> exec :v_id :=
1;
![\]()
hr@ora10g> select * from t1 where id=:v_id;
![\]()
此处省略查询结果
![\]()
hr@ora10g > begin
![\]()
2 select sql_id into :v_sql_id from v$sql
![\]()
3 where sql_text like 'select * from t1 where id=:v_id%';
![\]()
4 end;
![\]()
5 /
![\]()
hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));
![\]()
PLAN_TABLE_OUTPUT
![\]()
--------------------------------------------------------------------------------
![\]()
SQL_ID djwq30cpbcz7k, child number
0
![\]()
-------------------------------------
![\]()
select * from t1 where id=:v_id
![\]()
Plan hash value:
50753647
![\]()
--------------------------------------------------------------------------------
![\]()
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
![\]()
--------------------------------------------------------------------------------
![\]()
|
0 | SELECT STATEMENT | | | |
11 (
100) |
![\]()
|
1 | TABLE ACCESS BY INDEX ROWID| T1 |
1365 |
28665|
11 (
0) |
00:
00:
01
![\]()
|*
2 | INDEX RANGE SCAN | IDX_T1 |
1365 | |
3 (
0) |
00:
00:
01
![\]()
--------------------------------------------------------------------------------
![\]()
从上面结果可以看出，在为绑定变量传入第一个值为13871时，由于返回的记录条数较少，导致走索引扫描。当我们第二次传入绑定变量值1时，oracle不再生成新的执行计划，而直接拿索引扫描的执行路径来用。
但是，如果先传入1的绑定变量值，然后再传入13871的绑定变量值时，会怎样？我们继续测试。
hr@ora10g> alter system flush shared_pool;
hr@ora10g> set autotrace traceonly exp stat;
hr@ora10g> exec :v_id := 1;
hr@ora10g> select * from t1 where id=:v_id;
hr@ora10g > begin
2 select sql_id into :v_sql_id from v$sql
3 where sql_text like 'select * from t1 where id=:v_id%';
4 end;
5 /
hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID djwq30cpbcz7k, child number 0
-------------------------------------
select * from t1 where id=:v_id
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| |
|* 1 | TABLE ACCESS FULL | T1 | 8738 | 179K | 13 (0) | 00:00:01 |
--------------------------------------------------------------------------
......
hr@ora10g > exec :v_id := 13871;
hr@ora10g > select * from t1 where id=:v_id;
hr@ora10g > begin
2 select sql_id into :v_sql_id from v$sql
3 where sql_text like 'select * from t1 where id=:v_id%';
4 end;
5 /
hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID djwq30cpbcz7k, child number 0
-------------------------------------
select * from t1 where id=:v_id
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| |
|* 1 | TABLE ACCESS FULL | T1 | 8738 | 179K | 13 (0) | 00:00:01 |
----------