Selecting Secondary Indexes for Join Indexes
Join indexes are often more high-performing if they have 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 it can, and just because you define it to have a particular most likely use and access path, there is no reason to believe that the same join index might not also be useful for other, unplanned, queries. The Optimizer will join 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 it is defined is unique. This rule is enforced because of the way indexes on join index tables are handled internally.
See “CREATE JOIN INDEX” in SQL Data Definition Language for further information about creating join indexes and using secondary indexes with them.