All Products
Search

This Product

    PolarDB:Instructions

    Document Center

    PolarDB:Instructions

    Last Updated:Jul 01, 2026

    Refer to the Quick start section to configure your cluster for Hybrid Transactional/Analytical Processing (HTAP) workloads, or the Advanced usage section to customize IMCI for specific business requirements.

    Quick start

    Step 1: Add a read-only IMCI node

    Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select your cluster's region and find the cluster. In the Actions column, click Add/Remove Node to add a read-only IMCI node.image

    Note

    • If you already added a read-only IMCI node when you purchased the cluster, skip this step.

    • Your cluster must meet the version requirements for IMCI. For more information, see read-only IMCI node.

    Step 2: Configure request routing

    You can choose automatic or manual request routing to use IMCI based on your business requirements.

    • Automatic request routing: If your Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP) workloads access the database from the same application, you can automatically route read requests to either read-only IMCI nodes or read-only row store nodes based on the estimated execution cost, which is determined by the number of scanned rows.

    • Manual request routing: If your OLTP and OLAP workloads access the database from different applications, you can configure different cluster endpoints for these applications. Then, you can route requests to row and column stores by assigning read-only IMCI nodes and read-only row store nodes to the service nodes of different endpoints.

    Automatic request routing

    On the cluster details page, go to the Database Connections section. For the Cluster Endpoint, set Service Node to Enable.image

    Note

    Manual request routing

    On the cluster details page, go to the Database Connections section and create a custom endpoint. Ensure that the Service Node include only Service Node.image

    Step 3: Add an IMCI

    You can add IMCIs to your tables manually or automatically based on your business requirements.

    Important

    For a query to be accelerated by IMCI, all columns referenced in the SQL statement must be fully covered by an IMCI.

    Manual

    IMCI provides a full set of DDL statements to add or remove IMCI for business tables. Choose the option that fits your needs:

    DDL syntax

    Example

    Create an IMCI when you create a table

    		 
    -- Add an IMCI for a specific column.
CREATE TABLE <table_name>(
  <column_name_1> INT COMMENT 'COLUMNAR=1',
  <column_name_2> VARCHAR(100)
) ENGINE InnoDB;

