Call dbms_imci.columnar_advise() or dbms_imci.columnar_advise_by_columns() to generate the DDL statement needed to create an In-Memory Columnar Index (IMCI) for a given query. Both stored procedures return DDL statements only — they do not execute them. After running the returned DDL, repeat the process if any columns are still invalid until all columns involved in the query have a valid IMCI.
Prerequisites
Before you begin, make sure you have:
A PolarDB for MySQL 8.0.1 cluster with revision version 8.0.1.1.30 or later
SELECT permission on the target table
Procedures
Choose the procedure based on how broadly you want to apply IMCI:
| Procedure | Scope | Use when |
|---|---|---|
dbms_imci.columnar_advise() | All columns in the queried tables | You want full columnar coverage on every table the query touches |
dbms_imci.columnar_advise_by_columns() | Only the columns referenced in the query | You want a targeted IMCI limited to the columns the query actually uses |
Syntax
Get the DDL statement to create an IMCI for all columns in the queried tables:
CALL dbms_imci.columnar_advise('<query_string>');Get the DDL statement to create an IMCI for only the specific columns referenced in the query:
CALL dbms_imci.columnar_advise_by_columns('<query_string>');Parameters
| Parameter | Type | Description |
|---|---|---|
query_string | String literal | The SQL statement to analyze. Must be a valid SELECT statement — INSERT, UPDATE, and DELETE statements are not supported. Must be a string literal, not a variable or a query result. If the SELECT statement references a nonexistent column, an error is returned. |
Examples
The following examples use two tables, t1 and t2, in the test database.
Set up the example tables:
USE test;
CREATE TABLE t1 (a INT, b INT) ENGINE = InnoDB;
CREATE TABLE t2 (a INT, b INT) ENGINE = InnoDB;Get DDL for all columns in the queried tables
Call dbms_imci.columnar_advise() with the target query:
CALL dbms_imci.columnar_advise('SELECT COUNT(t1.a) FROM t1 INNER JOIN t2 ON t1.a = t2.a GROUP BY t1.b');Sample output:
+-------------------------------------------+
| DDL_STATEMENT |
+-------------------------------------------+
| ALTER TABLE test.t1 COMMENT='COLUMNAR=1'; |
| ALTER TABLE test.t2 COMMENT='COLUMNAR=1'; |
+-------------------------------------------+
2 rows in set (0.00 sec)The output contains one DDL statement per table. Each statement enables IMCI for all columns in that table. Execute these statements to create the IMCIs.
Get DDL for specific columns referenced in the query
Call dbms_imci.columnar_advise_by_columns() with the same query:
CALL dbms_imci.columnar_advise_by_columns('SELECT COUNT(t1.a) FROM t1 INNER JOIN t2 ON t1.a = t2.a GROUP BY t1.b');Sample 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)Because the query references only t1.a, t1.b, and t2.a, the output creates IMCIs for those three columns only. Execute these statements to enable IMCI on the exact columns the query uses.
Iterating until all columns are valid
After executing the DDL statements, some columns may still have an invalid IMCI status. Call the same stored procedure again with the same query to get updated DDL statements, then execute them. Repeat this cycle until the stored procedure returns no further statements and the IMCI is valid for all columns the query uses.