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 Sybase IQ 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 Sybase IQ System Administration Guide and “Ad hoc joins vs. using join indexes” in “Joining Tables,”Sybase IQ 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 sample database” to see which columns are in each table in this example.
Some additional tasks are required for multiplex databases.
Please see Chapter
5, “Working with Database Objects,” in Sybase IQ System Administration Guide 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 sales_order
and sales_order_items
Select the Join Indexes container and click the
New Join Index toolbar button
or choose Alt+F,N,J to open the
Create Join Index Wizard.
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 Sybase IQ 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 sales_order for the left table.
Choose sales_order_items 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 sales_order
and sales_order_items.
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 department and employee, using equivalent values in columns from two tables. Follow these steps to create this joined index, which we will call dept_emp_jdx:
Select the Join Indexes container and click the
New Join Index toolbar button or choose Alt+F,N,J to open the
Create Join Index Wizard
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 department for the left table.
Choose employee for the right table.
Click Next.
In the Choose Index Columns dialog, select the desired Left Table column, dept_id and the Right Table column dept_id.
Click Add. After you add the two columns, they appear
in the Joined Columns box as DBA.department.dept_id=DBA.employee.dept_id. (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 department and
employee. Click Finish.
The dept_emp_jdx join index appears in the Join Indexes folder.
For detailed information about planning and creating join indexes, see Sybase IQ System Administration Guide.
