PolarDB:DDL syntax for database-level and table-level IMCIs

Create a table-level IMCI

CREATE COLUMNAR INDEX ON <db_name>.<table_name>;
CREATE COLUMNAR INDEX ON <table_name>;

When db_name is omitted, the statement targets the database of the current session. Unlike a secondary index, you do not specify an index name or the included columns — the IMCI covers all columns automatically.

To avoid an error when the table already has an IMCI, add IF NOT EXISTS before ON:

CREATE COLUMNAR INDEX IF NOT EXISTS ON <db_name>.<table_name>;

By default, this statement is equivalent to:

ALTER TABLE <db_name>.<table_name> COMMENT 'COLUMNAR=1 <OLD_COMMENT>';

The equivalence above applies only when allow_implicit_imci_alter_comment is set to ON (the default). When set to OFF, the IMCI is created without modifying the table comment. For details, see Create a table-level IMCI without modifying table comments.

To configure a custom IMCI instead of accepting all columns, see Use the extended attributes of an IMCI to customize the IMCI.

Verify IMCI creation status

After creating an IMCI, check whether it is ready by running:

SHOW IMCI INDEXES;

The STATE field indicates the current status:

Delete a table-level IMCI

DROP COLUMNAR INDEX ON <db_name>.<table_name>;
DROP COLUMNAR INDEX ON <table_name>;

When db_name is omitted, the statement targets the database of the current session. You do not need to specify the index name.

To avoid an error when the IMCI is already deleted, add IF EXISTS before ON:

DROP COLUMNAR INDEX IF EXISTS ON <db_name>.<table_name>;

This statement is equivalent to running both of the following:

ALTER TABLE <db_name>.<table_name> COMMENT 'COLUMNAR=0 <OLD_COMMENT>';
ALTER TABLE <db_name>.<table_name> COMMENT '<OLD_COMMENT>';

Batch create table-level IMCIs

Three equivalent syntax options are supported:

-- Option 1
CREATE COLUMNAR INDEX FOR TABLES IN <db_name>;

-- Option 2
CREATE COLUMNAR INDEX FOR TABLES FROM <db_name>;

-- Option 3: stored procedure (dbms_imci package)
-- dbms_imci: database package for managing IMCI operations
-- add_columnar_index: creates IMCIs for all tables in the specified database
CALL dbms_imci.add_columnar_index('<db_name>');

All three options create a table-level IMCI for every table in <db_name> that does not already have one.

The stored procedure accepts an optional set_implicit parameter. Set it to 1 to add the implicit_imci marker to the database, which causes new tables created in that database to automatically receive an IMCI:

CALL dbms_imci.add_columnar_index('<db_name>', 1);

Example

The following example creates IMCIs for all tables in the tpch database and checks the result:

mysql> SHOW TABLES IN tpch;
+--------------------+
| Tables_in_tpch     |
+--------------------+
| customer           |
| lineitem           |
| nation             |
| orders             |
| part               |
| partsupp           |
| region             |
| revenue0           |
| supplier           |
+--------------------+
9 rows in set (0.01 sec)

mysql> SHOW IMCI INDEXES;
Empty set (0.02 sec)

mysql> CREATE COLUMNAR INDEX FOR TABLES IN tpch;
+------------+--------+
| Table_Name | Result |
+------------+--------+
| customer   | Ok     |
| lineitem   | Ok     |
| nation     | Ok     |
| orders     | Ok     |
| part       | Ok     |
| partsupp   | Ok     |
| region     | Ok     |
| supplier   | Ok     |
+------------+--------+
8 rows in set (0.56 sec)

-- Check the STATE field. COMMITTED means the IMCI is ready.
-- RECOVERING and RECOVER_BUILDING mean it is still being built.
mysql> SHOW IMCI INDEXES;
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
| TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID  | STATE            | STATE_UPDATE_AT     | CHECKPOINT_DATADIR          | WRITE_POLICY |
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
| 1080     | tpch        | region     | 3        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1080_258 | Tradeoff     |
| 1081     | tpch        | nation     | 4        | 1         | 65536     | 65536   | COMMITTED        | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1081_254 | Tradeoff     |
| 1082     | tpch        | part       | 9        | 64        | 65536     | 4194304 | RECOVER_BUILDING | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1082_256 | Tradeoff     |
| 1083     | tpch        | supplier   | 7        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1083_259 | Tradeoff     |
| 1084     | tpch        | partsupp   | 5        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1084_257 | Tradeoff     |
| 1085     | tpch        | customer   | 8        | 64        | 65536     | 4194304 | RECOVER_BUILDING | 2024-09-24 18:50:15 | ./imci_1/imci_chkp_1085_252 | Tradeoff     |
| 1086     | tpch        | orders     | 9        | 0         | 65536     | 0       | RECOVER_BUILDING | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1086_255 | Tradeoff     |
| 1087     | tpch        | lineitem   | 15       | 0         | 65536     | 0       | RECOVER_BUILDING | 2024-09-24 18:50:15 | ./imci_1/imci_chkp_1087_253 | Tradeoff     |
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
8 rows in set, 1 warning (0.07 sec)

