ADVISE_INDEX_RESULT

说明

显示索引推荐的结果

语法

advise_index_result ::=

advise_index_result ::=
    ADVISE_INDEX_RESULT ( ) ;

参数

无

返回值

索引推荐的结果，具体为：推荐索引ID，模式名，表名，组合列，创建该索引的SQL语句。

注解

为方便打印信息，我们提供了同名的视图ADVISE_INDEX_RESULT。

示例

-- 创建表
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 ID, TABLE_SCHEMA, REL_NAME, COL_NAME, CREATE_SQL from advise_index_result() as (ID INT2, TABLE_SCHEMA TEXT, REL_NAME TEXT, COL_NAME TEXT, SIZE INT4, CREATE_SQL TEXT);
 ID | TABLE_SCHEMA | REL_NAME | COL_NAME |                   CREATE_SQL
----+--------------+----------+----------+------------------------------------------------
  1 | SYSDBA       | T3       | A,B      | CREATE INDEX ADV_IDX_T3_A_B ON SYSDBA.T3(A,B);
  2 | SYSDBA       | T1       | A        | CREATE INDEX ADV_IDX_T1_A ON SYSDBA.T1(A);
  3 | SYSDBA       | T2       | B        | CREATE INDEX ADV_IDX_T2_B ON SYSDBA.T2(B);
(3 rows)

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

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