Show TOC

Join Indexes (IQ/Oracle)Locate this document in the navigation structure

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, 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 Start of the navigation path Model Next navigation step Join Indexes End of the navigation path, and click the Add a Row tool.

  • Right-click the model or package in the Browser, and select Start of the navigation path New Next navigation step Join Index End of the navigation path
  • Automatically, for each fact table and the dimension table it references by selecting Start of the navigation path Tools Next navigation step Rebuild Objects Next navigation step Rebuild Join Indexes End of the navigation path (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 object.

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.

In this section:
    Automatically Creating Join Indexes Through Rebuilding
    You can automatically generate a join index for each selected fact table and the dimension tables that it references. Each rebuilt join index contains the references that link the fact table to all the dimension tables located on a single axis proceeding from the fact table.
    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.