Netezza/PureData – Table Distribution Key Practices

For those of us new to Netezza and coming from other database systems, which uses an index, the optimization of the distribution key in Netezza can be counter initiative.  The distribution key is not about a grouping like an index, but rather spreading the data in a which let the zone map functionality improve performance.  So, here are a few effective practices to help decision making when deciding on a distribution key for a table:

  1. Distribute on one key only
  2. Distribute on the lowest/finest possible grain of the data to improve your zone map balance
  3. Avoid distributing foreign keys and/or foreign Surrogate key
  4. Avoid distributing on fields, which may need to be updated