CREATE JOIN INDEX statement

Description

Creates a join index, which defines a group of tables that are prejoined through specific columns, to improve performance of queries using tables in a join operation.

Syntax

CREATE JOIN INDEX join-index-name FOR  join-clause
IN dbspace-name

Parameters

join-clause:: [ ( ] join-expression join-type join-expression [ ON search-condition ] [ ) ]

join-expression:: { table-name | join-clause }

join-type:: [ NATURAL ] FULL [ OUTER ] JOIN

search-condition:: [ ( ] search-expression [ AND search-expression ] [ ) ]

search-expression:: [ ( ] [ table-name.] column-name = [ table-name.] column-name [ ) ]

Examples

Example 1

Creates a join index between the Departments and Employees tables using the DepartmentID column, which is the primary key for Departments and foreign key for Employees.

CREATE JOIN INDEX emp_dept_join
FOR Departments FULL OUTER JOIN Employees
ON Departments.DepartmentID = Employees.DepartmentID

Example 2

Creates tables t1 and t2, where future data allocation is from the default dbspace, and join index t1t2, where future data allocation is from dbspace Dsp6.

CREATE TABLE t1(c1 int, c2 char(5));
CREATE TABLE t2(c1 int, c3 char(5));
CREATE JOIN INDEX t1t2 FOR t1
   FULL OUTER JOIN t2 ON t2.c1=t1.c1 IN Dsp6;

Usage

CREATE JOIN INDEX creates a join index on the specified columns of the named tables. Once a join index is created, it is never referenced again except to delete it using DROP JOIN INDEX or to synchronize it using SYNCHRONIZE JOIN INDEX. This statement supports joins only of type FULL OUTER; the OUTER keyword is optional.

IN Specifies the join index placement. If the IN clause is omitted, Sybase IQ creates the join index in the default dbspace (as specified by the option default_dbspace.)

ON References only columns from two tables. One set of columns must be from a single table in the left subtree and the other set of columns must be from a table in the right subtree. The only predicates supported are equijoin predicates. Sybase IQ does not allow single-variable predicates, intra-column comparisons, or nonequality joins.

Join index columns must have identical data type, precision, and scale.

To specify a multipart key, include more than one predicate linking the two tables connected by a logical AND. A disjunct ON clause is not supported; that is, Sybase IQ does not permit a logical OR of join predicates. Also, the ON clause does not accept a standard WHERE clause, so you cannot specify an alias.

You can use the NATURAL keyword instead of an ON clause. A NATURAL join is one that pairs columns up by name and implies an equijoin. If the NATURAL join generates predicates involving more than one pair of tables, CREATE JOIN INDEX returns an error. You can specify NATURAL or ON, but not both.

CREATE JOIN INDEX looks for a primary-key-to-foreign-key relationship in the tables to determine the direction of the one-to-many relationship. (The direction of a one-to-one relationship is not important.) The primary key is always the “one” and the foreign key is always the “many”. If such information is not defined, Sybase IQ assumes the subtree on the left is the “one” while the subtree on the right is the “many”. If the opposite is true, CREATE JOIN INDEX returns an error.

Query optimizations for all joins rely heavily on underlying primary keys. They do not require foreign keys. However, you can benefit from using foreign keys. Sybase IQ enforces foreign keys if you set up your loads to check for primary key-foreign key relationships.

Join index tables must be Sybase IQ base tables. They cannot be temporary tables, remote tables, or proxy tables.

Multicolumn indexes on base tables are not replicated in join indexes created using those base tables.

A star-join index is one in which a single table at the center of the star is joined to multiple tables in a one-to-many relationship. To define a star-join index, you must define single-column key and primary keys, and then use the key join syntax in the CREATE JOIN INDEX statement. Sybase IQ does not support star-join indexes that use multiple join key columns for any join.

You must explicitly grant permissions on the underlying “join virtual table” to other users in your group before they can manipulate tables in the join. For information on granting privileges on the join virtual table, see “Inserting or deleting from tables in a join index” in Chapter 6, “Using Sybase IQ Indexes” in the System Administration Guide: Volume 1.