If you have defined join indexes on the join columns referenced in your query, Sybase IQ will automatically use them to make the query process faster. (For information about defining join indexes, see Chapter 6, “Using Sybase IQ Indexes,”in the Sybase IQ System Administration Guide.)
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:
Only full outer joins are supported in the index. The query can be an inner, left outer, or right outer join if indexed.
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.
