So where I work we have the need to sequence a few hundred million rows a day. Surprisingly using a Netezza sequence object took about 2 hours on a 10200 to sequence 70 million rows. This was acceptable for our initial release, but in the future we have several hundred million more sequences to generate every day, so this was a big issue. Having an Oracle DBA background I realized the difference in performance sequence caching could make. But to my surprise it would not allow me to put a postgres style cache clause to the sequence create statement. For reference here is the syntax…
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
But then while investigating another sequence issue I came across the configuration parameter
So this can be simply used at the session level. In a sql session just run a set command and set this to 1 or true
Also to look at your current setting enter
And if you are using a sql tool that uses jdbc, the result returns like an error message. For example
Warning: NOTICE: ENABLE_SEQUENCE_CACHESIZE is off
So here’s the gold, once this is set to 1, you can create your sequence with a cache clause. You can also alter the sequence and add the cache clause. Once the sequence is created with a cache, you do not have to have ENABLE_SEQUENCE_CACHESIZE set to 1.
So what does this do for you? I set the cache to what I found to be a sweet spot of 50. I ran a test on our development 10050 and the sequencing of 34 million rows that had taken 6387 seconds, reduced to only 250 seconds. That’s over 25 times faster! This was such a big breakthrough for us, and it is not really documented anywhere I cound find. If you use sequences on high volumes this may be the quickest win performace tip you are going to find.