批量获取创建列存索引的DDL语句

某些场景中，需要为某个业务或某个模块创建列索引，而不仅仅是为一条或者几条SELECT语句中的列创建列索引。在这种场景下，为了避免重复创建列索引，可以先调用存储过程dbms_imci.columnar_advise_begin()，然后再批量调用存储过程dbms_imci.columnar_advise()，批量调用完成后，再调用存储过程dbms_imci.columnar_advise_show()批量获取DDL语句。

前提条件

• PolarDB集群版本需满足以下条件之一：

  • PolarDB MySQL版8.0.1版本且修订版本为8.0.1.1.30及以上。

  • PolarDB MySQL版8.0.2版本且修订版本为8.0.2.2.12及以上。

• 您需要具有所操作的表的读（SELECT）权限。

语法

• dbms_imci.columnar_advise_begin()

  存储过程dbms_imci.columnar_advise_begin()用于开启批量获取DDL语句模式。该存储过程调用后，调用存储过程dbms_imci.columnar_advise()时，不会立即输出DDL语句，而是缓存在内存中，缓存过程中不会记录重复的表名和列名。最后通过调用存储过程dbms_imci.columnar_advise_show()或dbms_imci.columnar_advise_show_by_columns()显式获取DDL语句。

  说明

  调用存储过程dbms_imci.columnar_advise_begin()后，再调用存储过程dbms_imci.columnar_advise_by_columns()的效果与调用存储过程dbms_imci.columnar_advise()的效果相同。

• dbms_imci.columnar_advise_show()

  按表展示存储过程dbms_imci.columnar_advise()所获取的DDL语句，且DDL语句不会重复覆盖相同的表。

• dbms_imci.columnar_advise_show_by_columns()

  按列展示存储过程dbms_imci.columnar_advise()获取的DDL语句，DDL语句不会重复覆盖相同的列。

• dbms_imci.columnar_advise_end()

  结束批量获取DDL语句模式并清理缓存。调用存储过程dbms_imci.columnar_advise_end()前可以重复调用dbms_imci.columnar_advise_show()和 dbms_imci.columnar_advise_show_by_columns()，dbms_imci.columnar_advise_end()调用后，再调用dbms_imci.columnar_advise_show()会返回错误信息。

注意事项

• 用于缓存dbms_imci.columnar_advise()中间结果的最大内存受imci_columnar_advise_buffer_size参数控制，默认为8 MB，一般情况下，能够缓存几千张表的DDL语句。如果需要缓存更多的DDL语句，可以使用SET命令适当调整imci_columnar_advise_buffer_size参数值，如SET imci_columnar_advise_buffer_size = 16777216;。

• 即使没有显式调用存储过程dbms_imci.columnar_advise_end()，当链接断开时，也会清理 dbms_imci.columnar_advise()缓存。

示例

以t1和t2表为例，批量获取创建列存索引的DDL语句。

1. 执行如下命令，切换至test库。

   use test;

2. 执行如下命令，创建t1和t2表。

   create table t1 (a int, b int) engine = innodb;
   create table t2 (a int, b int) engine = innodb;

3. 调用存储过程，开启批量获取DDL语句模式。

   call dbms_imci.columnar_advise_begin();

4. 批量执行存储过程dbms_imci.columnar_advise()。

   call dbms_imci.columnar_advise('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');
   call dbms_imci.columnar_advise('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');
   call dbms_imci.columnar_advise('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');
   call dbms_imci.columnar_advise('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');

5. 调用存储过程，来显示获取DDL语句。

   • 按表展示存储过程dbms_imci.columnar_advise()所获取的DDL语句。

     call dbms_imci.columnar_advise_show();

     执行结果如下：

     +-------------------------------------------+
     | DDL_STATEMENT                             |
     +-------------------------------------------+
     | ALTER TABLE test.t1 COMMENT='COLUMNAR=1'; |
     | ALTER TABLE test.t2 COMMENT='COLUMNAR=1'; |
     +-------------------------------------------+
     2 rows in set (0.00 sec)

   • 按列展示存储过程dbms_imci.columnar_advise()所获取的DDL语句。

      call dbms_imci.columnar_advise_show_by_columns();

     执行结果如下：

     +-------------------------------------------------------------------------------------------------------------------------------------------+
     | DDL_STATEMENT                                                                                                                             |
     +-------------------------------------------------------------------------------------------------------------------------------------------+
     | ALTER TABLE test.t1 MODIFY COLUMN a int(11) DEFAULT NULL COMMENT 'COLUMNAR=1', MODIFY COLUMN b int(11) DEFAULT NULL COMMENT 'COLUMNAR=1'; |
     | ALTER TABLE test.t2 MODIFY COLUMN a int(11) DEFAULT NULL COMMENT 'COLUMNAR=1';                                                            |
     +-------------------------------------------------------------------------------------------------------------------------------------------+
     2 rows in set (0.00 sec)

6. 批量获取DDL语句完成后，调用以下存储过程，结束批量获取DDL语句模式并清理缓存。

   call dbms_imci.columnar_advise_end();

   执行结果如下：

   Query OK, 0 rows affected (0.11 sec)