  ## Message Posted: Wed, 03 Dec 2003 @ 14:15:34 GMT  < Last>>

 Subj: Re: Help on Explain From: Vivek Pandey

Hi Narayan,

 Can some explain what is happening in this explain!

 - What does two-AMP JOIN step mean - with those conditions?

It is a 2 AMP step as it is a USI access method. AMP 1 to access the USI subtable with USI = 1 specified. (This USI subtable will then point to the base table row.) AMP 2 to access the base table row then.

 - Why does the join appear again - "using a nested join"?

That says Teradata is using the 'nested join' internally to join the rows. This internal join could be nested,merge or a product join.

The condition for nested join is.

1.An equality value for a unique index on Table 1 (this will retrieve a single row).

2.A Join on a column of that single row to any index on Table 2

Condition 1 is satisfied by A.b=1 (A.b being the USI)
Condition 1 is satisfied by A.b=B.b (B.b being the USI and an index again)

 - What does "unique index # 4" - mean?

unique index # 4 means the USI t3.b is being used.

You can get all the indexes defined on the table by quering the dictionary view dbc.indices.

The IndexNumber column is often 4 for USI & 8 for a NUSI.

You have a USI created on t3 with the column b(int).

```explain
sel A.b,B.b from t3 A, t3 B where A.b=B.b and A.b=1;

Explanation
-------------------------------------------------------------
1) First, we do a two-AMP JOIN step from CHK.B by way of unique index
# 4 "CHK.B.b = 1", which is joined to CHK.A by way of unique index
# 4 "CHK.A.b = CHK.B.b" with an additional condition of ("CHK.A.b
= 1").  CHK.B and CHK.A are joined using a nested join, with a
join condition of ("CHK.A.b = CHK.B.b").  The result goes into
Spool 1 (one-amp), which is built locally on the AMPs.  The size
of Spool 1 is estimated with high confidence to be 1 row.  The
estimated time for this step is 0.05 seconds.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1.  The total estimated time is 0.05 seconds.
```

Thanks & Regards,

Vivek.  < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2003 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback 