Join indexes are often more high-performing with one or more secondary indexes defined on them. The Optimizer adds a join index (even a partially covering join index) to its query plan whenever possible, and defining the join index to have a most likely use and access path is no reason the same join index may not also be useful for other, unplanned, queries. The Optimizer joins a base table that is unrelated to a join index with that join index if the query plan can be made more cost effective by doing so.
A secondary index on a join index cannot be defined as UNIQUE, even though the column set on which the secondary index is defined is unique. This rule is enforced because of the way indexes on join index tables are handled internally.
For further information about creating join indexes and using secondary indexes with them, see "selection" in CREATE JOIN INDEX Syntax Elements .