The Explain Command in Teradata provides an English explanation of the steps that the AMP (Access Module Processor) goes through while running a SQL query. As you know it is the job of Parsing Engine (PE) to create the steps for accessing the data. When the ‘EXPLAIN’ command is submitted with any SQL statement, the PE builds the steps in a readable format and sends it back to the client as a query result. Remember that 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. 

1. It gives an over all 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.
2. It gives an estimate of number of rows to be returned with a confidence level as NO, LOW and HIGH.
3. It gives an estimated time (cost) that would take to complete the SQL request.
4. 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 EXPLAIN;

EXPLAIN<SQL STATEMENT>;

Syntax for STATISTICS;

COLLECT STATISTICS ON <table name> COLUMN <column name>

COLLECT STATISTICS ON <table name> INDEX <column name>

HELP STATISTICS <table name>

Our Random Articles

• Somdev Devvarman ousts Xavier Malisse 6-1, 3-6,7-6(5)
• How To Choose A Good Online Traffic School
• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns

More Links