Select organizing keys

You can specify up to four organizing keys when you create or alter a CBT; however, it is rare that you would use four keys. Most CBTs typically use one, two, or three keys at most.

As a best practice, review the design and columns of your large fact tables and the types of queries that run against them. If you typically run queries on one dimension, such as date, you can load the data by date to take advantage of the zone maps. If you typically query a table by two dimensions, such as by storeId and customerID for example, CBTs can help to improve the query performance against that table.

The organizing keys must be columns that can be referenced in zone maps. By default, Netezza Performance Server creates zone maps for columns of the following data types:
  • Integer (1-byte, 2-byte, 4-byte, and 8-byte)
  • Date
  • Timestamp
In addition, Netezza Performance Server also creates zone maps for the following data types if columns of this type are used as the ORDER BY restriction for a materialized view or as the organizing key of a CBT:
  • Char, all sizes, but only the first 8 bytes are used in the zone map
  • Varchar, all sizes, but only the first 8 bytes are used in the zone map
  • Nchar, all sizes, but only the first 8 bytes are used in the zone map
  • Nvarchar, all sizes, but only the first 8 bytes are used in the zone map
  • Numeric, all sizes up to and including numeric(18)
  • Float
  • Double
  • Bool
  • Time
  • Time with timezone
  • Interval

You specify the organizing keys for a table when you create it (such as using the CREATE TABLE command), or when you alter it (such as using ALTER TABLE). When you define the organizing keys for a table, Netezza Performance Server does not automatically reorganize the records; you use the GROOM TABLE command to start the reorganization process.

You can add to, change, or drop the organizing keys for a table by using ALTER TABLE. The additional or changed keys take effect immediately, but you must groom the table to reorganize the records to the new keys. You cannot drop a column from a table if that column is specified as an organizing key for that table.