When you use an IMCI to query complex SQL statements, the IMCI must be valid for all columns involved in the SQL statements. PolarDB for MySQL has built in several stored procedures, which allow you to obtain the names of columns for the IMCI is invalid in SQL statements and obtain the DDL statements for creating IMCIs. This topic describes the stored procedures built in PolarDB for MySQL and how to create IMCIs by using the stored procedures.
Built-in stored procedures
PolarDB for MySQL has built in the following stored procedures:
Check whether the columns for which the IMCI is invalid are involved in the SQL statement: dbms_imci.check_columnar_index()
Obtain the DDL statements for creating IMCIs: dbms_imci.columnar_advise() and dbms_imci.columnar_advise_by_columns()
Batch obtain the DDL statements for creating IMCIs: dbms_imci.columnar_advise_begin(), dbms_imci.columnar_advise_show(), and dbms_imci.columnar_advise_end()
Usage
When you use an IMCI to query complex SQL statements, you must check whether the columns for which the IMCI is invalid are involved in the SQL statements. If yes, you can obtain the DDL statement for creating an IMCI for a SQL statement or obtain the DDL statements for batch creating IMCIs for a service. Execute the obtained DDL statements to set the IMCI valid for all columns in the SQL statements, and then use the IMCI to accelerate the query.