SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> select count(*) from t where object_type='TABLE';

COUNT(*)
----------
49143

SQL> select count(*) from t where object_type='INDEX';

COUNT(*)
----------
1
SQL> create index typeidx on t (object_type) nologging;

Index created.


SQL> analyze table t compute statistics for table for all indexes for all indexe
d columns;

表已分析。

SQL> alter system flush shared_pool;

系统已更改。

SQL> var r varchar(20);
用法: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR ] ]
SQL> var r varchar2(20);
SQL> exec :r:='TABLE';

PL/SQL 过程已成功完成。

SQL> set autotrace traceonly stat exp
SQL> select * from t where object_type=:r;

已选择49143行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=24572 Bytes=
2088620)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=59 Card=24572 Bytes=20886
20)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49143 rows processed

SQL> exec :r:='INDEX';

PL/SQL 过程已成功完成。

SQL> select * from t where object_type=:r;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=24572 Bytes=
2088620)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=59 Card=24572 Bytes=20886
20)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

CBO并没有选择索引，而采用全表扫描.oracle仅仅在第一次产生执行计划的时候使用histograms，之后随着变量的值的变化执行计划不会改变.所以在这种情况下绑定变量并不好.

SQL> alter system flush shared_pool;

系统已更改。
SQL> execute :v:='INDEX';

PL/SQL 过程已成功完成。

SQL> select * from t where object_type=:v;



Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=24572 Bytes=
2088620)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=59 Card=24572 Bytes=20886
20)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

这里可以看到刷新了shared_pool,不过EXPLAIN PLAN 显示的还是全表扫描.这里EXPLAIN PLAN显示的不正确,可以做一下10046的trace跟踪一下,就可以看到实际是INDEX (RANGE SCAN) .