Comment récupérer en lot les instructions DDL de création d'index columnstore - PolarDB

Pour créer des index columnaires en mémoire (IMCI) sur l'ensemble d'un service ou d'un module, et non seulement sur les colonnes d'une seule instruction SELECT, utilisez le workflow dbms_imci.columnar_advise_begin(). Ce workflow met en cache les recommandations issues de plusieurs requêtes et déduplique les résultats, ce qui permet d'obtenir un ensemble cohérent d'instructions DDL couvrant toutes les tables et colonnes concernées.

Prérequis

Avant de commencer, assurez-vous de disposer des éléments suivants :

Un cluster PolarDB for MySQL 8.0.1 dont la version de révision est 8.0.1.1.30 ou ultérieure
L'autorisation SELECT sur les tables à analyser

Fonctionnement

Le workflow DDL par lot repose sur quatre procédures stockées appelées séquentiellement :

Appelez dbms_imci.columnar_advise_begin() pour ouvrir une session par lot. Les appels suivants à columnar_advise() mettent alors leurs recommandations en cache mémoire au lieu de retourner immédiatement les résultats. Les noms de tables et de colonnes en double sont automatiquement dédupliqués lors de la mise en cache.
Appelez dbms_imci.columnar_advise() une fois par requête afin d'enregistrer chaque instruction SELECT à analyser.
Appelez dbms_imci.columnar_advise_show() ou dbms_imci.columnar_advise_show_by_columns() pour récupérer les instructions DDL dédupliquées.
Appelez dbms_imci.columnar_advise_end() pour clore la session et vider le cache.

Remarque

Appeler dbms_imci.columnar_advise_begin() puis dbms_imci.columnar_advise_by_columns() équivaut à appeler dbms_imci.columnar_advise().

Procédures stockées

dbms_imci.columnar_advise_begin()

Cette procédure ouvre une session de collecte DDL par lot. Une fois appelée, dbms_imci.columnar_advise() place les recommandations en cache mémoire plutôt que de les afficher immédiatement.

dbms_imci.columnar_advise_show()

Retourne une instruction DDL par table concernée. Les noms de tables en double sont exclus.

Chaque ligne du résultat contient une unique colonne DDL_STATEMENT avec une instruction ALTER TABLE activant l'IMCI sur l'ensemble de la table via l'attribut COMMENT='COLUMNAR=1'.

dbms_imci.columnar_advise_show_by_columns()

Retourne une instruction DDL par table concernée, en listant individuellement chaque colonne recommandée. Les noms de colonnes en double sont exclus.

Chaque ligne contient une unique colonne DDL_STATEMENT avec une instruction ALTER TABLE ... MODIFY COLUMN activant l'IMCI sur chaque colonne recommandée au moyen de COMMENT 'COLUMNAR=1'.

Privilégiez cette variante lorsque vous avez besoin d'une granularité au niveau de la colonne, par exemple si vos requêtes ne référencent qu'un sous-ensemble des colonnes d'une table.

dbms_imci.columnar_advise_end()

Clôt la session par lot et vide le cache. Avant d'appeler cette procédure, vous pouvez invoquer les procédures show à plusieurs reprises pour examiner les résultats. En revanche, appeler une procédure show après columnar_advise_end() génère une erreur.

Même si vous omettez columnar_advise_end(), le cache se vide automatiquement à la fermeture de la connexion.

Remarques d'utilisation

Le paramètre imci_columnar_advise_buffer_size contrôle la limite de mémoire du cache. La valeur par défaut est de 8 Mo, ce qui suffit pour des milliers de tables. Pour augmenter cette limite, exécutez :
```
SET imci_columnar_advise_buffer_size = 16777216;
```

Exemple : obtention par lot d'instructions DDL

L'exemple suivant analyse plusieurs requêtes SELECT portant sur deux tables et récupère les instructions DDL recommandées pour l'IMCI.

Basculez vers la base de données test :
```
USE test;
```

Créez les tables t1 et t2 :

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

Ouvrez une session DDL par lot :
```
CALL dbms_imci.columnar_advise_begin();
```

Enregistrez les requêtes à analyser. L'exemple ci-dessous appelle columnar_advise() quatre fois avec la même requête afin de simuler une charge de travail comportant des motifs de requêtes répétés :

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

Récupérez les instructions DDL. Utilisez l'une ou l'autre des procédures show, ou les deux, avant de clore la session.

Par table — une instruction ALTER TABLE par table concernée, activant l'IMCI sur toutes les colonnes :

CALL dbms_imci.columnar_advise_show();

Sortie attendue :

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

Par colonne — une instruction ALTER TABLE par table concernée, avec chaque colonne recommandée listée individuellement :

CALL dbms_imci.columnar_advise_show_by_columns();

Sortie attendue :

+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

Clôturez la session et videz le cache :

CALL dbms_imci.columnar_advise_end();

Sortie attendue :

Query OK, 0 rows affected (0.11 sec)