IQ Join Indexes

A join index is a special type of index used in Sybase AS IQ, which represents a full outer join of two or more tables. The query engine may use this full outer join as a starting point for queries that include left outer, right outer, and inner joins. A full outer join is one where all rows from both the left and right specified tables are included in the result, with NULL returned for any column with no matching value in the corresponding column.

Join indexes are defined from references. You can create a join index for any set of columns that your users commonly join to resolve queries.

While some references are based on keys, Sybase AS IQ allows you to create user-defined references to include the exact join required by your foreseen queries.

You can define a join index:

• Creating a Join Index from the Table Property Sheet
  You can create a join index or a bitmap join index from the Join Index tab of the table property sheet. The join index is automatically based on the current table.
• Creating a Join Index in the List of Join Indexes
  If you need to create more join indexes than those automatically created by the rebuild process, you can use the list of join indexes.
• Join Index Properties
  A join index has the following properties:
• Rebuilding Join Indexes for All References Linked to a Fact Table
  You can automatically generate a join index for each selected fact table and the dimension tables that it references. The rebuild process only applies to fact tables selected in the Selection tab of the Join Index Rebuild tab.
• Adding References to a Join Index
  You can add a reference to any join index. You do this, for example, when you create a new reference that you want to include in an existing join index.