outline创建使用

--outline现在已经被sql profile替代，但还是学习下

--

准备工作 ：

赋权限：grant create any outline to aiki;

SQL> show parameter stored

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string

SQL> show parameter stored

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string

SQL> alter system set create_stored_outlines=aiki_outline(后面创建）;

System altered.

SQL>
SQL>  create or replace outline outline1 for category aiki_outline

SQL>
SQL> show parameter stored

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string      AIKI_OUTLINE

----------------------------------------------------------------------------------------------

开始创建

SQL> l
  1* drop outline out_line1
SQL> /

Outline dropped.

SQL> create table t21 as select object_id,object_name from dba_objects;

Table created.

  1* select count(1) from t21
SQL> /

  COUNT(1)
----------
     72583

SQL> update t21 set object_id=88 where rownum<72581;

72580 rows updated.

SQL> commit;

Commit complete.

SQL> select object_id from t21 where object_id !=88;

 OBJECT_ID
----------
        10

SQL> set autot traceonly exp
SQL> l
  1* select * from t21 where object_id=10
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 4004056351

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |   474 |    85   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T21  |     6 |   474 |    85   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> create or replace outline outline1 for category aiki_outline
  2  on select * from t21 where object_id=10;

Outline created.

SQL> select * from t21 where object_id=10
  2  /

Execution Plan
----------------------------------------------------------
Plan hash value: 4004056351

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |   474 |    85   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T21  |     6 |   474 |    85   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> create index idx_t21 on t21(object_id);

Index created.

SQL> l
  1* create index idx_t21 on t21(object_id)
SQL> select * from t21 where object_id=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 4004056351

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   294 | 23226 |    84   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T21  |   294 | 23226 |    84   (0)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=10)

Note
-----
   - outline "SYS_OUTLINE_13090906473958425" used for this statement  --开始使用上outline,虽然创建了索引但还是走全表扫描

--当然如果你用HINT仍有效

SQL> select /*+ index(t21) */ * from t21 where object_id=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3928030068

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   294 | 23226 |    73   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T21     |   294 | 23226 |    73   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IDX_T21 |   118 |       |    71   (0)| 00:00:02 |
---------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=10)

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

SQL> show parameter stored

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string      FALSE

SQL> l
  1* select * from t21 where object_id=10
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 4004056351

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   294 | 23226 |    84   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T21  |   294 | 23226 |    84   (0)| 00:00:02 |  --设为false结果还是全表扫，待明天确认；
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=10)

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

相关视图

dba_outlines

dba_outline_hints

删除操作

drop outline outline1;