-- Add an IMCI for the entire table.
CREATE TABLE <table_name>(
  <column_name_1> INT,
  <column_name_2> VARCHAR(100)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';

    Dynamically add or remove an IMCI

    		 
    -- Add an IMCI for a specific column.
ALTER TABLE <table_name> MODIFY COLUMN <column_name_1> INT COMMENT 'COLUMNAR=1';
               
-- Add an IMCI for the entire table.
ALTER TABLE <table_name> COMMENT 'COLUMNAR=1';

    Add or remove IMCIs at the database or table level in batches

    		 
    -- Add an IMCI for the entire table.
CREATE COLUMNAR INDEX ON <db_name>.<table_name>;

-- Add IMCIs for all tables in a database.
CREATE COLUMNAR INDEX FOR TABLES IN <db_name>;
    Note

    • When you add an IMCI, you can preserve existing comments. Example: ALTER TABLE <table_name> COMMENT 'COLUMNAR=1 <original comment>';.

    • By default, when you add an IMCI in batches at the database or table level, the table comment is updated to 'COLUMNAR=1 <original comment>'.

    Automatic

    The AutoIndex feature can automatically create IMCIs based on your slow queries to significantly improve their execution speed. This eliminates the need to understand and tune each slow query. As the application workload changes, AutoIndex continuously monitors and adjusts the IMCI strategy to ensure that your PolarDB cluster maintains optimal performance.

    You can experience the benefits of this feature by simply Enable AutoIndex on the PolarDB cluster details page:

    image

    (Optional) Step 4: Check IMCI build progress

    After you manually add an IMCI, check its build progress. Wait for the build to complete to benefit from query acceleration.

    SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

    Example: Check whether the value in the STATUS column is Safe to read to confirm that the IMCI is fully built.

    +-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT          | STARTED_AT          | FINISHED_AT         | STATUS       | APPROXIMATE_ROWS | SCANNED_ROWS    | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch        | lineitem   | 2024-10-21 13:44:02 | 2024-10-21 13:44:02 | 2024-10-21 13:50:11 | Safe to read | 590446240        | 600037902(100%) | 369         | 0           | 0           | 0(0%)      | 0            | 1625058   | 0                 | 0               |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

    (Optional) Step 5: Check for IMCI usage

    An IMCI execution plan is displayed as a horizontal tree, which has a distinctly different format from a row-store execution plan. You can use the EXPLAIN statement to view the execution plan and determine whether a SQL statement can be accelerated by IMCI. The following examples show the difference:

    IMCI execution plan

    +----+----------------------------+----------+--------+--------+-----------------------------------------------------------------------------+
| ID | Operator                   | Name     | E-Rows | E-Cost | Extra Info                                                                  |
+----+----------------------------+----------+--------+--------+-----------------------------------------------------------------------------+
|  1 | Select Statement           |          |        |        | IMCI Execution Plan (max_dop = 4, max_query_mem = 858993459)                |
|  2 | └─Sort                     |          |        |        | Sort Key: revenue DESC,o_orderdate ASC                                      |
|  3 |   └─Hash Groupby           |          |        |        | Group Key: (lineitem.L_ORDERKEY, orders.O_ORDERDATE, orders.O_SHIPPRIORITY) |
|  4 |     └─Hash Join            |          |        |        | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY                          |
|  5 |       ├─Hash Join          |          |        |        | Join Cond: customer.C_CUSTKEY = orders.O_CUSTKEY                            |
|  6 |       │ ├─Table Scan       | customer |        |        | Cond: (C_MKTSEGMENT = "BUILDING")                                           |
|  7 |       │ └─Table Scan       | orders   |        |        | Cond: (O_ORDERDATE < 03/24/1995)                                            |
|  8 |       └─Table Scan         | lineitem |        |        | Cond: (L_SHIPDATE > 03/24/1995)                                             |
+----+----------------------------+----------+--------+--------+-----------------------------------------------------------------------------+
8 rows in set (0.01 sec)

    Row-store execution plan

    +----+-------------+----------+------------+------+--------------------+------------+---------+-----------------------------+--------+----------+----------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys      | key        | key_len | ref                         | rows   | filtered | Extra                                        |
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | customer | NULL       | ALL  | PRIMARY            | NULL       | NULL    | NULL                        | 147630 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | orders   | NULL       | ref  | PRIMARY,ORDERS_FK1 | ORDERS_FK1 | 4       | tpch100g.customer.C_CUSTKEY |     14 |    33.33 | Using where                                  |
|  1 | SIMPLE      | lineitem | NULL       | ref  | PRIMARY            | PRIMARY    | 4       | tpch100g.orders.O_ORDERKEY  |      4 |    33.33 | Using where                                  |
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------------------+--------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

    Helper tools

    When you use IMCI to query complex SQL statements, you must check whether any columns in the SQL statement are not covered by an IMCI. If you find uncovered columns, you can get the DDL statements to create an IMCI for the SQL statement, or batch-get the DDL statements to create IMCIs for a specific workload. After running the DDL statements, ensure all columns referenced in the SQL statement are covered by an IMCI to enable query acceleration.

    PolarDB provides the following built-in stored procedures:

    FAQ

    Query not using IMCI

    After you add a read-only IMCI node, you must also create an IMCI for all tables queried in the SQL statement. The statement uses an IMCI only if its estimated execution cost exceeds a specific threshold. Additionally, the SQL statement must be forwarded to a read-only IMCI node to be accelerated. If a SQL statement is not using an IMCI, follow these steps to troubleshoot the issue:

    1. Confirm that the SQL statement is forwarded to a read-only IMCI node.

      • Check whether the Service Node for the database endpoint include a read-only IMCI node.

      • Use SQL Explorer to confirm that the SQL statement is forwarded to a read-only IMCI node.

      If you use a Cluster Endpoint with Service Node enabled, and the SQL statement's estimated execution cost is higher than the loose_imci_ap_threshold or loose_cost_threshold_for_imci threshold, PolarProxy forwards the SQL statement to a read-only IMCI node. You can also add the HINT syntax /*FORCE_IMCI_NODES*/ before the SELECT keyword to force the SQL statement to be routed to a read-only IMCI node. For more information, see Configure the automatic routing threshold. Example:

      In database engine minor versions 8.0.1.1.39 and 8.0.2.2.23 and later, the loose_imci_ap_threshold parameter is deprecated and replaced by the loose_cost_threshold_for_imci parameter.
      /*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;
      Creating a new database endpoint ensures that SQL statements are always routed to read-only IMCI nodes for execution. For more information, see Create a new database endpoint.

    2. Check whether the SQL statement’s estimated execution cost exceeds the configured threshold.

      On a read-only IMCI node, the optimizer estimates the execution cost of a SQL statement. If the estimated execution cost exceeds the threshold set by loose_imci_ap_threshold or loose_cost_threshold_for_imci, the optimizer uses an IMCI. Otherwise, it uses the existing row-based indexes.

      After you confirm that an SQL statement is forwarded to a read-only IMCI node, if the execution plan viewed by using EXPLAIN still does not use the IMCI, you can compare the estimated execution cost with the preset threshold to determine if the IMCI was not used because the estimated execution cost was too low. You can query the Last_query_cost_for_imci variable to obtain the estimated execution cost of the previous SQL statement:

      -- Use EXPLAIN to view the execution plan of the SQL statement.
EXPLAIN SELECT * FROM t1;
-- Get the estimated execution cost of the previous query.
SHOW STATUS LIKE 'Last_query_cost_for_imci';
      If you use a cluster endpoint to connect to the database, we recommend that you add the HINT syntax /*ROUTE_TO_LAST_USED*/ before SHOW STATUS LIKE 'Last_query_cost_for_imci'. This ensures that you can query the estimated execution cost of the previous statement on the correct node. For example, /*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost_for_imci';

      If the SQL statement's estimated execution cost is lower than the configured threshold, consider adjusting the value of loose_imci_ap_threshold or loose_cost_threshold_for_imci. For example, you can use a HINT to adjust the threshold for a single SQL statement:

      /*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

    3. Verify that all tables and columns in the SQL statement are fully covered by an IMCI.

      You can call the built-in stored procedure dbms_imci.check_columnar_index('<query_string>') to check whether an IMCI has been created for the tables or columns in a SQL statement. For more information, see Check whether an IMCI has been created for the tables or columns in a SQL statement. Example:

      CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');

      If the SQL statement is not fully covered by an IMCI, calling this stored procedure returns the uncovered tables and columns. You must create an IMCI for each returned table and column. If the statement is fully covered, the procedure returns an empty result set.

    4. Check for unsupported SQL features.

      Check the IMCI syntax usage limitations to confirm whether a specific SQL feature supports IMCI. For more information, see IMCI syntax usage limitations.

    If the SQL statement still does not use an IMCI after you perform the preceding checks, you can refer to the usage guide for assistance or contact us.

    For more information, see IMCI FAQ.

    Advanced usage

    See the following topics to optimize your use of IMCI.

    Advanced feature

    Description

    Set a sort key for an IMCI

    IMCI organizes data into row groups. In each row group, different columns are packed into column data blocks. These blocks are built in parallel based on the primary key order of the original row-based data, which results in an overall unordered state. You can configure a sort key to reorder the column data blocks and improve query performance.

    Analyze JSON data efficiently

    IMCI integrates a set of features such as columnar JSON, virtual columns, Instant DDL, and column count expansion to handle large-scale structured and semi-structured data analytics.

    Accelerate ETL with IMCI

    The Extract, Transform, Load (ETL) feature lets you use IMCI on a read-write (RW) node. SELECT requests issued on the RW node are sent to a read-only IMCI node for accelerated queries. After data is read, the system transfers it back to the RW node over the internal network and writes it to the destination table.

    Enable Serverless for read-only IMCI nodes

    The Serverless feature automatically scales resources up and down based on your workload. It scales up during peak hours to handle workload spikes, and scales down during off-peak hours to reduce costs.

    Accelerate wide table queries with Hybrid Plan

    A Hybrid Plan uses both column and row indexes in the same query. Hybrid Plan can significantly improve wide table query performance. In the execution plan, parts that benefit from column indexes are run on IMCI and produce intermediate results that contain only primary keys. Then, the required columns are fetched and returned by using the primary keys with the InnoDB primary index.

    Accelerate analysis of massive data with multi-node MPP

    For complex queries on massive data, a single read-only IMCI node may not meet performance requirements. You can use multi-node parallel processing (MPP) to accelerate queries.

    Learn more

    To learn more about the principles behind IMCI, see the following documents: