Filter
Top Products
Using join indexes
152
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.