What is explain in teradata?

What is explain and how does it work?

Questions by dheeraj reddy chala

Editorial / Best Answer

Madhulathamadduri  

  • Member Since Jan-2010 | Apr 15th, 2011

The EXPLAIN modifier preceding an SQL statement generates an
English translation of the optimizer’s plan. It is fully parsed and
optimized, but not executed.
EXPLAIN returns:
Text showing how a statement will be processed (a plan).
An estimate of how many rows will be involved.
A relative cost of the request (in units of time).
This information is useful for:
• Predicting row counts.
• Predicting performance.
• Testing queries before production.
• Analyzing various approaches to a problem.
EXPLAIN may also be accessed using Teradata Visual Explain.

Showing Answers 1 - 5 of 5 Answers

sakthiganesht

  • Feb 7th, 2008
 

Explain is a fn using which you can find the execution procedure of any query in sql assistant. To use this fn type Explain before any query and run it or press F6 after writing a query. It also gives the estimated time, join confidence and memory needed to execute that query. It's advisable to use explain before executing any complex query.

  Was this answer useful?  Yes

A4ananth

  • Feb 14th, 2008
 

 The EXPLAIN facility is a teradata extension that provides you with an "ENGLISH" translation of the steps choosen by the optimizer to execute an SQL statement.It may be used oin any valid teradata database with a preface called "EXPLAIN".

The following is an example:-

 EXPLAIN select last_name,first_name FROM employees;

The EXPLAIN parses the SQL statement but does not execute it.

This provides the designer with an "execution stratergy".

The execution stratergy provides what an optimizer does but not why it choses them.

The EXPLAIN facility is used to analyze all joins and complex queries.

  Was this answer useful?  Yes

Madhulathamadduri

  • Apr 15th, 2011
 

The EXPLAIN modifier preceding an SQL statement generates an
English translation of the optimizer’s plan. It is fully parsed and
optimized, but not executed.
EXPLAIN returns:
Text showing how a statement will be processed (a plan).
An estimate of how many rows will be involved.
A relative cost of the request (in units of time).
This information is useful for:
• Predicting row counts.
• Predicting performance.
• Testing queries before production.
• Analyzing various approaches to a problem.
EXPLAIN may also be accessed using Teradata Visual Explain.

  Was this answer useful?  Yes

yuvaevergreen

  • Jun 6th, 2011
 

Explain statement generally gives the step by step analysis of the queries being executed in the database. It is the plan prepared by PE dispatched to the amps.

It could also identify intermediate spool files that would be generated in the process. In addition, EXPLAINS could show whether the statements in a transaction would be completed in parallel.

 

  Was this answer useful?  Yes

senthang

  • Jun 22nd, 2011
 

1. English version on optimizer plan
2. to identify the objects used and kind of locks applied on those objects
3. to identify the number of amp operation eg. single ot group amp
4. to identify the data conversion in differnt datatype join cols
5. to identify translation of charater set on join columns
6. to indentify the level of confidence
7. to identify the output estimated rows
8. to indentify the kind of joins used to process the request
9. to identify the duplication/redistribution of tables
10 to identify the dynamic partition elimination if the table has PPI

and so on...

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions