Optimizer | SQL Request & Transaction Processing | VantageCloud Lake - Optimizer - Teradata Vantage

The SQL Query Optimizer determines an efficient way to access, join, and aggregate the tables required to answer an SQL request.

Questions a Query Optimizer Asks and Their Answers

The Optimizer performs its task of determining a best plan using demographic information about the tables and columns involved in the request and the configuration of the system and numerous heuristic strategies, or rules of thumb.

Ideally, these questions are answered based on statistical data that you have generated using the SQL COLLECT STATISTICS statement (see COLLECT STATISTICS (Optimizer Form)). When database statistics are collected regularly, you can expect the Optimizer to make the best decisions possible.

When the Optimizer needs index or column statistics, it first checks the statistics cache (see Statistics Cache). If the necessary statistics are not cached, the Optimizer retrieves them from DBC.StatsTbl.

If collected statistics no longer reflect the demographics of the data, the Optimizer may not be able to make the best decisions (see Time and Resource Consumption Factors in Deciding How to Collect Statistics, An Example of How Stale Statistics Can Produce a Poor Query Plan, and Stale Statistics). You can set thresholds for recollecting statistics to make sure the system does not unnecessarily recollect statistics. See COLLECT STATISTICS (Optimizer Form).

If no statistics have been collected on indexed columns in a request, the Optimizer makes a snapshot sampling of data and uses that estimate to make a best guess about the optimum data retrieval path (see Dynamic AMP Sampling). Any derived statistics that the Optimizer develops begin with the dynamic AMP sample as a basis for deriving additional statistics. The Optimizer does not use dynamic AMP samples of non-indexed columns to make cardinality estimates.

The degree that this dynamic AMP sample approximates the population demographics for a column or table is directly proportional to the size of the table: the larger the table, the more likely a sample approximates its true global demographics.

Optimizer Processes

Tasks Query Optimizers Do Not Perform

AMP Steps

An AMP step is a data structure that describes an operation to be processed by one or more AMPs to perform a task in response to a request parcel. The combined AMP steps for a request constitute the plan for that request.

The most important steps are the plastic and concrete steps. See Generator.

Parallel Steps

Parallel steps are steps from the same request parcel that can be processed concurrently by the AMPs or a single step in a request parcel that can be processed simultaneously by multiple AMPs, taking advantage of the parallelism inherent in the Vantage architecture. Each parallel step has an independent execution path, running simultaneously with other steps.

The Optimizer groups the task steps that can run in parallel. These parallel steps, which make the best use of the BYNET architecture, are generated by the Optimizer whenever possible.

The EXPLAIN facility explicitly reports parallel steps specified by the Optimizer. The Dispatcher has limits on the number of steps actively running in parallel. Also, checks at the AMP level may block a parallel step until a previous parallel step completes.

Additionally, a step may spawn other steps to which rows are sent. For example, during redistribution. The sending steps and the spawned receiving steps run in parallel, with the message system acting as a pipeline between the sender and receiver.

Common Steps

Processing steps common to two or more SQL statements from the same request parcel or macro are combined by the Optimizer.

For example, consider the following multiple-statement request parcel.

     SELECT employee_number, last_name, ‘Handling Calls’
     FROM employee
     WHERE employee_number IN (SELECT employee_number
                               FROM call_employee)
     ;SELECT employee_number, last_name, ‘Not Handling Calls’
      FROM employee
      WHERE employee_number NOT IN (SELECT employee_number
                                    FROM call_employee);

The Optimizer processes these requests in parallel using a common steps approach as illustrated by the following table:

The Optimizer generates the parallel and common steps for the parcel as shown in the following illustration:

Related Information

For more detailed information about query optimization, see Query Rewrite, Statistics, and Optimization.

For information about how Vantage optimizes join requests, see Join Planning and Optimization and Join Optimizations.