Batch delete table-level IMCIs

Three equivalent syntax options are supported:

-- Option 1
DROP COLUMNAR INDEX FOR TABLES IN <db_name>;

-- Option 2
DROP COLUMNAR INDEX FOR TABLES FROM <db_name>;

-- Option 3: stored procedure (dbms_imci package)
-- dbms_imci: database package for managing IMCI operations
-- drop_columnar_index: deletes IMCIs from all tables in the specified database
CALL dbms_imci.drop_columnar_index('<db_name>');

All three options delete existing table-level IMCIs from every table in <db_name>.

The stored procedure accepts an optional reset_implicit parameter. Set it to 1 to remove the implicit_imci marker from the database, which stops new tables from automatically receiving an IMCI:

CALL dbms_imci.drop_columnar_index('<db_name>', 1);

Example

mysql> SHOW IMCI INDEXES;
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
| TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID    | STATE     | STATE_UPDATE_AT     | CHECKPOINT_DATADIR          | WRITE_POLICY |
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
| 1080     | tpch        | region     | 3        | 1         | 65536     | 65536     | COMMITTED | 2024-09-24 18:50:30 | ./imci_1/imci_chkp_1080_258 | Tradeoff     |
| 1081     | tpch        | nation     | 4        | 1         | 65536     | 65536     | COMMITTED | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1081_254 | Tradeoff     |
| 1082     | tpch        | part       | 9        | 306       | 65536     | 20054016  | COMMITTED | 2024-09-24 18:50:30 | ./imci_1/imci_chkp_1082_256 | Tradeoff     |
| 1083     | tpch        | supplier   | 7        | 16        | 65536     | 1048576   | COMMITTED | 2024-09-24 18:50:34 | ./imci_1/imci_chkp_1083_259 | Tradeoff     |
| 1084     | tpch        | partsupp   | 5        | 1221      | 65536     | 80019456  | COMMITTED | 2024-09-24 18:51:15 | ./imci_1/imci_chkp_1084_257 | Tradeoff     |
| 1085     | tpch        | customer   | 8        | 229       | 65536     | 15007744  | COMMITTED | 2024-09-24 18:50:28 | ./imci_1/imci_chkp_1085_252 | Tradeoff     |
| 1086     | tpch        | orders     | 9        | 2289      | 65536     | 150011904 | COMMITTED | 2024-09-24 18:51:23 | ./imci_1/imci_chkp_1086_255 | Tradeoff     |
| 1087     | tpch        | lineitem   | 15       | 9156      | 65536     | 600047616 | COMMITTED | 2024-09-24 18:54:16 | ./imci_1/imci_chkp_1087_253 | Tradeoff     |
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
8 rows in set, 1 warning (1.09 sec)

mysql> CALL dbms_imci.drop_columnar_index('tpch');
+-------------+------------+--------+
| Object_Name | Operation  | Result |
+-------------+------------+--------+
| customer    | drop_index | Ok     |
| lineitem    | drop_index | Ok     |
| nation      | drop_index | Ok     |
| orders      | drop_index | Ok     |
| part        | drop_index | Ok     |
| partsupp    | drop_index | Ok     |
| region      | drop_index | Ok     |
| supplier    | drop_index | Ok     |
+-------------+------------+--------+
8 rows in set (0.33 sec)

mysql> SHOW IMCI INDEXES;
Empty set (0.27 sec)

Batch operation result codes

The Result column in the output of a batch operation indicates the outcome for each table:

When to use this setting

Use `allow_implicit_imci_alter_comment = OFF` when:

Table comments must remain consistent across tenants or instances. In a SaaS environment, for example, a large tenant's table (with an IMCI) and a small tenant's table (without one) will have identical SHOW CREATE TABLE output, which simplifies validation with ecosystem tools.

Be aware of the following limitations:

• Binary log replication: When replicating via binary logs, the secondary database cannot recreate the IMCI because the IMCI-related comment is absent from the binlog. To create IMCIs on a secondary database, configure the same allow_implicit_imci_alter_comment and related parameter settings on both the primary and secondary databases.

• Custom IMCIs: Custom IMCIs cannot be configured through these DDL statements. To customize an IMCI, see Use the extended attributes of an IMCI to customize the IMCI.