Como obter em lote instruções DDL para criação de índices columnstore - PolarDB: Central de Documentação do Alibaba Cloud

Quando for necessário criar índices colunares em memória (IMCIs) para um serviço ou módulo inteiro — e não apenas para colunas de uma única instrução SELECT — utilize o fluxo de trabalho dbms_imci.columnar_advise_begin(). Esse fluxo armazena recomendações de várias consultas em cache e remove duplicatas dos resultados, fornecendo um conjunto limpo de instruções DDL que abrange todas as tabelas e colunas afetadas.

Pré-requisitos

Antes de começar, verifique se você possui:

Um cluster PolarDB for MySQL 8.0.1 com versão de revisão 8.0.1.1.30 ou posterior
Permissão selecione nas tabelas que deseja analisar

Como funciona

O fluxo de trabalho de DDL em lote utiliza quatro stored procedures chamados em sequência:

Chame dbms_imci.columnar_advise_begin() para iniciar uma sessão em lote. As chamadas subsequentes a columnar_advise() armazenam as recomendações na memória em vez de retornar os resultados imediatamente. Nomes duplicados de tabelas e colunas são removidos automaticamente durante o armazenamento em cache.
Execute dbms_imci.columnar_advise() uma vez por consulta para registrar cada instrução SELECT que você deseja analisar.
Utilize dbms_imci.columnar_advise_show() ou dbms_imci.columnar_advise_show_by_columns() para recuperar as instruções DDL sem duplicatas.
Invoque dbms_imci.columnar_advise_end() para encerrar a sessão e limpar o cache.

Nota

Chamar dbms_imci.columnar_advise_begin() seguido por dbms_imci.columnar_advise_by_columns() equivale a chamar dbms_imci.columnar_advise().

Stored procedures

dbms_imci.columnar_advise_begin()

Inicia uma sessão de coleta de DDL em lote. Após chamar este procedimento, dbms_imci.columnar_advise() armazena as recomendações na memória em vez de exibi-las imediatamente.

dbms_imci.columnar_advise_show()

Retorna uma instrução DDL por tabela afetada. Nomes de tabelas duplicados não são incluídos.

Cada linha no resultado contém uma única coluna DDL_STATEMENT com uma instrução ALTER TABLE que ativa o IMCI em toda a tabela usando o atributo COMMENT='COLUMNAR=1'.

dbms_imci.columnar_advise_show_by_columns()

Fornece uma instrução DDL por tabela afetada, listando individualmente cada coluna recomendada. Nomes de colunas duplicados não são incluídos.

Cada linha contém uma única coluna DDL_STATEMENT com uma instrução ALTER TABLE ... MODIFY COLUMN que ativa o IMCI em cada coluna recomendada usando COMMENT 'COLUMNAR=1'.

Essa variante é ideal quando você precisa de granularidade no nível da coluna — por exemplo, quando suas consultas referenciam apenas um subconjunto de colunas de uma tabela.

dbms_imci.columnar_advise_end()

Encerra a sessão em lote e limpa o cache. Antes de chamar este procedimento, é possível executar os procedimentos "show" várias vezes para inspecionar os resultados. Chamar um procedimento "show" após columnar_advise_end() retorna um erro.

Mesmo que você não execute columnar_advise_end(), o cache será limpo automaticamente quando a conexão for fechada.

Notas de uso

O parâmetro imci_columnar_advise_buffer_size controla o limite de memória do cache. O valor padrão é 8 MB, suficiente para milhares de tabelas. Para aumentar o limite, defina:
```
SET imci_columnar_advise_buffer_size = 16777216;
```

Exemplo: obter instruções DDL em lote

O exemplo a seguir analisa várias consultas SELECT em duas tabelas e recupera as instruções DDL recomendadas para IMCI.

Mude para o banco de dados test:
```
USE test;
```

Crie as tabelas t1 e t2:

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

Inicie uma sessão de DDL em lote:
```
CALL dbms_imci.columnar_advise_begin();
```

Registre as consultas para análise. O exemplo abaixo chama columnar_advise() quatro vezes com a mesma consulta para simular uma carga de trabalho com padrões de consulta repetidos:

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

Recupere as instruções DDL. Use um ou ambos os procedimentos "show" antes de encerrar a sessão.

Por tabela — uma instrução ALTER TABLE por tabela afetada, ativando o IMCI em todas as colunas:

CALL dbms_imci.columnar_advise_show();

Saída esperada:

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

Por coluna — uma instrução ALTER TABLE por tabela afetada, com cada coluna recomendada listada individualmente:

CALL dbms_imci.columnar_advise_show_by_columns();

Saída esperada:

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

Encerre a sessão e limpe o cache:

CALL dbms_imci.columnar_advise_end();

Saída esperada:

Query OK, 0 rows affected (0.11 sec)