Explain plan

Database

Explain plan

Hi, could one give some quick steps to understand the explain plan of SQL (F6) in teradata. So that we can tune the query well and get the high performance.
5 REPLIES
Enthusiast

Re: Explain plan

Hi,

There are no quick steps available to understand the explain plan completely.
Please go through the documentation for a day or two.

I've provided some points which can be a start up for you (Hope it helps)

Please refer below for some the terms(There are lotsss more :) ) that you might see
in an explain plan. On seeing them decide whether you want that to happen or not.
Based on that change your query.

- check for the data flow across Spool files (Look at their size mentioned in the plan and
if its too large then try to reduce it by introducing filters or trying follow a sequence where start from small table and move towards a large table but in a logical way)

- Check if some joins result in a extremely large spool file
(If so then try to reduce the number of records that qualify for the joins.
Avoid residual conditions if possible - Though this might not be needed always)

- Look for the term "Redistribution" and "Duplication" (For ex: In a join duplicating a
smaller table is better than redistributing a larger one. If this doesnt happen
collect stats on the smaller table. This might change the plan.)

- See for terms which indicate that the plan is using an index(For UPI - 'using unique primary index,
For NUPI - 'Using primary index' for USI - 'using unique index # 12, for NUSI - 'Using
index #34. If none appears then see if you can add indexes in your query to make it efficient)

- Look for terms like All Amp, Group AMp etc

- Look for terms indicating confidence levels. ( High confidence, Low confidence and No confidence which directly indicates the level to which it has prior knowledge on the data it is using.
Try collecting stats on columns where the confidence level is very low)

- Look if partition elimination happens for PPI Tables. If not present try to use the PPI column in you where clause.

- Look for BMSMS (Incase of several NUSI being used, try to use them with an AND operation instead of an OR)

....

Regards,

Annal T
Enthusiast

Re: Explain plan

Start with simple queries and try to understand their plans and then play with the query and see the impact on the explain plan. Gradually increase the complexity of the queries and analyse.

Regards,
Annal T

Re: Explain plan

Hi Annal,

Thank you very much !!

It really helps !!

Could you send any link (online documentation) or any PDF available for this.

Enthusiast

Re: Explain plan

Documents are available at www.Info.teradata.com.

Information on Explain is available in several documents.

You can start with "SQL Reference - Statement and Transaction Processing" PDf
Chapter Name : "Interpreting the Output of Explain Request Modifier"

Regards,
Annal T

Re: Explain plan

Thanks for the help !!