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.
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(
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.
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:
Hope it will answer your query :)
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.