ADVISE_INDEX_CREATE

说明

创建推荐的索引

语法

advise_index_create ::=

advise_index_create ::=
    ADVISE_INDEX_CREATE ( [ IndexID [, ... ] ] ) ;

参数

IndexID

推荐索引的ID，可以通过ADVISE_INDEX_RESULT视图查看具体推荐索引ID。

注解

若参数为空，则创建所有推荐的索引。

返回值

若成功创建推荐的索引，则返回TRUE；否则返回FALSE。

示例

-- 创建表
create table t1(a int, b int);
create table t2(a int, b int);
create table t3(a int, b int);

-- 创建推荐索引环境
select advise_index_begin(4, 8192);
 ADVISE_INDEX_BEGIN
--------------------
 t
(1 row)

-- 执行推荐索引命令
select advise_index('select * from t1 where a=1;select * from t2 where b = 3;select * from t3 order by a, b');
 ADVISE_INDEX
--------------
 t
(1 row)

-- 使用视图查看索引推荐的结果
select * from ADVISE_INDEX_RESULT;
 ID | TABLE_SCHEMA | REL_NAME | SIZE |                   CREATE_SQL
----+--------------+----------+------+------------------------------------------------
  1 | SYSDBA       | T3       |   19 | CREATE INDEX ADV_IDX_T3_A_B ON SYSDBA.T3(A,B);
  2 | SYSDBA       | T1       |   13 | CREATE INDEX ADV_IDX_T1_A ON SYSDBA.T1(A);
  3 | SYSDBA       | T2       |   13 | CREATE INDEX ADV_IDX_T2_B ON SYSDBA.T2(B);
(3 rows)

-- 创建索引推荐结果中ID为1和3的索引
select advise_index_create(1, 3);
 ADVISE_INDEX_CREATE
---------------------
 t
(1 row)

-- 清理推荐索引环境
select advise_index_end();
 ADVISE_INDEX_END
------------------
 t
(1 row)