All Products
Search

This Product

    PolarDB:IMCI-related parameters

    Document Center

    PolarDB:IMCI-related parameters

    Last Updated:Mar 28, 2026

    This topic describes the parameters for configuring the In-Memory Column Index (IMCI) feature in PolarDB for MySQL.

    Quick reference

    ParameterDefaultValid valuesUnit
    loose_imci_auto_update_statisticASYNCASYNC, SYNC, OFF—
    loose_imci_enable_window_function20, 1, 2—
    loose_imci_max_enum_join_pairs20000–4294967295count
    loose_imci_min_virtual_memoryDBNodeClassMemory * 3 / 201–9223372036854775807bytes
    loose_imci_max_virtual_memory92233720368547758071–9223372036854775807bytes
    loose_imci_optimizer_switch'force_hybrid_index_search=OFF''force_hybrid_index_search=OFF', 'force_hybrid_index_search=ON'—
    loose_imci_ap_threshold500001–18446744073709551615row count
    loose_cost_threshold_for_imci500001–18446744073709551615row count
    loose_imci_enable_query_traceOFFOFF, ON—
    loose_polar_enable_implicit_imci_with_create_tableOFFOFF, ON—
    loose_imci_columnar_advise_buffer_size83886080–18446744073709551615bytes
    loose_etl_from_imciOFFOFF, ON—
    loose_etl_from_imci_compressOFFOFF, ON—
    loose_imci_default_pack_shift14 (16 for PolarDB for MySQL 8.0.1.1.36+)7–18—
    loose_slow_log_record_imciOFFOFF, ON—
    loose_imci_default_write_policyTradeoffTradeoff, ForCapacity, ForPerformance, Skip4K—

    loose_imci_auto_update_statistic

    Controls whether the IMCI query optimizer re-collects statistics when the existing statistics are stale.

    • Default value: ASYNC

    • Valid values: ASYNC, SYNC, OFF

    ValueBehaviorWhen to use
    ASYNC (default)Re-collects statistics using asynchronous sampling. The query runs immediately without waiting for the refresh.Most workloads. Minimizes query latency while keeping statistics reasonably current.
    SYNCRe-collects statistics using synchronous sampling. The query waits until statistics are refreshed before running.When plan accuracy is more important than query latency, such as scheduled batch jobs where a suboptimal plan would be costly.
    OFFDisables automatic re-collection. Statistics are not refreshed when stale.Only if you manage statistics manually and want to prevent automatic re-collection from interfering.

    loose_imci_enable_window_function

    Controls whether IMCI acceleration applies to SQL statements that contain window functions.

    • Default value: 2

    • Valid values: 0, 1, 2

    ValueBehaviorWhen to use
    2 (default)IMCI acceleration applies to all SQL statements with window functions, including those with frames specified.Most workloads. Provides the broadest acceleration coverage.
    1IMCI acceleration applies only to SQL statements with window functions that have no frames specified.If you observe correctness issues with framed window functions under IMCI acceleration.
    0IMCI acceleration is disabled for all SQL statements with window functions.Troubleshooting or when window function queries must fall back to row store execution.

    loose_imci_max_enum_join_pairs

    The maximum number of equivalent execution plans the IMCI query optimizer evaluates when both IMCI and join reordering are enabled.

    • Default value: 2000

    • Valid values: 0–4294967295

    • Unit: count

    A higher value allows the optimizer to consider more join orderings, which may improve plan quality for complex multi-table queries at the cost of increased planning time.

    loose_imci_min_virtual_memory

    The minimum amount of memory allocated to an IMCI during a query.

    • Default value: DBNodeClassMemory * 3 / 20

    • Valid values: 1–9223372036854775807

    • Unit: bytes

    The default is derived from the node class memory. Increase this value if IMCI queries are memory-constrained on nodes with large memory.

    loose_imci_max_virtual_memory

    The maximum amount of memory an IMCI can use during a query.

    • Default value: 9223372036854775807

    • Valid values: 1–9223372036854775807

    • Unit: bytes

    The default is effectively unbounded. Set a lower value to cap memory usage per IMCI query when running mixed workloads.

    loose_imci_optimizer_switch

    Controls whether the optimizer forces a hybrid plan to accelerate wide-table queries.

    • Default value: 'force_hybrid_index_search=OFF'

    • Valid values: 'force_hybrid_index_search=OFF', 'force_hybrid_index_search=ON'

    ValueBehavior
    'force_hybrid_index_search=OFF' (default)The optimizer automatically selects the best plan without forcing a hybrid plan.
    'force_hybrid_index_search=ON'Forces a hybrid plan for wide-table queries that meet the conditions.
    Note

    Setting this parameter to ON has no effect if the table has no statistics or if cardinality estimation and cost calculation for IMCIs are not enabled. Join reorder for IMCIs remains disabled in those cases.

    loose_imci_ap_threshold

    The row-count threshold that determines whether a SQL statement is routed to a column store node.

    • Default value: 50000

    • Valid values: 1–18446744073709551615

    • Unit: row count

    If the estimated number of rows scanned by a SQL statement exceeds this threshold, the statement is routed to a column store node. For example, with the default value of 50000, any statement estimated to scan more than 50,000 rows is distributed to a column store node.

    Note

    loose_imci_ap_threshold and loose_cost_threshold_for_imci work together. loose_imci_ap_threshold controls routing to column store nodes. loose_cost_threshold_for_imci controls which execution plan is used after routing. Configure both parameters together to ensure consistent behavior.

    loose_cost_threshold_for_imci

    The row-count threshold that determines whether a column store execution plan is used on a column store node.

    • Default value: 50000

    • Valid values: 1–18446744073709551615

    • Unit: row count

    After a statement is routed to a column store node (see loose_imci_ap_threshold), this threshold determines the execution plan. If the estimated rows scanned exceed this value, a column store execution plan is used. Otherwise, a row store execution plan is used.

    For example, with the default value of 50000, statements estimated to scan more than 50,000 rows use a column store execution plan. All others fall back to a row store execution plan.

    loose_imci_enable_query_trace

    Controls whether the kernel prints error logs during IMCI query optimization.

    • Default value: OFF

    • Valid values: OFF, ON

    Enable this parameter to collect diagnostic information when troubleshooting IMCI query optimization issues. Disable it in production to avoid log overhead.

    loose_polar_enable_implicit_imci_with_create_table

    Controls whether a full-table IMCI is automatically added to every new table at creation time.

    • Default value: OFF

    • Valid values: OFF, ON

    When set to ON, all tables created with CREATE TABLE automatically include a full-table IMCI. This is useful when you want IMCI acceleration applied to all new tables without explicit index creation.

    loose_imci_columnar_advise_buffer_size

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

    • Default value: 8388608 (8 MiB)

    • Valid values: 0–18446744073709551615

    • Unit: bytes

    Increase this value if dbms_imci.columnar_advise() runs out of memory when analyzing large tables.

    loose_etl_from_imci

    Controls whether data is read from read-only column store nodes during ETL operations.

    • Default value: OFF

    • Valid values: OFF, ON

    Set to ON to offload ETL reads to read-only column store nodes, reducing the load on primary nodes.

    loose_etl_from_imci_compress

    Controls whether files are compressed when data is read from read-only column store nodes.

    • Default value: OFF

    • Valid values: OFF, ON

    Enable compression to reduce network transfer size when reading data from read-only column store nodes during ETL operations.

    loose_imci_default_pack_shift (updated in PolarDB for MySQL 8.0.1.1.36)

    The row group size for IMCIs.

    • Default value: 14 (16 for PolarDB for MySQL 8.0.1.1.36 and later)

    • Valid values: 7–18

    Note

    For clusters running PolarDB for MySQL 8.0.1.1.36 or later, the default value is 16.

    loose_slow_log_record_imci

    Controls whether IMCI-related information is appended to slow query logs.

    • Default value: OFF

    • Valid values: OFF, ON

    Enable this parameter to include IMCI metadata in slow query log entries, which helps diagnose slow IMCI queries.

    loose_imci_default_write_policy

    The write policy for IMCI data.

    • Default value: Tradeoff

    • Valid values: Tradeoff, ForCapacity, ForPerformance, Skip4K

    ValuePriorityBehaviorWhen to use
    Tradeoff (default)BalancedBased on Skip4K. Writes smaller than 1 MB behave like ForPerformance; writes of 1 MB or larger try to split the data into multiple files.Most workloads. Balances I/O performance and storage efficiency without manual tuning.
    ForCapacityStorage efficiencySplits data across multiple files to minimize fragmentation. Generates at most one 4 KB fragment per write.When storage space is the primary concern.
    ForPerformanceI/O performanceSelects the file with the optimal ExtentSize for each write. Delivers the best I/O performance but produces the highest fragmentation and space overhead.Write-intensive workloads where performance takes priority over storage efficiency.
    Skip4KModerate efficiencyUses the same splitting logic as ForCapacity, but ignores the 4 KB extent when more than 4 KB is written. Generates at most one 16 KB fragment per write.When you want slightly larger fragments than ForCapacity allows, trading minimal extra fragmentation for simpler write paths.