USING Access-Method Clause (IDS)

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

USING Access-Method Clause (IDS)

The USING clause specifies the secondary-access method for the new index.

USING Access-Method Clause:
 
                             .-,-----------------.
                             V                   |
|--USING--sec_acc_method--(----parameter = value-+--)-----------|

Element	Description	Restrictions	Syntax
parameter	Secondary-access-method parameter for this index	See the user documentation for your user-defined access method	Quoted String
sec_acc _method	Secondary-access method for this index	Method can be a B-tree, R-tree, or user-defined access method, such as one that a DataBlade module defines	Identifier
value	Value of the specified parameter	Must be a valid literal value for parameter in this secondary-access method	Quoted String or Literal Number

A secondary-access method is a set of routines that perform all of the operations needed for an index, such as create, drop, insert, delete, update, and scan.

The database server provides the following secondary-access methods:

The generic B-tree index is the built-in secondary-access method.
A B-tree index is good for a query that retrieves a range of data values. The database server implements this secondary-access method and registers it as btree in the system catalog tables.
The R-tree method is a registered secondary-access method.
An R-tree index is good for searches on multidimensional data. The database server registers this secondary-access method as rtree in the system catalog tables of a database. An R-tree secondary-access method is not valid for a UNIQUE index key. An R-tree index cannot be clustered, and cannot be stored in a dbspace that has a non-default page size. For more information on R-tree indexes, see the IBM Informix R-Tree Index User's Guide.

The access method that you specify must be registered in the sysams system catalog table. The default secondary-access method is B-tree.

If the access method is B-tree, you can create only one index for each unique combination of ascending and descending columnar or functional keys with operator classes. (This restriction does not apply to other secondary-access methods.) By default, CREATE INDEX creates a generic B-tree index. If you want to create an index with a secondary-access method other than B-tree, you must specify the name of the secondary-access method in the USING clause.

Some user-defined access methods are packaged as DataBlade modules. Some DataBlade modules provide indexes that require specific parameters when you create them. For more information about user-defined access methods, refer to the documentation of your secondary access-method or DataBlade module.

The following example (for a database that implements R-tree indexes) creates an R-tree index on the location column that contains an opaque data type, point, and performs a query with a filter on the location column.

CREATE INDEX loc_ix ON TABLE emp (location) USING rtree;
SELECT name FROM emp WHERE location N_equator_equals point('500, 0');

The following CREATE INDEX statement creates an index that uses the fulltext secondary-access method, which takes two parameters: WORD_SUPPORT and PHRASE_SUPPORT. It indexes a table t, which has two columns: i, an integer column, and data, a TEXT column.

CREATE INDEX tx ON t(data)
   USING fulltext (WORD_SUPPORT='PATTERN',
   PHRASE_SUPPORT='MAXIMUM');