NOPARAM

Syntax

NOPARAM (expression)

Arguments

• expression: any expression. This may be a constant (most common), column name, the result of another function, or a math operation.

Remarks

• When NOPARAM is used, the constants in a query are not converted into parameters. So, a separate query plan is generated even for the queries with the same structure.

• Parameterization can only be disabled for constants in a query.

Examples

Example 1: Let us look at few queries and their query plans when NOPARAM function is used. A query plan can be viewed in the QUERY_TEXT field of the information_schema.PLANCACHE table.

For example, the following two queries with different parameter values,

SELECT * from t WHERE x = 5;
SELECT * from t WHERE x = 30;

use the same query plan:

QUERY_TEXT: SELECT * from t WHERE x = @

Whereas, when the constants in these queries are enclosed in NOPARAM, a separate plan is generated for each query. For example, for the following queries,

SELECT * from t WHERE x = NOPARAM(5);
SELECT * from t WHERE x = NOPARAM(30);

different query plans are generated:

QUERY_TEXT: SELECT * from t WHERE x = NOPARAM(5)
QUERY_TEXT: SELECT * from t WHERE x = NOPARAM(30)

Example 2: Let us look at an example where NOPARAM is used for variable offsets. Consider a table orders with two fields orderid and orderdate.

The following queries with different parameter values generate the same query plan, which can be viewed in the QUERY_TEXT field of the information_schema.PLANCACHE table.

SELECT COUNT(orderid) FROM orders WHERE orderdate <= DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY);
SELECT COUNT(orderid) FROM orders WHERE orderdate <= DATE_ADD(CURRENT_DATE(), INTERVAL -20 DAY);

QUERY_TEXT: SELECT COUNT(orderid) FROM orders WHERE orderdate <= DATE_ADD(CURRENT_DATE(), INTERVAL @ DAY)

Whereas when NOPARAM function is used for the same queries,

SELECT COUNT(orderid) FROM orders WHERE orderdate <= NOPARAM(DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY));
SELECT COUNT(orderid) FROM orders WHERE orderdate <= NOPARAM(DATE_ADD(CURRENT_DATE(), INTERVAL -20 DAY));

different query plans are generated:

QUERY_TEXT: SELECT COUNT(orderid) FROM orders WHERE orderdate <= NOPARAM(DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY))
QUERY_TEXT: SELECT COUNT(orderid) FROM orders WHERE orderdate <= NOPARAM(DATE_ADD(CURRENT_DATE(), INTERVAL -20 DAY))

Other examples of NOPARAM function usage:

INSERT INTO t(a) VALUES (NOPARAM(20));

SELECT NOPARAM(x) + NOPARAM(SUM(x)) FROM t;