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.

This entry was posted in SQL Tricks and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>