June 10, 2014

Excluding NULL Key Columns

I've never been a fan of setting columns to null, because this introduces complexities into the application code. I'm certainly not the only one who feels this way, either. Check out this well-written screed that explains in detail why NULL values in a database are a programmer's nightmare.

Increasingly though, applications with database designs that incorporate NULL values -- developed by SQL Server, Oracle, Sybase and others -- are being brought to DB2 for z/OS. Of course DB2 for z/OS already supported these distributed databases, but DB2 11 provides new function that makes it easier to deal with NULL settings.

DB2 11 features the capability to exclude NULL key columns, which is designed to improve performance and reduce index sizes. When NULL key columns are excluded from an index, DB2 only adds an entry when the key column is not NULL. To specify that an index excludes NULL keys, create the index using the CREATE INDEX statement.

As you can imagine, if your data has a high percentage of NULL values in key columns, then the index will be much smaller and a scan will perform much more quickly since fewer entries will be in the index.

If you're currently supporting a database with NULL key columns and have had a chance to take advantage of the new DB2 11 feature to exclude NULL keys, please share your experiences in comments.