Error: SPU Swap Partition: Disk Temporary Work Space is full by query having row_number() over (partition by) clause : Netezza

Question

Db used: Netezza

Below query throw error as

Error: SPU Swap Partition: Disk Temporary Work Space is full

(SELECT A.*,ROW_NUMBER() OVER (PARTITION BY MDLX_CASE_NB ORDER BY PAID DESC) AS RNK_NEW
FROM ANLA1.FPA_RANK_OLD A)
DISTRIBUTE ON (CLAIM_NB);

From the plan file, I can see given table is having 1.7B records and I feel it is processing that much record in temp space due to which error is thrown.

If my understanding is correct then can we have any alternate of above query or any other solution?

Answer 1

I have two ideas:

1. the MDLX_CASE_NB has an uneven distribution. Test it by doing a ‘select MDLX_CASE_NB,count(*) numbof FROM ANLA1.FPA_RANK_OLD A group by 1 order by numbof Desc’. In that case please get back with the results, and we can discuss what to do
2. The columns behind A.* are very wide. In that case you can try to change the above query to use Rowid and the join it back:

create temp table x as SELECT Rowid as row_id,ROW_NUMBER() OVER (PARTITION BY MDLX_CASE_NB ORDER BY PAID DESC) AS RNK_NEW FROM ANLA1.FPA_RANK_OLD A DISTRIBUTE ON (random); Select A.*, RNK_NEW From ANLA1.FPA_RANK_OLD A Join x On x.row_id=a.rowid;