Alternate Method For Sequences

So if you’ve used Netezza sequences you may have noticed they are all over the place, and far from sequential.  This is just the nature of a share nothing massively parallel architecture.  But if you want sequential sequences that automatically keep in sync with your table you can leverage the ability of Netezza to do table counts REALLY fast, and the row_number logic covered in ROWNUM alternative post.  Here is an example

SELECT

ROW_NUMBER() OVER ( ORDER BY A.CUSTOMER_SOME_NATURAL_KEY) + CUST_MAXSKEY.MAXSKEY  AS CUSTOMER_SKEY,

FROM

  (select

   …

   FROM CUSTOMER_SOURCE

   WHERE …) A

   CROSS JOIN

(SELECT NVL(MAX(CUSTOMER_SKEY),0) MAXSKEY FROM CUSTOMER ) CUST_MAXSKEY

The ordering clause is only needed to give you some control over what order the sequences are generated.  You just cross product to a SQL getting the max value, then used the row_number method and add that to that max value.  I’ve used this on tables with millions rows.  Larger tables should probably stick to the sequence object.  The great thing about this method is you don’t have to worry about ever getting your sequence out of sync with the table.

Leave a comment