Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 174 / 536

Using join indexes
154
You can set the frequency of these messages with the
NOTIFY_MODULUS
option, and override the option value in either the
CREATE DATABASE
or
LOAD TABLE
command. For examples of these messages, see “Interpreting
notification messages” on page 187.
The join hierarchy in query resolution
Adaptive Server IQ can use the same join index to resolve a query that involves
the full join relationship specified in the join index, or a query that involves any
contiguous subset of that relationship; you do not have to create separate join
indexes for the subset relationships.
For example, assume that join index
ABCDEF
joins the tables illustrated in
Figure 4-1. Adaptive Server IQ can use join index
ABCDEF
to resolve any
queries that involve:
• The entire relationship
• Table
A
to Table
D
• Table
A
to Table
D
to Table
F
• Table
B
to Table
D
• Table
B
to Table
D
to Table
F
• Table
D
to Table
F
• Table
C
to Table
E
• Table
E
to Table
F
• Table
C
to Table
E
to Table
F
However, Adaptive Server IQ cannot use join index
ABCDEF
to resolve queries
against, for example, Table
E
to Table
D
.
One-to-many relationship
In a one-to-many join relationship, one row in one table potentially matches
with one or more rows in another table, and there is not more than one row in
the first table that matches with the same row(s) in the second table. For this to
be true, the values in the join column in the first table must be unique.
It is possible that either table has no match on the other table. This constitutes
an outer join and is fully supported. For more information, see the Introduction
to Adaptive Server IQ.