Join Indexes (IQ/Oracle)

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

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 IQ allows you to create user-defined references to include the exact join required by your foreseen queries.

Creating a Join Index

You can create a join index in any of the following ways:

Open the property sheet of a table, click the Join Index tab, and click the Add a Row tool. The join index is created with the selected table specified as the base table.
Select Model > Join Indexes, and click the Add a Row tool.
Right-click the model or package in the Browser, and select New > Join Index
Automatically, for each fact table and the dimension table it references by selecting Tools > Rebuild Objects > Rebuild Join Indexes (see Automatically Creating Join Indexes Through Rebuilding).

Join Index Properties

You can modify an object's properties from its property sheet. To open a join index property sheet, double-click its Browser entry in the Join Indexes folder.

The General tab contains the following properties:

Property	Description
Name/Code/Comment	Identify the object. The name should clearly convey the object's purpose to non-technical users, while the code, which is used for generating code or scripts, may be abbreviated, and should not normally include spaces. You can optionally add a comment to provide more detailed information about the object. By default the code is generated from the name by applying the naming conventions specified in the model options. To decouple name-code synchronization, click to release the = button to the right of the Code field.
Stereotype	Extends the semantics of the object. You can enter a stereotype directly in this field, or add stereotypes to the list by specifying them in an extension file.
Owner	Specifies the user who is the owner of the join index (usually its creator). Use the tools to the right of the list to create, browse for, or view the properties of the currently selected user.
Comment	Descriptive label for the join index.
Base table	Specifies the name of the table or materialized view that stores the join index.
DBSpace	[IQ only] Specifies the DBSpace that will contain the join index.

The following tabs are also available:

Columns - Lists the columns used for the join index.
References - Lists the references used for the join index.