关于OUTLINE的使用方法请参见文章《【OUTLINE】使用Oracle Outline技术暂时锁定SQL的执行计划》（http://space.itpub.net/519536/viewspace-673010）。

我们这里测试环境不具备OUTLINE中记录的执行计划时的执行效果。

1.环境准备
1）创建用户secooler，并授予适当权限，注意，用户需要具有create any outline权限
sys@ora10g> create tablespace tbs_secooler_d datafile '/oracle/ora10gR2/oradata/ora10g/tbs_secooler_d_01.dbf' size 10m autoextend on;

Tablespace created.

sys@ora10g> create user secooler identified by secooler default tablespace TBS_SECOOLER_D;

User created.

sys@ora10g> grant connect,resource to secooler;

Grant succeeded.

sys@ora10g> grant create any outline to secooler;

Grant succeeded.

2）在secooler用户中创建表T
sys@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> create table t as select * from all_objects;

Table created.

secooler@ora10g> select count(*) from t;

COUNT(*)
----------
      4370

3）在T表中创建索引
secooler@ora10g> create index i_t on t(object_id);

Index created.

4）此时的执行计划
secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |   128 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T    |     1 |   128 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=258)

Note
-----
   - dynamic sampling used for this statement

2.创建OUTLINE
1）解锁OUTLN用户
sys@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter user outln identified by outln account unlock;

User altered.

2）创建一个outline，取名叫做t_outln1，指定它的category名字为CATEGORY_T
outln@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> create outline t_outln1 for category CATEGORY_T on select * from t where OBJECT_ID=258;

Outline created.

此时创建了一个访问表T时走索引的OUTLINE。

3.使用OUTLINE
1）调整当前session使用CATEGORY_T这个OUTLINE
secooler@ora10g> alter session set use_stored_outlines=CATEGORY_T;

Session altered.

（2）查看此时SQL的执行计划
secooler@ora10g> set autotrace traceonly explain;
secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    50 | 6400 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T    |    50 | 6400 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T |    20 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=258)

Note
-----
   - outline "T_OUTLN1" used for this statement

从执行计划上可以看到此时该SQL使用到了索引。

4.将T表索引删除测试使用OUTLINE效果
secooler@ora10g> drop index i_t;

Index dropped.

secooler@ora10g> alter session set use_stored_outlines=CATEGORY_T;

Session altered.

secooler@ora10g> set autotrace traceonly explain;
secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    50 | 6400 |    15   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |    50 | 6400 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=258)

Note
-----
   - outline "T_OUTLN1" used for this statement

结论已经揭晓：
当表及索引信息不满足OUTLINE记录的执行计划时，Oracle并不会报错，而是走其他执行计划以便能够返回正确执行结果。

5.小结
OUTLINE并非“强制”按照记录的执行计划行事，当满足条件时会按照OUTLINE中的执行计划执行，当环境不满足时，也不会报错，SQL将选择其他的执行计划。

Good luck.

secooler
11.07.03

-- The End --

【OUTLINE】环境不满足OUTLINE记录的执行计划时会选择其他执行计划