All Products
Search

This Product

    PolarDB:IMCI-related parameters

    Document Center

    PolarDB:IMCI-related parameters

    Last Updated:Jun 11, 2025

    This topic describes the parameters related to the In-Memory Column Index (IMCI) feature.

    Parameter

    Description

    loose_imci_auto_update_statistic

    Specifies whether the IMCI query optimizer re-collects statistics when the statistics are not the latest. Valid values:

    • ASYNC (default): The IMCI query optimizer uses asynchronous sampling and re-collects statistics when the statistics are not the latest.

    • OFF: The IMCI query optimizer does not re-collect statistics when the statistics are not the latest.

    • SYNC: The IMCI query optimizer uses synchronous sampling and re-collects statistics when the statistics are not the latest.

    loose_imci_enable_window_function

    Specifies whether the IMCI feature is supported for the SQL statements that contain window functions. Valid values:

    • 2 (default): The IMCI feature is supported for the SQL statements that contain window functions.

    • 1: The IMCI feature is supported for the SQL statements that contain window functions without frames specified.

    • 0: The IMCI feature is not supported for the SQL statements that contain window functions.

    loose_imci_max_enum_join_pairs

    The number of equivalent execution plans that the IMCI query optimizer can retrieve when the IMCI and join reordering features are enabled.

    Valid values: 0 to 4294967295. Default value: 2000.

    loose_imci_min_virtual_memory

    The minimum amount of memory occupied by an IMCI during a query.

    Valid values: 1 to 9223372036854775807. Default value: DBNodeClassMemory*3/20. Unit: bytes.

    loose_imci_max_virtual_memory

    The maximum amount of memory occupied by an IMCI during a query.

    Valid values: 1 to 9223372036854775807. Default value: 9223372036854775807. Unit: bytes.

    loose_imci_optimizer_switch

    Specifies whether to use a hybrid plan to accelerate wide-table queries that meet the conditions. Valid values:

    • 'force_hybrid_index_search=OFF' (default): does not force the use of a hybrid plan to accelerate wide-table queries. The optimizer automatically chooses the best plan.

    • 'force_hybrid_index_search=ON': forces the use of a hybrid plan to accelerate wide-table queries.

    Note

    If the table involved in the query statement does not have statistics or the cardinality estimation and cost calculation for IMCIs are not enabled, join reorder for IMCIs is not enabled even if this parameter is set to ON.

    loose_imci_ap_threshold

    The threshold that determines whether SQL statements are distributed to column store nodes.

    Valid values: 1 to 18446744073709551615. Default value: 50000.

    Note

    For example, when the default value is used, if the number of rows scanned by an SQL statement is estimated to be larger than 50,000, the statement is distributed to a column store node.

    loose_cost_threshold_for_imci

    The threshold that determines whether SQL statements are executed by using column store execution plans in column store nodes.

    Valid values: 1 to 18446744073709551615. Default value: 50000.

    Note

    For example, when the default value is used, if the number of rows scanned by an SQL statement is estimated to be larger than 50,000, the statement is executed by using a column store execution plan. Otherwise, a row store execution plan is used.

    loose_imci_enable_query_trace

    Specifies whether to print kernel error logs during IMCI query optimization. Valid values:

    • OFF (default)

    • ON

    loose_polar_enable_implicit_imci_with_create_table

    Specifies whether to add IMCIs that are valid entire tables to all new tables. Valid values:

    • OFF (default)

    • ON

    loose_imci_columnar_advise_buffer_size

    The amount of memory available for the dbms_imci.columnar_advise(); stored procedure.

    Valid values: 0 to 18446744073709551615. Default value: 8388608. Unit: bytes.

    loose_etl_from_imci

    Specifies whether to read data from read-only column store nodes. Valid values:

    • OFF (default)

    • ON

    loose_etl_from_imci_compress

    Specifies whether to compress files when data is read from read-only column store nodes. Valid values:

    • OFF (default)

    • ON

    loose_imci_default_pack_shift

    The row group size for IMCIs.

    Valid values: 7 to 18. Default value: 14.

    Note

    For a cluster of PolarDB for MySQL 8.0.1.1.36 or later, the default value of this parameter is 16.

    loose_slow_log_record_imci

    Specifies whether to add IMCI information to slow query logs. Valid values:

    • OFF (default)

    • ON

    loose_imci_default_write_policy

    The write policy for IMCI data. Valid values:

    • Tradeoff (default): a policy optimized on the basis of Skip4K. When less than 1 MB data is to be written, it is equivalent to ForPerformance. When 1 MB data or more is to be written, try to split the data into multiple files.

    • ForCapacity: Storage space is prioritized. Data is split into multiple files and written across these files to minimize fragmentation. A maximum of one fragment of 4 KB is generated in each write.

    • ForPerformance: Performance is prioritized. A file with the optimal ExtentSize value is selected for each write. This policy ensures the best I/O performance, but results in the maximum fragmentation percentage and high space waste.

    • Skip4K: uses a data splitting logic that is similar to ForCapacity. When more than 4 KB data is to be written, the 4 KB extent is ignored. A maximum of one fragment of 16 KB is generated in each write.