I have a series of joins that insert a large number of rows into a table. As you can imagine, I want this process to perform as well as it can, and in Netezza that means finding the best distribution and utilizing zone maps where possible.
To optimize these joins I would tweak the tables involved, run the process, and kill it if it took too long. I had an idea of how long it should take in an ideal scenario, so anything much longer than that I halted and adjusted.
But the more I tweaked, the slower it ran. It just became worse and worse. So finally I reverted back to the original structure… and it was slower than ever! What happened? (more…)
There are no indexes in Netezza. This is an intentional design choice. Indexes are effective in an OLTP system, where transactions affect a single row or two, but not in an OLAP system, where transactions affect large sets of data. Netezza is designed to be a fully OLAP-focused system, in both hardware and software, and indexes did not meet that goal. (more…)
Want to view deleted data? See which rows were added in a transaction? These extra fields will allow you to do so. (more…)
Inside Netezza are specialized processing units called SPUs, which have their own sets of disks, each containing a portion of your data. The SPUs work in parallel to return the answer you need. But if one of them has more data than the others, then we will be waiting on just that one to finish while the others are already done and sitting idle. The workload is unbalanced because the data is skewed. To conquer big data, we need to divide work evenly. (more…)
In any database, whether it’s SQL Server, Oracle, Sybase, DB2, Access (does Access count?), or anything else, the primary bottleneck is disk IO. Even if you have an SSD, the disk is the slowest part. In those databases we use indexes to alleviate this. Their query engine uses the index to find exactly where on the disk to start and stop reading, which keeps it from scanning entire tables to find every answer.
Netezza has an entirely different solution to the IO problem (in fact, it has no indexes). It cuts the cost of disk reads by doing the work in parallel. Simply put, it’s a brute force solution to the problem using specialized hardware, but that’s just the start. (more…)
ERROR: 256 : Code generation failure
In my experience this error means “I can’t figure out how to execute your query” or “The plan I created to execute your query failed.” (more…)
In case you ever need to, it is possible to groom a table without taking a backup first. (more…)
Spreading values across rows (or subsets of rows within a set of rows) was once cumbersome and difficult, but the invention of window functions (which have been around for a while now) have made it much easier. Here are some examples you can take and tweak to fit your needs. (more…)
The Unbearable Lightness of Data
Have you ever tried explaining your job to family or friends and seen their eyes glaze over? Will Thrash explains it better than we did. In the first 15 minutes or so of this video he talks about how he first got interested in data at a young age, and why it still holds his interest to this day. You can tell he gets why we do what we do, and why we all love it. (more…)
One of the first things a newcomer to Netezza learns is that it does not enforce primary key or unique constraints. (more…)