ALTER OUTLINE

说明

修改outline

语法

alter_outline ::=

alter_outline::=
    ALTER OUTLINE outline_name { REBUILD | RENAME TO new_name | ENABLE | DISABLE};

参数

outline_name

outline名称。

new_name

新改写的Outline名称

示例

示例1: 重建outline

-- 切换到 SYSDBA 用户
CONNECT sysdba/szoscar55;

-- 清理环境
DROP TABLE T1 CASCADE;
DROP INDEX IDX1;
DROP OUTLINE OUT1;

CREATE TABLE T1(A INT);
insert into t1 values(1);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 values(2);

analyze t1;

-- 创建Outline
create outline out1 on select /*+ index(t1) */ * from t1 where a = 2 match select * from t1 where a = 2;


-- 此时outline中固定的是顺序扫描计划
select OUTLINE_NAME,OUTLINE_DATA from sys_outline;
OUTLINE_NAME(name)      |OUTLINE_DATA(text)      |
------------------------------------------------
OUT1                    |/*+ FULL(@SEL$1 SYSDBA.T|
                        |1@SEL$1) */             |
总数目：1

-- 创建索引后，应该走索引更快，所以重建后应该走索引扫描
create index idx1 on t1(a);

-- 重建Outline
alter outline out1 rebuild;


-- 此时outline中固定的是索引扫描计划
select OUTLINE_NAME,OUTLINE_DATA from sys_outline;
OUTLINE_NAME(name)      |OUTLINE_DATA(text)      |
------------------------------------------------
OUT1                    |/*+ INDEX(@SEL$1 SYSDBA.|
                        |T1@SEL$1 IDX1) */       |
总数目：1

-- 清理环境
DROP OUTLINE OUT1;
DROP TABLE T1;

示例2: 修改outline名字

-- 切换到 SYSDBA 用户
CONNECT sysdba/szoscar55;

-- 清理环境
DROP TABLE T1 CASCADE;
DROP OUTLINE OUT1;
DROP OUTLINE OUT2;

CREATE TABLE T1(A INT);

-- 创建Outline
CREATE OUTLINE OUT1 ON SELECT * FROM T1 MATCH SELECT * FROM T1;

-- 修改Outline名字
ALTER OUTLINE OUT1 RENAME TO OUT2;


select OUTLINE_NAME,OUTLINE_DATA from sys_outline;
OUTLINE_NAME(name)      |OUTLINE_DATA(text)      |
------------------------------------------------
OUT2                    |/*+ FULL(@SEL$1 SYSDBA.T|
                        |1@SEL$1) */             |
总数目：1

-- 删除Outline
DROP OUTLINE OUT2;
DROP TABLE T1;

示例3: 启用/禁用outline

-- 切换到 SYSDBA 用户
CONNECT sysdba/szoscar55;

-- 开启outline参数
set ENABLE_USE_OUTLINE = TRUE;

-- 清理环境
DROP TABLE T1 CASCADE;
DROP INDEX IDX1;
DROP OUTLINE OUT1;

CREATE TABLE T1(A INT);
CREATE INDEX IDX1 ON T1(A);

-- 创建Outline
CREATE OUTLINE OUT1 ON SELECT /*+ FULL(T1) */ * FROM T1 WHERE A = 1 MATCH SELECT /*+ INDEX(T1) */ * FROM T1 WHERE A = 1;

-- 禁用outline
ALTER OUTLINE out1 DISABLE;


EXPLAIN SELECT /*+ INDEX(T1) */ * FROM T1 WHERE A = 1;
QUERY PLAN(text)      
----------------------
Index Scan using IDX1(
Fast Index Scan) on T
1  (cost=0.00..1.41 r
ows=41 width=4)       
  Index Key: (A = 1)  
总数目：2

-- 启用outline
ALTER OUTLINE out1 ENABLE;


EXPLAIN SELECT /*+ INDEX(T1) */ * FROM T1 WHERE A = 1;
QUERY PLAN(text)      
----------------------
Seq Scan on T1  (cost=
0.00..110.40 rows=41 
width=4)              
  Scan Key: (A = 1)   
Note: use outline OUT1
总数目：4

-- 删除Outline
DROP OUTLINE OUT1;
DROP TABLE T1;