outline使用简介
下边的语句是在没有索引的情况下的执行计划

SQL> conn yallonking/yallonking
Connected.
SQL> set autot traceonly
SQL> set line 400
SQL> select * from yallonking where object_id=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 2582482548

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     8 |  1416 |   158   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| YALLONKING |     8 |  1416 |   158   (2)| 00:00:02 |
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=20)

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


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

我们将一个没有索引的查询存储在outline的category中

SQL> create or replace outline yallonking for category yallonking on select * from yallonking where object_id=20;

Outline created.

创建一个索引

SQL> create index idx_yallonking on yallonking(object_id);

Index created.

再次查看该sql执行计划

SQL> set autot traceonly
SQL> select * from yallonking where object_id=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 4208659539

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

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

   2 - access("OBJECT_ID"=20)

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


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

启用outline category

SQL> ALTER SESSION SET USE_STORED_OUTLINES = yallonking;

Session altered.

再次查看该sql执行计划

SQL> select * from yallonking where object_id=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 2582482548

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   575 |    99K|   158   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| YALLONKING |   575 |    99K|   158   (2)| 00:00:02 |		//执行计划保持以前的
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=20)

Note
-----
   - outline "YALLONKING" used for this statement		//此处使用了outline category


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

下边取消outline，执行计划恢复正常

SQL> ALTER SESSION SET USE_STORED_OUTLINES = false;

Session altered.
		  
SQL> select * from yallonking where object_id=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 4208659539

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

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

   2 - access("OBJECT_ID"=20)

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


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

查看outline

SQL> SELECT NAME, SQL_TEXT 
  2    FROM USER_OUTLINES 
  3    WHERE CATEGORY='YALLONKING';

NAME                           SQL_TEXT
------------------------------ --------------------------------------------------------------------------------
YALLONKING                     select * from yallonking where object_id=20