I executed the following request and I have the error no more spool space for my user id. I can't have more spool space.

The challenging part is that vue_z has 1 trillion rows. The partition column is col6 and is date.It is partitioned by week.

When I put a filter on WHERE clause like : WHERE SUBSTR(t1.col10, 1, 3) = 'ABC' and cast(t2.col6 as date ) between '2024-12-01' and '2024-01-07'

What can I do to have all data on this table_test and avoid no more spool space error ?

Here is what the request looks like :

CREATE MULTISET VOLATILE TABLE table_test AS (
      SELECT col1,
             col2,
             COUNT(DISTINCT col3) AS count_col,
             AVG(col4) * count_col / 1000 AS computed_value
      FROM (
        SELECT col1, 
               col3,
               col4,
               COALESCE(col5, -1) AS transformed_col,
               col6 - EXTRACT(MINUTE FROM col6) * INTERVAL '1' MINUTE
                   + TRUNC(EXTRACT(MINUTE FROM col6)/30) * INTERVAL '30' MINUTE AS col2
        FROM database_a.schema_x.vue_y t1
        INNER JOIN database_b.schema_y.vue_z t2 
        ON t1.col3 = t2.col3 
        AND PERIOD(CAST(t2.date_start AS TIMESTAMP), CAST(t2.date_end AS TIMESTAMP)) CONTAINS t1.col6
        AND t2.col7 = 'VALUE1'
        AND t2.col8 = 'VALUE2'
        AND t2.col9 = 'VALUE3'
        AND t1.col4 IS NOT NULL
        
        WHERE SUBSTR(t1.col10, 1, 3) = 'ABC' and cast(t2.col6 as date ) between '2024-12-01' and '2024-12-31'
        
        QUALIFY 1 = ROW_NUMBER() 
        OVER (PARTITION BY t1.col3, t1.col6
              ORDER BY transformed_col DESC,
                       t2.col11 DESC,
                       t2.col12 DESC,
                       t2.col13 DESC,
                       t2.col14 DESC,
                       t1.col4 DESC)
      ) AS temp_table
      GROUP BY 1, 2
    ) WITH DATA ON COMMIT PRESERVE ROWS;