Not all requests are cached, and not all cached requests are cached immediately.
When a request has a data parcel (specified in Teradata SQL by a USING request modifier), the system may cache the data parcel immediately, using a generic plan, or decide to use a specific plan. In this discussion, the term data parcel refers to a data parcel set. A non-iterated request is associated with only one data parcel, while an iterated request is associated with multiple data parcels. A request can also have no data parcels associated with it.
By exposing parameterized values before determining a plan for a request, the system can generate a more optimal plan for a category of specific requests. The term parameterized is used instead of USING because you can send both data and DataInfo parcels with a request without specifying a USING request modifier and to specify a USING request modifier with only a data parcel and no DataInfo parcel. See Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 and Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 for information about Data and DataInfo parcels.
The result is identical if you specify constants instead of USING variables everywhere in the request that the Optimizer considers value predicates. See Parameterized Requests for details about how this is done.
The following examples show SQL statements that produce requests with data parcels:
USING (a INTEGER, b INTEGER, c SMALLINT) INSERT INTO tablex VALUES (:a, :b, :c); USING (d SMALLINT, e INTEGER) EXEC macroy (:d, :e);
If these requests are submitted as part of a data-driven iterative request, multiple data parcels are involved (see Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 or Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 for more information about iterated requests).
The system does not immediately cache macro executions that do not specify USING request modifiers, because such macro executions do not have data parcels. Example of a macro execution without a data parcel:
EXEC macroz (100, 200, 300);
The Parser considers macro parameter values provided at execution time in the request text to be a part of the request parcel, not a part of the data parcel.
If a request does not have a data parcel, its plastic steps are not cached immediately. Instead, a hash value derived from the request text is stored in one of the first-seen entries in the cache management data structure.
If the same request is submitted to the Parser a second time, the request can be considered for dynamic parameterization of literals (DPL). The system may move its entry from the first-seen area into one of the cache entries with the plastic steps. A plan may not be cached until the request is seen a third time. For more information about DPL, see Dynamically Parameterized Requests.