You can define a simple join index on a single table. A single-table join index is a database object created using the CREATE JOIN INDEX statement, but specifying only one table in its FROM clause. This permits you to hash some or all of the columns of a large replicated base table on a foreign key that hashes rows to the same AMP as another large table. In some situations, this is more high-performing than building a multitable join index on the same columns. In effect, you are redistributing an entire base table or a frequently accessed subset of base table columns using a join index when you do this. The main advantage comes from less under-the-covers update maintenance on the single-table form of the index.
Single-table join indexes are the only type of join index that can be defined with a unique primary index.
The term single-table join index might seem to be a contradiction because there are no joins in a single-table join index. However, the Optimizer can use single-table join indexes to facilitate joins. The single-table join index came about because an observant software architect had the insight that it was possible to use the join index mechanism with a single table to horizontally partition all or a subset of a very large base table as a join index on a different primary index than that used by the original base table to hash its rows to the same AMPs as another very large base table that with which it was frequently joined. In this respect, a single-table join index is essentially a hashed NUSI.
Because of the way the rows of a column-partitioned join index are distributed to the AMPs, this advantage does not generalize to them or by the FastLoad utility. However, column-partitioned join indexes are useful as an alternative method to partition a base table in an entirely different way when such a option provides an appropriate choice for the Optimizer to consider for some queries.
This application is analogous to how NUPIs are often used in database design to hash the base table rows of a minor entity to the same AMP as rows from another table they are likely to be joined with in a well known query workload (see Nonunique Primary Indexes), though you cannot explicitly specify a join to a join index in a DML request. Instead, the Optimizer must determine if joining base table rows with join index rows is less costly than other methods.