Does Teradata move all columns of a table to Spool

General
N/A

Does Teradata move all columns of a table to Spool

Hi

I was wondering if there are two tables and while joining two tables , if the table is getting redistributed into SPOOL.

Then does the teradata takes all columns in the table to SPOOL or just the columns eventually required in the query.

EG:

SEL TB1.COL1, TB1.COL2,TB1.COL3,TB1.COL4, TB1.COL5

FROM TABLE1 TB1 INNER JOIN TABLE2 TB2

ON TB1.COL1 = TB2.COL1;

Following is the DDL for TABLE1.

CREATE TABLE TABLE1(
COL1 INTEGER,
COL2 INTEGER,
COL3 INTEGER,
COL4 INTEGER,
COL5 INTEGER,
COL6 INTEGER,
COL7 INTEGER,
COL8 INTEGER,
COL9 INTEGER,
COL10 INTEGER
)
PRIMARY INDEX(COL5);

So now if the TABLE1 is redistributed into some SPOOL, then SPOOL will have all the columns of TABLE1(COL1...COL10) or just the columns used in INNER JOIN (COL1...COL5) ?

Any suggestion guys ?

Thanks in advance.

2 REPLIES

Re: Does Teradata move all columns of a table to Spool

Raj,

A very interesting question. As you know, Teradata got a fantastic optimizer. This optimizer do a lot of things to make sure the use of minimal system resources (CPU, Memmory, Spool etc) and provide best response time and throughput to user.

Teradata Optimizer minimizes the amount of spool required by:

  1. Projecting (Copying) only those columns which are required by the query
  2. Doing single table set selections first (for example Row selection using where condintion)
  3. put only the smaller table into spool whenever possible.

Hope it will answer your query :)

Khurram

Re: Does Teradata move all columns of a table to Spool

One more thing I forgot to add:

Teradata determines the smaller table by multiplying the number of qualifying rows with the number of bytes in column to be projected.

Smaller table size: Number of rows after set selection * Number of bytes in projecting columns

and also the SET SELECTION OPERATORS can be any condition.

Khurram