Creating Join Indexes

Join indexes are Sybase IQ internal structures that optimize joins of related tables.

Join indexes are created on sets of columns rather than individual columns. A join index 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. You can create a join index for any set of columns that your users commonly join to resolve queries. For guidelines, see the System Administration Guide: Volume 1 and Performance and Tuning Guide > Joining Tables.

Three important rules about creating join indexes:

Create join indexes after indexing columns, because the join index will rely on the column indexes of related tables.
The creator of the join index must also be the owner of the tables used in the index, or the join index will fail.
Join index columns must have identical data type and scale.

Note: Some additional tasks are required for multiplex databases. Please see Using Sybase IQ Multiplex instead of this chapter if you need to create join indexes in a multiplex.

Creating a Join Index for SalesOrders and SalesOrderItems
This example shows you how to create join indexes. The first join index, so_soi_jdx, joins the sales_order and sales_order_items tables.
Creating a Join Index for Department and Employee
This example creates a join between Departments table and Employees table, using equivalent values in columns from the two tables. Follow these steps to create a joined index called dept_emp_jdx.
Updating Join Index Data
Synchronize your join indexes after loading the tables.

Parent topic: Indexing and Loading Data