Using Join Indexes

If you know that certain tables in the same database will typically be joined in a consistent way, you may want to create a join index for those tables.

When you create a join index, Sybase IQ produces a new internal structure that relates table columns. It represents two or more tables, including the inner, left outer, and right outer rows.

• Join Indexes Improve Query Performance
  Join indexes usually provide better query performance than when table joins are first defined at query time (ad hoc joins). In many situations, however, you can gain optimal performance on joined columns without creating join indexes.
• Loading Considerations for Join Indexes
  Join indexes require more space and time to load than other IQ indexes. To load a join index, you must first load the underlying tables, and then load the join index.
• How Join Indexes are Used for Queries
  After you create a join index, its use is determined by the criteria of the SELECT statement.
• Relationships in Join Indexes
  Sybase IQ join indexes support one-to-many join relationships. A simple example of a one-to-many relationship is a sales representative to a customer. A sales representative can have more than one customer, but a customer has only one sales representative.
• When a Join Becomes Ad Hoc
  If there is no join index that handles all of the reference tables involved in a query, the query is resolved with an ad hoc join
• Join Hierarchy Overview
  All join relationships supported by Sybase IQ must have a hierarchy. Think of a join hierarchy as a tree that illustrates how all the tables in the join are connected.
• Columns in the Join Index
  Suppose that you join Tables A through E in a join index called ABCDE. If each table has two columns of data, expect the join index to have a total of fourteen columns.
• The Join Hierarchy in Query Resolution
  Sybase IQ can use the same join index to resolve a query that involves the full join relationship specified in the join index, or a query that involves any contiguous subset of that relationship. You do not have to create separate join indexes for the subset relationships.
• Multiple Table Joins and Performance
  Rules for multiple table joins are:
• Steps in Creating a Join Index
  In order to create a join index you must perform all of the following steps.
• Synchronizing Join Indexes
  The data in join index tables must be synchronized before you can use a join index. Synchronization ensures that the data is loaded in the correct order for the joins.
• Defining Join Relationships Between Tables
  When you create a join index, you must specify the relationship between each related pair in the join. A related pair is always two tables, however, you can also specify a relationship by relating a table to another join relationship.
• Issuing the CREATE JOIN INDEX Statement
  The following examples illustrate how to issue CREATE JOIN INDEX statements.
• Creating a Join Index in Sybase Central
  Follow these steps to create a join index in Sybase Central.
• Types of Join Hierarchies
  Sybase IQ supports two different types of join hierarchies.
• Restrictions on Modifying Join Index Tables
  Once you have created a join index, you are restricted in the types of changes you can make to the join index and its underlying tables and indexes.
• Insertions or Deletions from Join Index Tables
  You always insert or load into, or delete from, the underlying tables, not the join index itself.
• Table Versioning Controls Access to Join Indexes
  Any table is only available for write use to a single user at any given time.