CREATE JOIN INDEX statement

Description

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

Syntax

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

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

Create a join index between the department and employee tables using the dept_id column, which is the primary key for department and foreign key for employee.

CREATE JOIN INDEX emp_dept_join
FOR department FULL OUTER JOIN employee
ON department.dept_id = employee.dept_id

Usage

The CREATE JOIN INDEX statement 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 the DROP JOIN INDEX statement or to synchronize it using the SYNCHRONIZE JOIN INDEX statement. This statement only supports joins of type FULL OUTER; the OUTER keyword is optional.

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

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

To specify a multipart key, you need to include more than one predicate linking the two tables connected by a logical AND. A disjunct ON clause is not supported (i.e., IQ does not permit a logical OR of join predicates). Also, the ON clause does not accept a standard WHERE clause, so it is not possible to 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 will return an error. You can specify NATURAL or ON, but not both.

The CREATE JOIN INDEX statement 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 sub-tree on the left is the “one” while the sub-tree on the right is the “many”. If the opposite is true, CREATE JOIN INDEX will return 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 IQ base tables. They must not be temporary tables, remote tables, or proxy tables.

Note that 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. In order 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. IQ does not support star-join indexes that use multiple join key columns for any join.

The FLOAT_AS_DOUBLE option, which defaults to OFF, must be set ON for JDBC and client connections in order for CREATE JOIN INDEX statements to succeed.

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 the section “Inserting or deleting from tables in a join index” in Chapter 6, “Using Sybase IQ Indexes” in the Sybase IQ System Administration Guide.