The Query Capture Facility, or QCF, provides a method to capture and store the steps from any query plan in a set of predefined relational tables called the query capture database, or QCD.
You create your QCD databases using the procedures described in Database Administration.
The principal source of the captured information in QCD is the white tree produced by the Optimizer, the same data structure used to produce EXPLAIN reports (note that the current implementation of QCD does not represent all the information reported by EXPLAIN). The white tree was chosen because it represents the output of the final stage of optimization performed by the Optimizer.
Statistical and other demographic information in the QCD is captured using the following set of SQL statements.
See SQL Data Manipulation Language and SQL Data Definition Language for further information.
Teradata Database supports the following applications of QCF and QCD.
This utility analyzes various SQL query workloads and recommends candidate indexes to enhance the performance of those queries or candidate columns in the context of the defined workloads
The workload definitions, supporting statistical and demographic data, and index recommendations are stored in various QCD tables.
Visual EXPLAIN also has an option that compares different EXPLAIN reports. This feature can be used to compare visually the white trees of identical queries performed on different hardware configurations or software releases as well as comparing semantically identical but syntactically different DML statements to analyze their relative performance.
See Database Design for information about physical capacity planning for your QCDs.
Note the following facts about the physical implementation of QCD databases.