列ストアインデックス作成用の DDL 文を一括取得する方法 - PolarDB

単一の SELECT 文内のカラムだけでなく、サービスまたはモジュール全体に対してインメモリー列指向インデックス（IMCI）を作成する必要がある場合は、dbms_imci.columnar_advise_begin() ワークフローを使用します。このワークフローは複数のクエリにわたる推奨事項をキャッシュし、重複排除を行うことで、影響を受けるすべてのテーブルおよびカラムをカバーするクリーンな DDL 文のセットを提供します。

前提条件

作業を開始する前に、以下の要件を満たしていることを確認してください。

リビジョンバージョン 8.0.1.1.30 以降の PolarDB for MySQL 8.0.1 クラスター
分析対象のテーブルに対する SELECT 権限

仕組み

バッチ DDL ワークフローでは、以下の 4 つのストアドプロシージャを順番に呼び出します。

dbms_imci.columnar_advise_begin() を呼び出してバッチセッションを開始します。その後の columnar_advise() 呼び出しでは、結果を即座に返す代わりに推奨事項をメモリ内にキャッシュします。キャッシュ中にテーブル名およびカラム名の重複が自動的に排除されます。
分析対象の各 SELECT 文ごとに、dbms_imci.columnar_advise() を 1 回ずつ呼び出してクエリを登録します。
dbms_imci.columnar_advise_show() または dbms_imci.columnar_advise_show_by_columns() を呼び出して、重複排除済みの DDL 文を取得します。
dbms_imci.columnar_advise_end() を呼び出してセッションを終了し、キャッシュをクリアします。

説明

dbms_imci.columnar_advise_begin() の後に dbms_imci.columnar_advise_by_columns() を呼び出すことは、dbms_imci.columnar_advise() を呼び出すことと同等です。

ストアドプロシージャ

dbms_imci.columnar_advise_begin()

バッチ DDL コレクションセッションを開始します。このプロシージャを呼び出した後、dbms_imci.columnar_advise() は推奨事項を即座に表示する代わりにメモリ内にキャッシュします。

dbms_imci.columnar_advise_show()

影響を受けるテーブルごとに 1 つの DDL 文を返します。重複するテーブル名は含まれません。

結果の各行には、COMMENT='COLUMNAR=1' 属性を使用してテーブル全体で IMCI を有効にする ALTER TABLE 文が含まれる DDL_STATEMENT カラムが 1 つだけ存在します。

dbms_imci.columnar_advise_show_by_columns()

影響を受けるテーブルごとに 1 つの DDL 文を返し、推奨される各カラムを個別にリストアップします。重複するカラム名は含まれません。

各行には、推奨される各列で COMMENT 'COLUMNAR=1' を使用してインメモリー列指向インデックスを有効にする ALTER TABLE ... MODIFY COLUMN 文を含む、単一の DDL_STATEMENT 列が含まれます。

クエリで参照されるのがテーブル内のカラムの一部のみであるなど、カラムレベルの粒度が必要な場合は、このバリエーションを使用してください。

dbms_imci.columnar_advise_end()

バッチセッションを終了し、キャッシュをクリアします。このプロシージャを呼び出す前に、show プロシージャを複数回呼び出して結果を確認できます。columnar_advise_end() の後に show プロシージャを呼び出すとエラーが返されます。

columnar_advise_end() を省略した場合でも、接続が閉じられるとキャッシュは自動的にクリアされます。

注意事項

キャッシュのメモリ上限は、imci_columnar_advise_buffer_size パラメーターで制御されます。デフォルト値は 8 MB で、数千のテーブルを処理するのに十分です。上限を増やすには、次のコマンドを実行します。
```
SET imci_columnar_advise_buffer_size = 16777216;
```

例：DDL 文を一括取得する

以下の例では、2 つのテーブルに対する複数の SELECT クエリを分析し、推奨される IMCI DDL 文を取得します。

test データベースに切り替えます。
```
USE test;
```

t1 テーブルおよび t2 テーブルを作成します。

CREATE TABLE t1 (a INT, b INT) ENGINE = InnoDB;
CREATE TABLE t2 (a INT, b INT) ENGINE = InnoDB;

バッチ DDL セッションを開始します。
```
CALL dbms_imci.columnar_advise_begin();
```

分析対象のクエリを登録します。次の例では、繰り返されるクエリパターンを含むワークロードを再現するために、同じクエリで columnar_advise() を 4 回呼び出します。

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');

DDL 文を取得します。セッション終了前に、どちらか一方または両方の show プロシージャを使用できます。

テーブル単位 — 影響を受けるテーブルごとに 1 つの ALTER TABLE 文で、すべてのカラムに対して IMCI を有効にします。

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)

カラム単位 — 影響を受けるテーブルごとに 1 つの ALTER TABLE 文で、推奨される各カラムを個別にリストアップします。

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)

セッションを終了し、キャッシュをクリアします。
```
CALL dbms_imci.columnar_advise_end();
```
期待される出力：
```
Query OK, 0 rows affected (0.11 sec)
```