Parameterized Requests | VantageCloud Lake - Parameterized Requests - Teradata Vantage

Vantage has the ability to peek at the parameterized values of a request (such as USING request modifier values, which are user-supplied constant data) in CLIv2 data parcels, and then to resolve those variables in the Resolver. This enables the Optimizer to generate a specific, uncached plan for such requests when the system determines this is the best way to handle the request rather than generating a cached generic plan.

Data Parcel Peeking Terminology

The following terminology is specific to the parameter value peeking subsystem:

The presence of parameterized data in a request may not mean the Optimizer generates a request plan for the request. There are parameterized queries for which the query plan generated by the Optimizer does not depend on parameterized values. Such queries do not gain a performance benefit from generating specific query plans as the result of parameterized value peeking. Furthermore, the negative performance impact of not caching a plan can be high if a request has a short run time.

For example, consider the following query that specifies a USING request modifier whose single variable x is also used to specify a parameterized predicate condition :x in its WHERE clause.

USING (x INTEGER)
SELECT *
FROM table_1
WHERE column_1 = :x;

If there is a UPI defined on table_1.column_1, then the access path selected by the Optimizer is independent of the value of x because irrespective of its value, assuming that value specifies a valid primary index, a primary index access path is the best access path.

In the context of the parameter value peek subsystem, the following definitions apply:

Parameterized value peeking does not impact the treatment of parameterized PK requests, so the Optimizer does not generate a specific plan for those cases.

The following table shows whether parameterized requests are parameterized-dependent or parameterized-independent.

CREATE TABLE emp (
  emp_id INTEGER,
  dept_id INTEGER)
UNIQUE PRIMARY INDEX (emp_id);

USING (x INTEGER)
SELECT *
FROM emp
WHERE dept_id = 10
;SELECT *
FROM emp
WHERE emp_id = :x
AND   dept_id = 12;

Parameterized Value Peeking

Peeking facilitates the optimization of certain categories of queries by inserting data values that are specific to those queries into the parse tree at an earlier stage of request processing. The system caches generic plans because reusing a cached query plan saves parsing and optimization time. However, reusing a cached generic query plan is not always the best approach to executing a query that provides constant literals that can be used to produce a specific plan tailored closely to that specific request.

The system does not cache specific plans for requests that are parameterized-dependent, because those plans cannot be reused for otherwise identical queries that have different sets of parameterized or built-in function-supplied constant values. The system does cache the other information generated for specific plans. This information includes its SQL text hash, its host character set, its estimated execution costs, its parsing time, and its run time.

When the system determines not to cache a plan for a request, the system can optimize the request using the parameterized request and DATE, CURRENT_DATE, TEMPORAL_DATE, CURRENT_TIMESTAMP, USER, andTEMPORAL_TIMESTAMP built-in function values instead of treating that data as parameters with unknown values. That is, parameterized value peeking uses the literal data values from a parameterized request and date-related built-in functions, or both, to make sure the Optimizer generates an optimal, uncached plan for that request rather than generating a generic plan and then caching that plan for future reuse.

A specific plan generated by the Optimizer must be an optimal plan, and its runtime performance must be significantly better than the run time for an equivalent generic plan. However, the runtime costs of a specific plan and its equivalent generic plan may not differ significantly. The impact is high if the parsing cost is high. Therefore, the system monitors the parsing and run times of such requests and keeps the information in the request cache, to use in deciding between generating a specific or generic plan for a request. See Request Caching Logic.

Enabling and Disabling Parameterized Value Peeking

By default, parameterized value peeking is enabled.

Miscellaneous Considerations for Parameterized Value Peeking

Request Caching Logic

The request cache stores the plans of successfully parsed SQL DML requests to reuse when the same request is resubmitted. The request cache contains the text of the SQL request and its plastic steps. The plastic steps are called the request plan or simply as the plan.

• When parameterized value peeking is enabled, requests are cached or not according to the actions described by the following table.

Otherwise, the database parses the request again and generates a specific plan for it.

Otherwise, the Optimizer generates the specific plan for all requests until the cache is cleared.

A performance benefit is expected to be seen when the system decides to generate specific plans for all subsequent submittals of the request. A performance benefit is expected to be seen when the system decides to use the cached plan for all subsequent submittals of the request.

You can submit a MONITOR SESSION request to determine the number of request cache hits in a session. You can then use that information to determine whether a generic plan has been cached and used.

A specific plan is typically better than its equivalent generic plan, because its parameterized values are substituted as literals. The specific plan and equivalent generic plan may have the same cost. The logic that replaces the parameterized values to generate a specific plan cannot distinguish between queries for which the estimated specific and generic plan times are identical.

The request plans for the following two requests are identical, and peeking at parameterized values cannot help to generate more optimal plans for them. But requests such as the previous SELECT example can benefit from parameterized value peeking if a join index like the following is defined on the target base table for the query.

For example, consider the following table definition (on the Block File System).

CREATE SET TABLE MYDB.t2, NO FALLBACK,NO BEFORE JOURNAL,
  NO AFTER JOURNAL,CHECKSUM = DEFAULT (
  i INTEGER,
  j INTEGER,
  k INTEGER,
  l INTEGER,
  c CHARACTER(400) CHARACTER SET LATIN NOT CASESPECIFIC
                   DEFAULT 'a')
PRIMARY INDEX (i)
INDEX (j);

The statistics collected on the table show the following number of unique values for the NUSI column j and the NUPI column i.

Date     Time     Unique Values      Column Names
-------- -------- ------------------ --------------------------
06/02/17 12:51:22              1,537 j
06/02/17 12:56:10                 60 k

Now consider the following query against table t2, showing the relevant step:

EXPLAIN USING (a INTEGER) SELECT *
                          FROM t2
                          WHERE j = 58
                          AND   k = 100000+i;

...
3) We do an all-AMPs RETRIEVE step from MYDB.t2 by way of an all-rows
   scan with a condition of ("(MYDB.t2.k = (100000 + MYDB.t2.i )) AND
   (MYDB.t2.j = 58)") into Spool 1 (group_amps), which is built
   locally on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 10 rows. The estimated time for this step is
   0.17 seconds.
...

Also consider the following query, which is also made against table t2, showing the relevant step:

EXPLAIN USING (a INTEGER) SELECT *
                          FROM t2
                          WHERE j = 58
                          AND   k =:a + i;

...
3) We do an all-AMPs RETRIEVE step from MYDB.t2 by way of an all-rows
   scan with a condition of ("(MYDB.t2.k = (:a + MYDB.t2.i )) AND
   (MYDB.t2.j = 58)") into Spool 1 (group_amps), which is built
   locally on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 10 rows. The estimated time for this step is
   0.17 seconds.
...

Now create table t3 as a copy of table t2 with data and consider the following join index definition:

CREATE JOIN INDEX j1 AS
  SELECT *
  FROM t3
  WHERE j > 58
  AND k > i+3;

Consider the following query and assume the USING value for a1 is 4:

EXPLAIN USING (a1 INTEGER)
        SELECT *
        FROM t3
        WHERE j = 80
        AND   k = i+:a1;

The value 4 has been explicitly inserted in the text for step 3.

...
3) We do an all-AMPs RETRIEVE step from MYDB.J1 by way of an 
   all-rows scan with a condition of 
   ("(MYDB.J1.k = (MYDB.J1.i +  4  )) AND
   (MYDB.J1.j = 80)") into Spool 1 (group_amps), which is built
   locally on the AMPs. The size of Spool 1 is estimated with no
   confidence to be 126 rows. The estimated time for this step is
   0.14 seconds.
...