In addition to the column indexes, Sybase IQ allows you to define join indexes. Join indexes are Sybase IQ internal structures that optimize joins of related tables. In Performance and Tuning Guide you will learn more about join relationships between tables. In this chapter, tutorial instructions will supply join information for you to use in creating join indexes.
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 about creating join indexes, see the System Administration Guide: Volume 1 and Joining Tablesin Performance and Tuning Guide.
You should note 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.
The following tasks show how to create join indexes. Refer to “The demo database” to see which columns are in each table in this example.
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.
The first join index, so_soi_jdx, will join the sales_order and sales_order_items tables. To create it, follow these steps:
Creating a join index for SalesOrders and SalesOrderItems
In Folders view, right-click Join Indexes, point to New, choose Join Index.
In the Name and Table dialog, type so_soi_jdx for
the name.
In the Join Type dropdown list, select Natural, because you are joining equivalent columns with the same name (id) from two tables. For more details, see Performance and Tuning Guide.
When joining with the NATURAL keyword, take care that only the columns
to be joined have the same name. (For example id = id.)
Watch out for common column names like date and description.
Choose SalesOrders for the left table.
Choose SalesOrderItems for the right table.
Click Next. Sybase IQ locates the join columns.
In the Comment space, type the following to describe
the join index, Join index for SalesOrders and
SalesOrderItems.
Click Finish.
The so_soi_jdx join index appears in the Join Indexes folder.
Creating a join index for department and employee
Now create a join between Departments and Employees, using equivalent values in columns from two tables. Follow these steps to create this joined index, which we will call dept_emp_jdx:
In Folders view, right-click Join Indexes, point to New, choose Join Index.
Type the name of your new join index, dept_emp_jdx, in the Name box.
In the Join Type dropdown box, select ON. The ON clause is required in order to join equivalent columns with the same name (dept_id) from two tables.
Choose Departments for the left table.
Choose Employees for the right table.
Click Next.
In the Choose Index Columns dialog, select the desired Left Table column, DepartmentID and the Right Table column DepartmentID.
Click Add. After you add the two columns, they appear
in the Join Index Columns box as GROUP0.Departments.DepartmentID=GROUP0.employees.DepartmentID.
(You may have to scroll to read the entire join.)
Click Next.
On the Add Comment screen, type the following to describes
the join index, Join index for Departments and
Employees. Click Finish.
The dept_emp_jdx join index appears in the Join Indexes folder.
For detailed information about planning and creating join indexes, see System Administration Guide: Volume 1.
