Spool space error in collect statistics

Database

Spool space error in collect statistics

Hi All !!!

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.

Thank You
2 REPLIES
Enthusiast

Re: Spool space error in collect statistics

Try "USING SAMPLE" keyword.
This prevent fullscan.

Example:
COLLECT STATISTICS USING SAMPLE ON DB_1.TABLE_1
COLUMN (COL_B, COL_C, COL_D, COL_E);
Enthusiast

Re: Spool space error in collect statistics

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.