http://space.itpub.net/756652/viewspace-544642

SQL> variable aa number;

SQL> exec :aa:=90;

PL/SQL 过程已成功完成。

SQL> select * from tt where id=:aa;

ID MC
---------- --------------------
90 1

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TT'
2 1 INDEX (UNIQUE SCAN) OF 'TT_PK' (UNIQUE)

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

SQL> create or replace outline ol1 for category test_ol on
2 select * from tt where id=:aa;

大纲已创建。

SQL> create or replace outline ol2 for category test_ol on
2 select /*+FULL(tt)*/ * from tt where id=:aa;

大纲已创建。

SQL> desc dba_outlines
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
OWNER VARCHAR2(30)
CATEGORY VARCHAR2(30)
USED VARCHAR2(9)
TIMESTAMP DATE
VERSION VARCHAR2(64)
SQL_TEXT LONG
SIGNATURE RAW(16)

SQL> col name for a10
SQL> select name,used from dba_outlines;

NAME USED
---------- ---------
OL1 UNUSED
OL2 UNUSED

SQL> desc dba_outline_hints
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
OWNER VARCHAR2(30)
NODE NUMBER
STAGE NUMBER
JOIN_POS NUMBER
HINT VARCHAR2(512)

SQL> col hint for a20
SQL> col name for a10
SQL> select name,node,stage,join_pos,hint from dba_outline_hints;

NAME NODE STAGE JOIN_POS HINT
---------- ---------- ---------- ---------- --------------------
OL1 1 3 0 NO_EXPAND
OL1 1 3 0 ORDERED
OL1 1 3 0 NO_FACT(TT)
OL1 1 3 1 INDEX(TT TT_PK)
OL1 1 2 0 NOREWRITE
OL1 1 1 0 NOREWRITE
OL1 1 1 0 RULE
OL2 1 3 0 NO_EXPAND
OL2 1 3 0 ORDERED
OL2 1 3 0 NO_FACT(TT)
OL2 1 3 1 FULL(TT)

NAME NODE STAGE JOIN_POS HINT
---------- ---------- ---------- ---------- --------------------
OL2 1 2 0 NOREWRITE
OL2 1 1 0 NOREWRITE

已选择13行。

SQL> update outln.ol$hints
2 set ol_name =decode(ol_name,
3 'OL1','OL2',
4 'OL2','OL1')
5 where ol_name in ('OL1','OL2');

已更新13行。

SQL> update outln.ol$ ol1
2 set hintcount = (select hintcount from outln.ol$ ol2
3 where ol2.ol_name in ('OL1','OL2')
4 and ol2.ol_name != ol1.ol_name)
5 where ol1.ol_name in ('OL1','OL2');

已更新2行。

SQL> COMMIT;

提交完成。

SQL> select name,node,stage,join_pos,hint from dba_outline_hints;

NAME NODE STAGE JOIN_POS HINT
---------- ---------- ---------- ---------- --------------------
OL2 1 3 0 NO_EXPAND
OL2 1 3 0 ORDERED
OL2 1 3 0 NO_FACT(TT)
OL2 1 3 1 INDEX(TT TT_PK)
OL2 1 2 0 NOREWRITE
OL2 1 1 0 NOREWRITE
OL2 1 1 0 RULE
OL1 1 3 0 NO_EXPAND
OL1 1 3 0 ORDERED
OL1 1 3 0 NO_FACT(TT)
OL1 1 3 1 FULL(TT)

NAME NODE STAGE JOIN_POS HINT
---------- ---------- ---------- ---------- --------------------
OL1 1 2 0 NOREWRITE
OL1 1 1 0 NOREWRITE

已选择13行。

SQL> alter session set use_stored_outlines=test_ol;

会话已更改。

SQL> select name,used from dba_outlines;

NAME USED
---------- ---------
OL1 UNUSED
OL2 UNUSED

SQL> select * from tt where id=:aa;

ID MC
---------- --------------------
90 1

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=25)
1 0 TABLE ACCESS (FULL) OF 'TT' (Cost=2 Card=1 Bytes=25)

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

SQL> select name,used from dba_outlines;

NAME USED
---------- ---------
OL1 USED
OL2 UNUSED

10G以后可以用dbms_outln.CREATE_OUTLINE来创建:

BEGIN
DBMS_OUTLN.create_outline(
hash_value => 921600969,
child_number => 0,
category => 'TEST_OUTLINE');
END;
/

SELECT sql_id,hash_value,outline_category,sql_text FROM v$sqlarea WHERE hash_value=921600969;

参看metalink
Note:604022.1 How To Force A Query To Used Index Hint With Stored Outline
Note:730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline
Note:144194.1 Editing Stored Outlines in Oracle9i - an example