The columns specified in the PARTITION BY clause of a window specification determine the partitions over which the ordered analytical function runs. For example, the following query specifies the StoreID column in the PARTITION BY clause to compute the group sales sum for each store:
SELECT StoreID, SMonth, ProdID, Sales, SUM(Sales) OVER (PARTITION BY StoreID) FROM sales_tbl;
At run time, Vantage moves rows that fall into a partition to the same AMP. If a large number of rows fall into the same partition, the AMP can run out of spool space.
To avoid this problem, examine the data in the columns of the PARTITION BY clause. If necessary, rewrite the query to include additional columns in the PARTITION BY clause to create smaller partitions that Vantage can distribute more evenly among the AMPs. For example, the preceding query can be rewritten to compute the group sales sum for each store for each month:
SELECT StoreID, SMonth, ProdID, Sales, SUM(Sales) OVER (PARTITION BY StoreID, SMonth) FROM sales_tbl;