In Teradata 12, collect statistics step fails with spool space error for the below mentioned table. Skewness of the table is under the acceptable range. PFB the table structure and collect statistics step:
CREATE SET TABLE DB_1.TABLE_1, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL ( COL_A DECIMAL(11,0) , COL_B CHAR(3) , COL_C SMALLINT , COL_D CHAR(21) , COL_E CHAR(3) , COL_F INTEGER , COL_G DATE , COL_H CHAR(2) , COL_I CHAR(4) , COL_J DECIMAL(5,0) , COL_K CHAR(19) DEFAULT NULL , COL_L INTEGER DEFAULT 0 ) PRIMARY INDEX (COL_A);
COLLECT STATISTICS ON DB_1.TABLE_1 INDEX (COL_A);
COLLECT STATISTICS ON DB_1.TABLE_1 COLUMN (COL_B, COL_C, COL_D, COL_E);
Collect Statistics on Index (COL_A) executed fine. However, the next collect statistics on column - COL_B, COL_C, COL_D, COL_E fails with spool space error.
Check for the data distribution for column combination of COL_B, COL_C, COL_D, COL_E ( using hash function). Also, since spool is used evenly by other ids under profile. make sure that query traffic from the profile holders is light on system. Using sample is a recommendable option too.