The DISTRIBUTE ON clause is identical to a Teradata Primary Index definition. They both are used to determine which SPU (netezza) or AMP (teradata) holds the data. Neither are actually indexes in the traditional sense of the word.
Netezza does have any indexes. It does have zone maps, which help reduce the number of disk blocks read, but they are not indexes.
There are two things to consider when specifying distribution (RANDOM is the default distribution):
1. Skew - how even (numbers of rows) is the distribution across spus. Ideally, each spu should hold the same number of rows as other spus. Differences of 10% between the smallest and highest population are often considered worst case and should not be exceeded.
2. Co-located joins - if two tables are commonly joined and are large (depends on system, but usually 10GB or more) join query performance can be significantly improved by distributing both tables on the join columns so that a SPU broadcast is not necessary to join the two tables. ALL distribute on columns must be used in the join, and the join must be an equi-join for a co-located join to occur, so usually the fewer distribute on columns specified the better, provided it results in a reasonably smooth distribution. If the distribution choice causes a highly skewed distribution, it is often better off to leave the distribution random.
In the case of smaller tables, particularly in dimensional models, random distibution usually works fine. When implementing dimensional models (which work very well in Netezza, whereas they don't work well at all on Teradata) it is common to distribute the fact table randomly and the dimensions on their surrogate PK. When you have a very large dimension, you may sometimes distribute the facts on that dimension's FK (or one of the FK's if there are multiple role based references to that dimension). Again, it is important to be aware of data skew when doing so.
