Sybase 12.4.2 manual How join indexes are used for queries, Relationships in join indexes, 152

Using join indexes

How join indexes are used for queries

After you create a join index, its use is determined by the criteria of the SELECT statement. If a join index exists that joins the tables in the FROM clause by the relationship specified in the WHERE clause, or if a join index exists that is based on ANSI join syntax for natural or key joins, the join index is used to speed up queries. Otherwise, ad hoc joins between indexes on the individual tables are performed at query time. If there is a join index for a subset of tables in the SELECT, Adaptive Server IQ uses it to speed up the resulting ad hoc join.

Relationships in join indexes

Adaptive Server IQ join indexes support one-to-many join relationships. A simple example of a one-to-many relationship is a sales representative to a customer. A sales representative can have more than one customer, but a customer has only one sales representative.

There can be multiple levels of such relationships. However, you always specify join relationships between two tables, or between a table and a lower level join. The table that represents the “many” side of the relationship is called the top table. See “Join hierarchy overview” below for details.

When a join becomes ad hoc

If there is no join index that handles all of the reference tables involved in a query, the query is resolved with an ad hoc join. Because you cannot create a join index to represent a many-to-many join relationship, you can only issue ad hoc queries against such a relationship. Ad hoc queries provide flexibility at the expense of performance. If you have sufficient space for the join indexes, and you do not require many-to-many relationships, create join indexes whenever performance is critical.

Join hierarchy overview

All join relationships supported by Adaptive Server IQ must have a hierarchy. Think of a join hierarchy as a tree that illustrates how all the tables in the join are connected.

152