Tag Archives: netezza

How to “Undo” a Delete, Update, or Insert in Netezza

The great thing about Netezza is it does “soft” deletes until the groom process runs.  This means your deleted data is really still there.  This is of course very important if you just deleted something you didn’t mean too.  And … Continue reading

Posted in SQL Tricks | Tagged , , , | 13 Comments

Efficient Data Upload Method Via External Table

If you ever have tried to upload data to Netezza using convention query tool methods which create individual inserts, you will know this can be EXTREMELY SLOW.  The way to upload data efficiently is to create an on-the-fly external table … Continue reading

Posted in Performance Tuning for Netezza, SQL Tricks | Tagged , , , , , | 10 Comments

Returning and Saving Large Result Sets Locally

Some of the common SQL Query tools like Squirrel Sql can have issues when it tries to return large result sets.  This seems to be because it needs to hold them in memory.  There are times you need to just … Continue reading

Posted in SQL Tricks | Tagged , , , , , | 1 Comment

An Integer Check That Doesn’t Blow Up

Sometimes you load in data sourcing from charactor data into a numeric or integer field.  The challenge is any non-numeric data will blowup your entire SQL with something like an atoi (alpha to integer) error.  You really need to check … Continue reading

Posted in SQL Tricks | Tagged , , , , | Leave a comment

A Method for Handling Process Skew with Unknown Defaults

Back in the post Distributed Joins, Process Skew I mentioned a common cause of process skew were default values such as -1 for an “Unknown” customer creating skew when a redistribution happened on that customer key and there was a significant … Continue reading

Posted in Performance Tuning for Netezza | Tagged , , | Leave a comment

Distributed Joins, Modeling for Colocation

This post will build on my previous post Distributed Joins, The Basics.   So we will extend the example from a customer table and an orders table, to a customer table , orders table, and order_items table.  Logically the customer skey  … Continue reading

Posted in Best Practices, Performance Tuning for Netezza | Tagged , , , , | 3 Comments

Distributed Joins, The Basics

This post is really about some base understanding that will be needed to understand some later performance methods.  I find to understand database performance it is helpful to understand and think how things are done at the most basic level.  … Continue reading

Posted in Best Practices, Performance Tuning for Netezza | Tagged , , , , , , | Leave a comment

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 … Continue reading

Posted in SQL Tricks | Tagged , , | Leave a comment

Rownum In Netezza?

Those of you coming from the Oracle world may find you are missing a ROWNUM pseudo column in Netezza.  Sure there’s the LIMIT clause, but sometimes you have a need to assign a sequential number to each row.  The best solution … Continue reading

Posted in SQL Tricks | Tagged , , , | 2 Comments

Make Your Sequences 25 Times Faster

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 … Continue reading

Posted in Performance Tuning for Netezza | Tagged , , | 4 Comments