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

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

This example creates 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

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 a Sybase IQ multiplex, always perform CREATE JOIN INDEX in single-node mode on the write server, then synchronize query servers. CREATE JOIN INDEX returns an error instead of propagating from write server to query server.

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.

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

If a join column is a REAL data type, however, you must set FLOAT_AS_DOUBLE to OFF when creating join indexes, or an error occurs. Issues might also result from using inexact numerics for join columns.

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 Sybase IQ System Administration Guide.