Sybase 12.4.2 ユーザーズマニュアル

Using join indexes

 

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.

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.

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.

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.