PolarDB:Create and use a CCI

Specify an index using a hint

Syntax:

FORCE INDEX({index_name})

Example:

SELECT a.*, b.order_id 
 FROM t_seller a 
   JOIN t_order b FORCE INDEX(cc_i_seller) ON a.seller_id = b.seller_id 
 WHERE a.seller_nick="abc";

Do not use a specified index (IGNORE INDEX)

Syntax:

IGNORE INDEX({index_name},...)

The following statement specifies that the optimizer does not use the cc_i_seller index:

SELECT t_order.id,t_order.order_snapshot FROM t_order IGNORE INDEX(cc_i_seller) WHERE t_order.seller_id = 's1';

Use a specified index (USE INDEX)

Syntax:

USE INDEX({index_name},...)

The following statement specifies that the optimizer uses the cc_i_seller index:

SELECT t_order.id,t_order.order_snapshot FROM t_order USE INDEX(cc_i_seller) WHERE t_order.seller_id = 's1';

Supported data types

The following table shows the supported data types for the primary key of the primary table, as well as the sort and partition keys of the CCI:

Limits on DDL statements

SET [GLOBAL] forbid_ddl_with_cci = [true | false];

• The following table describes the DDL support for primary tables that contain CCIs and for CCIs.

  Category	Action	Sample SQL	Supported
  Primary table	Delete table	DROP TABLE tbl_name;	Yes
  	Truncate table	TRUNCATE TABLE tbl_name;	Yes
  	Rename table	ALTER TABLE old_tbl_name RENAME TO new_tbl_name; RENAME TABLE old_tbl_name TO new_tbl_name;	Yes
  	Rename multiple tables	RENAME TABLE tbl_name_a to tbl_name_b, tbl_name_c to tbl_name_d;	Yes
  	Add column	ALTER TABLE tbl_name ADD col_name TYPE;	Yes
  	Delete column	ALTER TABLE tbl_name DROP COLUMN col_name;	Yes
  	Modify column type	ALTER TABLE tbl_name MODIFY col_name TYPE;	Yes
  	Rename (modify) column	ALTER TABLE tbl_name CHANGE old_col new_col TYPE;	Yes
  	Modify column default value	ALTER TABLE tbl_name ALTER COLUMN col_name SET DEFAULT default_value; ALTER TABLE tbl_name ALTER COLUMN col_name DROP DEFAULT;	Yes
  	Lock-free column type change	ALTER TABLE tbl_name MODIFY col_name TYPE, ALGORITHM = omc;	Yes
  	Multiple ALTER TABLE operations	ALTER TABLE tbl_name MODIFY col_name_a, DROP COLUMN col_name_b;	Yes
  	Generated column	-	No
  	Partition change	-	No
  CCI	Create CCI	CREATE CLUSTERED COLUMNAR INDEX cci_name; ALTER TABLE tbl_name ADD CLUSTERED COLUMNAR INDEX cci_name;	Yes
  	Delete CCI	DROP INDEX cci_name ON TABLE tbl_name; ALTER TABLE tbl_name DROP INDEX cci_name;	Yes
  	Change CCI name	ALTER TABLE tbl_name RENAME INDEX cci_name_a TO cci_name_b;	Yes
  	Add a RANGE partition to a CCI	ALTER TABLE `tbl_name`.`cci_name` ADD PARTITION;	Yes
  	Other CCI partition changes	-	No

• The following constraints apply when you use ALTER TABLE to change columns:

  Statement	Can the primary key be changed?	Can the index partition key be changed?	Can the sort key be changed?
  MODIFY COLUMN	Yes	Yes	Yes
  ALTER COLUMN SET DEFAULT, ALTER COLUMN DROP DEFAULT	Yes	Yes	Yes
  ADD COLUMN	No	N/A	N/A
  CHANGE COLUMN	No	No	No
  DROP COLUMN	No	No	No

  Note

  • Currently, you can execute ALTER TABLE statements related to column changes on tables that contain a CCI.

  • In versions 5.4.20-20250714 and later, MODIFY COLUMN and changing column default values are supported for modifying some primary keys, CCI partition keys, and CCI sort keys.

  • Modifying a critical column (primary key, CCI partition key, or CCI sort key) may trigger a full rebuild of the CCI. This can be a long-running operation on large tables. This is disabled by default. We recommend that you use asynchronous execution. To use this feature, set the following parameters:

    # Allow modifying critical columns of a CCI
    SET ENABLE_MODIFY_CCI_CRITICAL_COLUMN = TRUE;
    # The rebuild logic for critical columns. 0 indicates that the system automatically selects a logic based on the policy. We recommend that you do not change this value.
    SET REBUILD_CCI_STRATEGY = 0;
    # The maximum number of CCIs allowed in a table. If multiple CCIs already exist in the table, you can set this value to a larger number.
    SET MAX_CCI_COUNT = 2;

• The following table describes the supported data type for the MODIFY/CHANGE COLUMN statement:

  Supported types	Unsupported types
  Numeric types: BIT (UNSIGNED), TINYINT (UNSIGNED), SMALLINT (UNSIGNED), MEDIUMINT (UNSIGNED), INT (UNSIGNED), and BIGINT (UNSIGNED) Time types: DATETIME, TIMESTAMP, TIME, and YEAR Floating-point number types: FLOAT, DOUBLE, DECIMAL, and NUMERIC String types: CHAR, VARCHAR, TEXT, BINARY, VARBINARY, and BLOB Special types: JSON, ENUM, and SET	Special types: POINT and GEOMETRY

  Note

  If you want to change a column to an unsupported data type, you can use the DROP INDEX statement to delete the CCI, change the data type of the column, and then recreate the CCI.

• The following table describes the support for changing indexes using the ALTER TABLE statement:

  Statement	Supported
  ALTER TABLE ADD PRIMARY KEY	Yes
  ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY	Yes
  ALTER TABLE DROP INDEX	Yes
  ALTER TABLE DROP FOREIGN KEY fk_symbol	Yes
  ALTER TABLE DROP PRIMARY KEY	No. Prohibited
  ALTER TABLE RENAME INDEX	Yes. You can modify the name of a CCI.
  ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE}	No. You cannot modify a CCI.
  ALTER TABLE {DISABLE | ENABLE} KEYS	No. You cannot modify a CCI.