Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 176 / 536

Using join indexes
156
2307,103,103,’Niedringhaus’
Warning! If the one-to-many relationship is incorrect, the join cannot be
synchronized until you remove the extra rows from the “one” table. If you try
to synchronize, you get a Duplicate Row error, and the transaction rolls back.
When you create a join index, you use ANSI FULL OUTER join syntax.
Adaptive Server IQ stores the index as a full outer join. Later, when you issue
queries against the columns in a join index, you can specify inner, left outer,
and right outer join relationships as well as full outer joins. Adaptive Server IQ
uses only the parts of the join index needed for a given query.
Multiple table joins and performance
Here are rules for multiple table joins:
• A table can be on the “one” side of a one-to-many relationship just once.
For example, you cannot have a join index or a join query where Table A
is joined to Table B in a one-to-many relationship, and Table A is joined
to Table C in a one-to-many relationship. You need to create separate join
indexes for each of these relationships.
• A table can appear in the relationship hierarchy only once. So, for
example, you cannot predefine a join relationship query where Table A is
joined to Table B, Table B is joined to Table C, and Table C is joined to
Table A. You can use predefined joins to query on the Table A to Table B
and the Table C to Table A relationships separately. To do so, create a
separate join index for each of these relationships.
• A table can be joined to another table, or to a join definition. For example,
you can create a join index that joins Table A to Table B, or a join index
that joins Table C to the join of Tables A and B.
• The top table in the hierarchy is the “many” side of a one-to-many
relationship with the rest of the hierarchy.
In some circumstances, you may want to create a separate join index for a
subset of the join relationship. If the top table in the subset of the join index has
a significantly smaller number of rows than the top table in the full join index,
a query on the subset may be faster than the same query on the full join index
if only tables in the subset are used in the query.