利用hints控制outline

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-544642/，如需轉載，請註明出處，否則將追究法律責任。

利用hints控制outline

相關文章