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:
EXPLAIN
<SQL-command>;
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>
