A join index is an internal structure that defines a relationship between the columns in two or more tables. Ad hoc joins are joins that do not use join indexes.
If you have defined join indexes on the join columns referenced in your query, Sybase IQ will usually use them to execute queries joining those tables. (For information about defining join indexes, see System Administration Guide: Volume 1 > Sybase IQ Indexes.)
Any join that does not use join indexes is known as an ad hoc join. If several tables are referenced by the query, and not all of them have join indexes defined, Sybase IQ will use the join indexes for those tables that have them in combination with an ad hoc join with the rest of the tables.
Because you cannot create join indexes for all possible joins, ad hoc joins may sometimes be necessary. Thanks to optimizations in Sybase IQ, you may find that queries perform as well or better without join indexes.
Keep these rules in mind when creating join indexes:
All join indexes are created using full outer joins. A query using a join index can be an inner, left outer, or right outer join though.
A full outer join is one where all rows from both the left and right specified tables are included in the result, with NULL returned for any column with no matching value in the corresponding column.
The only comparison operator that may be used in the join predicate ON clause is EQUALS.
You can use the NATURAL keyword instead of an ON clause, but you can only specify one pair of tables.
Join index columns must have identical data type, precision, and scale.
Join indexes tend to perform best compared to ad-hoc joins when the tables involved have similar numbers of rows. Join indexes perform less well compared to ad-hoc joins when there is a very large difference between the larger and smaller table.