USING access-method clause

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

USING Access-Method Clause


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, BTS, 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 that are 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. An R-tree index cannot be stored in a dbspace with a non-default page size.An R-tree index can be stored in a dbspace with a non-default page size. For more information about R-tree indexes, see the R-Tree Index User's Guide.
The bts method is a registered secondary-access method.
Use the bts access method to perform basic text searching for words and phrases in a document repository that is stored in a column of a table. To perform basic text searches, you create an index using the bts access method on a text column and then use the bts_contains() search predicate function and other management functions. For more information about the bts access method, see bts access method syntax.

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');