java生成一个矩阵outline_转载一个朋友的关于outline的使用

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[@more@]