Dynamic parameters - OpenSearch - Alibaba Cloud Documentation Center

Overview

You can use question marks (?) as placeholders in an SQL statement to specify dynamic parameters. The iquan component of OpenSearch Retrieval Engine Edition automatically converts the question marks (?) to the corresponding dynamic parameters.

Dynamic parameters can help improve the hit ratio of execution plans that the system caches. If you write SQL statements based on the same template and use dynamic parameters in the SQL statement, query performance is increased.

Note: You can use dynamic parameters to replace only values. You cannot use dynamic parameters to replace keywords or fields.

Supported versions

OpenSearch Retrieval Engine Edition V3.7.0 and later

Parameters

You need to configure the following parameters in a kvpair clause to specify a dynamic parameter.

Parameter	Description	Example	Supported version
iquan.plan.prepare.level	The query phase in which you want the system to replace a question mark (?) in the result with the value of the dynamic parameter. Default value: jni.post.optimize.	kvpair=...;iquan.plan.prepare.level:jni.post.optimize;...	For OpenSearch Retrieval Engine Edition of versions from V3.7.0 to V3.7.2, this parameter is required. For OpenSearch Retrieval Engine Edition of Beta version, this parameter is optional. If you do not specify this parameter, the default value jni.post.optimize is used.
dynamic_params	The parameters that you want to use to replace the results. Note: The data types of parameter values must be the same. Dynamic parameters are a two-dimensional array. Each one-dimensional array corresponds to an SQL statement.	kvpair=...;dynamic_params=[[1, 1.23, "str"]]	V3.7.0 and later
iquan.plan.cache.enable	Specifies whether to enable the execution plan cache feature for the phase that you specified for the iquan.plan.prepare.level parameter. If you use a dynamic parameter in an SQL statement and enable the execution plan cache feature, OpenSearch Retrieval Engine Edition caches the execution plan of the SQL statement. When you execute the same SQL statement again, OpenSearch Retrieval Engine Edition obtains the execution plan from the cache. In this case, the query execution time is reduced.	kvpair=...;iquan.plan.cache.enable:true;...	V3.7.0 and later

Examples

In the following sample statement, only dynamic parameters are used.

SELECT i1, cast(? as bigint) FROM t1 WHERE (i2 > 5 AND d3 < 10.1) OR s5 = ?

Before you use question marks (?) to specify dynamic parameters in an SQL statement, you must create dynamic parameters by using a kvpair clause. The following sample code shows how to specify dynamic parameters in the kvpair clause:

kvpair=...;
                         iquan.plan.prepare.level:jni.post.optimize;
       dynamic_params:[[10, "str5"]];
       ...;

In the following sample statement, dynamic parameters are used and the execution plan cache feature is enabled.

SELECT
    price,
    title,
    compute(
        longitude,
        latitude,
        city_id,
        CAST(? AS double),
        CAST(? AS double),
        CAST(1 AS bigint)
    ) AS distance
FROM
    store,
    unnest(store.sub_table)
WHERE
    MATCHINDEX('shop', ?)
    AND QUERY(name, ?)

The following sample code shows how to specify dynamic parameters in a kvpair clause:

kvpair= ...;
                         iquan.plan.cache.enable:true;
                         iquan.plan.prepare.level:jni.post.optimize;
       dynamic_params:[[119.98844256998, 
                        36.776817017143, 
                        "excellect", 
                        "Fruit OR Watermelon"]]
       ...