How to batch retrieve DDL statements for creating columnstore indexes - PolarDB

When you need to create in-memory columnar indexes (IMCIs) for an entire service or module — not just for columns in a single SELECT statement — use the dbms_imci.columnar_advise_begin() workflow. This workflow caches recommendations across multiple queries and deduplicates results, giving you a clean set of DDL statements covering all affected tables and columns.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for MySQL 8.0.1 cluster with revision version 8.0.1.1.30 or later
SELECT permission on the tables you want to analyze

How it works

The batch DDL workflow uses four stored procedures called in sequence:

Call dbms_imci.columnar_advise_begin() to start a batch session. Subsequent columnar_advise() calls cache their recommendations in memory instead of returning results immediately. Duplicate table and column names are automatically deduplicated during caching.
Call dbms_imci.columnar_advise() once per query to register each SELECT statement you want to analyze.
Call dbms_imci.columnar_advise_show() or dbms_imci.columnar_advise_show_by_columns() to retrieve the deduplicated DDL statements.
Call dbms_imci.columnar_advise_end() to end the session and clear the cache.

Note

Calling dbms_imci.columnar_advise_begin() followed by dbms_imci.columnar_advise_by_columns() is equivalent to calling dbms_imci.columnar_advise().

Stored procedures

dbms_imci.columnar_advise_begin()

Starts a batch DDL collection session. After you call this procedure, dbms_imci.columnar_advise() caches recommendations in memory instead of displaying them immediately.

dbms_imci.columnar_advise_show()

Returns one DDL statement per affected table. Duplicate table names are not included.

Each row in the result contains a single DDL_STATEMENT column with an ALTER TABLE statement that enables the IMCI on the entire table using the COMMENT='COLUMNAR=1' attribute.

dbms_imci.columnar_advise_show_by_columns()

Returns one DDL statement per affected table, listing each recommended column individually. Duplicate column names are not included.

Each row contains a single DDL_STATEMENT column with an ALTER TABLE ... MODIFY COLUMN statement that enables the IMCI on each recommended column using COMMENT 'COLUMNAR=1'.

Use this variant when you want column-level granularity — for example, when only a subset of columns in a table are referenced by your queries.

dbms_imci.columnar_advise_end()

Ends the batch session and clears the cache. Before calling this procedure, you can call the show procedures multiple times to inspect results. Calling a show procedure after columnar_advise_end() returns an error.

Even if you skip columnar_advise_end(), the cache is cleared automatically when the connection closes.

Usage notes

The cache memory limit is controlled by the imci_columnar_advise_buffer_size parameter. The default is 8 MB, which is enough for thousands of tables. To increase the limit, run:
```
SET imci_columnar_advise_buffer_size = 16777216;
```

Example: batch obtain DDL statements

The following example analyzes multiple SELECT queries against two tables and retrieves the recommended IMCI DDL statements.

Switch to the test database:
```
USE test;
```

Create the t1 and t2 tables:

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

Start a batch DDL session:
```
CALL dbms_imci.columnar_advise_begin();
```

Register the queries to analyze. The following example calls columnar_advise() four times with the same query to simulate a workload with repeated query patterns:

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

Retrieve the DDL statements. Use either or both show procedures before ending the session.

By table — one ALTER TABLE statement per affected table, enabling the IMCI on all columns:

CALL dbms_imci.columnar_advise_show();

Expected output:

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

By column — one ALTER TABLE statement per affected table, with each recommended column listed individually:

CALL dbms_imci.columnar_advise_show_by_columns();

Expected output:

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

End the session and clear the cache:

CALL dbms_imci.columnar_advise_end();

Expected output:

Query OK, 0 rows affected (0.11 sec)