PolarDB：批量擷取建立列存索引的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)