Use question marks (?) as placeholders in SQL statements to pass values at query time. The iquan component of OpenSearch Retrieval Engine Edition substitutes each ? with the corresponding value from the dynamic_params array before executing the query. This keeps values separate from the query structure, improving execution plan cache hit ratios — queries built from the same SQL template share a single cached execution plan, reducing execution time.
Dynamic parameters can replace values only. They cannot replace SQL keywords or field names.
Supported versions
OpenSearch Retrieval Engine Edition V3.7.0 and later.
How it works
Without dynamic parameters, values are embedded directly in the SQL statement:
SELECT i1, CAST(10 AS bigint) FROM t1 WHERE (i2 > 5 AND d3 < 10.1) OR s5 = 'str5'With dynamic parameters, ? placeholders replace the values, which are supplied separately in the kvpair clause:
SELECT i1, CAST(? AS bigint) FROM t1 WHERE (i2 > 5 AND d3 < 10.1) OR s5 = ?kvpair=...;
iquan.plan.prepare.level:jni.post.optimize;
dynamic_params:[[10, "str5"]];
...;Queries that share the same SQL template — differing only in the values supplied via dynamic_params — reuse the cached execution plan. Subsequent executions skip plan compilation entirely.
Parameters
Configure the following parameters in the kvpair clause.
| Parameter | Description | Default | Version |
|---|---|---|---|
iquan.plan.prepare.level | The query phase in which iquan replaces ? placeholders with the supplied values. | jni.post.optimize | Required for V3.7.0–V3.7.2; optional for Beta (defaults to jni.post.optimize if omitted) |
dynamic_params | A two-dimensional array of replacement values. Each inner array corresponds to one SQL statement. The data types of parameter values must be the same. | — | V3.7.0 and later |
iquan.plan.cache.enable | Enables execution plan caching for the phase set by iquan.plan.prepare.level. When enabled, OpenSearch Retrieval Engine Edition caches the execution plan and reuses it for subsequent identical queries, reducing execution time. | — | V3.7.0 and later |
Examples
Use dynamic parameters
The following SQL statement uses two ? placeholders — the first is cast to bigint, the second is matched against a string field:
SELECT i1, CAST(? AS bigint) FROM t1 WHERE (i2 > 5 AND d3 < 10.1) OR s5 = ?Specify the replacement values and the prepare level in the kvpair clause:
kvpair=...;
iquan.plan.prepare.level:jni.post.optimize;
dynamic_params:[[10, "str5"]];
...;Use dynamic parameters with execution plan caching
The following example queries a geospatial dataset. It uses four ? placeholders for a longitude, a latitude, a shop category, and a product keyword:
SELECT
price,
title,
compute(
longitude,
latitude,
city_id,
CAST(? AS double), -- longitude input
CAST(? AS double), -- latitude input
CAST(1 AS bigint)
) AS distance
FROM
store,
unnest(store.sub_table)
WHERE
MATCHINDEX('shop', ?) -- shop category
AND QUERY(name, ?) -- product keywordEnable execution plan caching and supply replacement values in the 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"]]
...After the first execution, OpenSearch Retrieval Engine Edition caches the execution plan. Subsequent queries with different values in dynamic_params reuse the cached plan without recompiling.