Optimizing Queries for a Parallel Environment
The Teradata Database query optimizer is designed to optimize queries executed in a parallel environment. Unlike the optimizers for many other competing relational database management systems, the Teradata Database optimizer, because it is designed only to optimize queries in a parallel architecture, does not first optimize for a nonparallel environment and then perform further optimizations to take advantage of a parallel architecture.
When a query optimizer works out the cost of performing a 6 table join in various ways, it must know, within limits, how long each operation required to perform the query takes to perform so it can determine the optimal ordering of the joins. To gain insight into how important this evaluation can be, consider the following table:
|
Number of Tables Joined |
Number of Possible Join Orders |
|
4 |
24 |
|
6 |
720 |
|
8 |
40,320 |
|
10 |
3,628,800 |
An optimizer that estimates operational costs based on a single‑threaded model will make inappropriate decisions for a parallel processing system. Using the previous table as a guide, you can easily see that any such inappropriate decisions are magnified exponentially as the number of tables to be joined increases.
See SQL Request and Transaction Processing for more information about how Teradata Database performs query optimization.