The EXPLAIN command is a powerful tool provided with the Teradata database. It is designed to provide an English explanation of what steps the AMP must complete to satisfy the SQL request. The EXPLAIN is based on the PE's execution plan.
The Parsing Engine (PE) does the optimization of the submitted SQL, the creation of the AMP steps and the dispatch to any AMP involved in accessing the data. The EXPLAIN is an SQL modifier; it modifies the way the SQL operates.
When an SQL statement is submitted using the EXPLAIN, the PE still does the same optimization step as normal. However, instead of building the AMP steps, it builds the English explanation and sends it back to the client software, not to the AMP.
This gives users the ability to see resource utilization, use of indices, and row and time estimates.
The syntax for using the EXPLAIN is simple: just type the EXPLAIN keyword preceding your valid SQL statement. For example:
when you run a query with the EXPLAIN command, it would NOT return any result data set, as mentioned, it just returns the explanation on how the results would be returned, in case the query runs. Following are some advantages of running an EXPLAIN before running the actual query.
It gives an overall explanation on how the data would be fetched, including the table access, AMP’s retrieval, locking of database objects, scanning all the rows or indexes, joining tables etc.
It gives an estimate of number of rows to be returned with a confidence level as NO, LOW and HIGH.
It gives an estimated time (cost) that would take to complete the SQL request.
By analyzing the EXPLAIN command output intelligently, one can save time by optimizing the query for faster processing.
It is always possible that the estimation provided by the EXPLAIN command is not accurate, in this case collecting statistics on the table helps.
Syntax for STATISTICS;
COLLECT STATISTICS ON <table name> COLUMN <column name>
COLLECT STATISTICS ON <table name> INDEX <column name>
HELP STATISTICS <table name>