Secondary-Access Methods

A secondary-access method, often called an index, is a set of user-defined functions that build, access, and manipulate an index structure. These functions encapsulate index operations, such as how to scan, insert, delete, or update nodes in an index. A secondary-access method describes how to access the data in an index that is built on a column (column index) or on a user-defined function (functional index). Typically, a secondary-access method speeds up the retrieval of a type of data.

The database server provides definitions for the following secondary-access methods in the system catalog tables of each database:

• A generic B-tree
• An R-tree

DataBlade modules can provide additional secondary-access methods for use with UDTs. For more information about secondary-access methods of DataBlade modules, refer to the user guide for each DataBlade module. For more information about R-trees, refer to the IBM Informix: R-Tree Index User's Guide.

Generic B-Tree Index

In traditional relational database systems, the B-tree access method handles only built-in data types and therefore can compare only two keys of built-in data types. The B-tree index is useful for a query that retrieves a range of data values. To support UDTs, the database server provides an extended version of a B-tree, the generic B-tree index.

The database server uses the generic B-tree index as the built-in secondary-access method. This secondary-access method is registered in the sysams system catalog table with the name btree. When you use the CREATE INDEX statement (without the USING clause) to create an index, the database server creates a generic B-tree index. The following statement creates a B-tree index on the zipcode column of the customer table:

CREATE INDEX zip_ix ON customer (zipcode)

For more information, see the CREATE INDEX statement in the IBM Informix: Guide to SQL Syntax.

R-Tree Index

The database server can support the R-tree index for columns that contain spatial data such as maps and diagrams. An R-tree index is most beneficial when queries look for objects that are within other objects or for an object that contains one or more objects.

To use an R-tree index, install a spatial DataBlade module such as the Spatial DataBlade module, Geodetic DataBlade module, or any other third-party DataBlade module that implements an R-tree index.

Other User-Defined Secondary-Access Methods

A DataBlade module can provide a UDT to handle a particular type of data. The module might also provide a new secondary-access method (index) for the new data type that it defines. For example, the Excalibur Text DataBlade module provides an index to search text data. For more information, refer to the IBM Informix: Excalibur Text Search DataBlade Module User's Guide. For more information on the types of data and functions that each DataBlade module provides, refer to the user guide for the DataBlade module. The sysams system catalog table describes the secondary-access methods that exist in your database. For information about sysams, see the IBM Informix: Guide to SQL Reference.