All Forums General
TD_Raj 50 posts Joined 05/10
03 Dec 2013
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.

M.Saeed Khurram 544 posts Joined 09/12
03 Dec 2013

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

M.Saeed Khurram 544 posts Joined 09/12
03 Dec 2013

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

You must sign in to leave a comment.