Generalized-Key Indexes (XPS)

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > CREATE INDEX >

Generalized-Key Indexes (XPS)

If you are using Extended Parallel Server, you can create generalized-key (GK) indexes. Keys in a conventional index consist of one or more columns of the STATIC table that is being indexed. A GK index stores information about the records in a STATIC table based on the results of a query.

GK indexes provide a form of pre-computed index capability that supports faster query processing, especially in data-warehousing environments. The optimizer can use the GK index to improve performance.

A GK index is defined on a table when that table is the one being indexed. A GK index depends on a table when the table appears in the FROM clause of the index. Before you create a GK index, keep the following issues in mind:

All tables used in a GK index must be STATIC tables. If you try to change the type of a table to a non-static type while a GK index depends on that table, the database server returns an error.
Any table involved in a GK index must be a STATIC type. UPDATE, DELETE, INSERT, and LOAD operations are not valid on such a table until you drop the dependent GK index and the table type changes.

Key-only index scans are not available with GK indexes.

SELECT Clause for Generalized-Key Index

In Extended Parallel Server, the options of the GK SELECT clause are a subset of the options of SELECT. The GK SELECT clause has this syntax:

GK SELECT Clause:
 
                              .-,-----------------------.
           .-ALL-----------.  V                  (2)    |
|--SELECT--+-DISTINCT------+----+-| Expression |------+-+------->
           |  (1)          |    '-+----------+--*-----'
           '--------UNIQUE-'      +-table.---+
                                  +-synonym.-+
                                  '-alias.---'
 
                      (3)
>--| GK FROM Clause |-------+--------------------------+--------|
                            |                     (4)  |
                            '-| GK WHERE Clause |------'

Notes:

Informix extension
See Expression
See FROM Clause for Generalized-Key Index
See WHERE Clause for Generalized-Key Index

Element	Description	Restrictions	Syntax
alias	Temporary name assigned to the table in the FROM clause	You cannot use an alias for the table on which the index is built	Identifier
synonym, table	Synonym or table from which to retrieve data	The synonym and the table to which it points must exist	Database Object Name

The following restrictions apply to expressions in the GK SELECT clause:

It cannot refer to any SPL routine.
It cannot include the USER, TODAY, CURRENT, DBINFO built-in functions, nor any function that refers to a point in time or interval.

FROM Clause for Generalized-Key Index

GK FROM Clause:
 
|--FROM--+-indexed_table-+-------------------------------------->
         '-synonym1------'
 
>--+----------------------------------------+-------------------|
   | .------------------------------------. |
   | V                                    | |
   '---,--+-table----+--+---------------+-+-'
          '-synonym2-'  '-+----+--alias-'
                          '-AS-'

Element	Description	Restrictions	Syntax
alias	Temporary name for a table	You cannot use an alias with indexed_table	Identifier
indexed_table, synonym1	Table on which the index is being built	The FROM clause must include the indexed table	Database Object Name
synonym2, table	Synonym or identifier of table from which to retrieve data	The synonym and the table to which it points must exist	Database Object Name

All tables that appear in the FROM clause must be local static tables. That is views, non-static tables, and remote tables are not valid in the FROM clause.

Tables that you specify in the FROM clause must be transitively joined on key to the indexed table. Table A is transitively joined on key to table B if A and B are joined with equal joins on the unique-key columns of A.

Suppose that tables A, B, and C each have col1 as a primary key. In the following example, B is joined on key to A and C is joined on key to B. C is transitively joined on key to A.

CREATE GK INDEX gki 
   (SELECT A.col1, A.col2 FROM A, B, C 
      WHERE A.col1 = B.col1 AND B.col1 = C.col1)

WHERE Clause for Generalized-Key Index

GK WHERE Clause:
 
          .-AND--------------------.
          V                 (1)    |
|--WHERE----+-| Condition |------+-+----------------------------|
            |          (2)       |
            '-| Join |-----------'

Notes:

See Condition
See Specifying a Join in the WHERE Clause

The WHERE clause for a GK index has the following restrictions:

It cannot include USER, TODAY, CURRENT, or DBINFO built-in functions, nor any function that references a time value or a time-interval value.
It cannot refer to any SPL routine.
It cannot include any subquery.
It cannot include any aggregate function.
It cannot include any IN, LIKE, or MATCHES expression.