As with built-in data types, you might improve the response time for a query when you define indexes for new data types. The response time for a query might improve when Dynamic Server uses an index for:
For more information on when the query performance can improve with an index on a built-in data type, see Improving Performance with Indexes.
Dynamic Server and DataBlade modules provide a variety of different types of indexes (also referred to as secondary-access methods). A secondary-access method is a set of database server 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.
To create an index on a user-defined data type, you can use any of the following secondary-access methods:
A B-tree index is good for a query that retrieves a range of data values. For more information, see B-Tree Secondary-Access Method .
An R-tree index is good for searches on multidimensional data. For more information, see the IBM Informix: R-Tree Index User's Guide.
A DataBlade module that supports a certain type of data can also provide a new index for that new data type. For more information, see Using an Index That a DataBlade Module Provides.
You can create a functional index on the resulting values of a user-defined function on one or more columns. For more information, see Using a Functional Index.
Once you choose the desired index type, you might also need to extend an operator class for the secondary-access method. For more information on how to extend operator classes, see the IBM Informix: User-Defined Routines and Data Types Developer's Guide.
Dynamic Server provides the generic B-tree index for columns in database tables. In traditional relational database systems, the B-tree access method handles only built-in data types and therefore it can only compare two keys of built-in data types. The generic B-tree index is an extended version of a B-tree that Dynamic Server provides to support user-defined data types.
Dynamic Server uses the generic B-tree as the built-in secondary-access method. This built-in 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. For more information, see the CREATE INDEX statement in the IBM Informix: Guide to SQL Syntax.
A B-tree index is good for a query that retrieves a range of data values. If the data to be indexed has a logical sequence to which the concepts of less than, greater than, and equal apply, the generic B-tree index is a useful way to index your data. Initially, the generic B-tree index supports the relational operators (<,<=,=,>=,>) on all built-in data types and orders the data in lexicographical sequence.
The optimizer considers whether to use the B-tree index to execute a query if you define a generic B-tree index on:
Initially, the generic B-tree can index data that is one of the built-in data types, and it orders the data in lexicographical sequence. However, you can extend a generic B-tree to support columns and functions on the following data types:
In this case, you need to extend the default operator class of the generic B-tree index.
In this case, you need to define a different operator class from the default generic B-tree index.
An operator class is the set of functions (operators) that are associated with a nontraditional B-tree index. For more details on operator classes, see Choosing Operator Classes for Indexes.
Dynamic Server provides a built-in B-tree secondary-access method. Your environment might have installed DataBlade modules that implement additional secondary-access methods. If additional access methods exist, they are defined in the sysams system catalog table.
To determine the secondary-access methods that are available for your database, query the sysams system catalog table with the following SELECT statement:
SELECT am_id, am_owner, am_name, am_type FROM sysams WHERE am_type = 'S';
An 'S' value in the am_type column identifies the access method as a secondary-access method. This query returns the following information:
In an ANSI-compliant database, the access-method name must be unique within the name space of the user. The access-method name always begins with the owner in the format am_owner.am_name.
By default, Dynamic Server provides the following definitions in the sysams system catalog table for two secondary-access methods, btree and rtree.
| Access Method | am_id Column | am_name Column | am_owner Column |
|---|---|---|---|
| Generic B-tree | 1 | btree | 'informix' |
| R-tree | 2 | rtree | 'informix' |
If you find additional rows in the sysams system catalog table (rows with am_id values greater than 2), the database supports additional user-defined access methods. Check the value in the am_type column to determine whether a user-defined access method is a primary- or secondary-access method.
For more information on the columns of the sysams system catalog table, see the IBM Informix: Guide to SQL Reference. For information on how to determine the operator classes that are available in your database, see Determining the Available Operator Classes.
The built-in secondary-access method is a B-tree index. If the concepts of less than, greater than, and equal do not apply to the data to be indexed, you probably want to consider a user-defined secondary-access method that works with Dynamic Server. You can use a user-defined secondary-access method to access other indexing structures, such as an R-tree index.
If your database supports a user-defined secondary-access method, you can specify that the database server uses this access method when it accesses a particular index. For information on how to determine the secondary-access methods that your database defines, see Determining the Available Access Methods.
To choose a user-defined secondary-access method, use the USING clause of the CREATE INDEX statement. The USING clause specifies the name of the secondary-access method to use for the index you create. This name must be listed in the am_name column of the sysams system catalog table and must be a secondary-access method (the am_type column of sysams is 'S').
The secondary-access method that you specify in the USING clause of CREATE INDEX must already be defined in the sysams system catalog. If the secondary-access method has not yet been defined, the CREATE INDEX statement fails.
When you omit the USING clause from the CREATE INDEX statement, the database server uses B-tree indexes as the secondary-access method. For more information, see the CREATE INDEX statement in the IBM Informix: Guide to SQL Syntax.
Dynamic Server supports the R-tree index for columns that contain spatial data such as maps and diagrams. An R-tree index uses a tree structure whose nodes store pointers to lower-level nodes. At the leaves of the R-tree are a collection of data pages that store n-dimensional shapes. For more information on the structure of an R-tree index and how to estimate the size of an R-tree index, see the IBM Informix: R-Tree Index User's Guide.
Dynamic Server provides support for indexes on the following database objects:
You can create a column index on the actual values in one or more columns.
You can create a functional index on the functional value of one or more columns.
To decide whether to use a column index or functional index, determine whether a column index is the right choice for the data that you want to index. An index on a column of some data types might not be useful for typical queries. For example, the following query asks how many images are dark:
SELECT COUNT(*) FROM photos WHERE darkness(picture) > 0.5
An index on the picture data itself does not improve the query performance. The concepts of less than, greater than, and equal are not particularly meaningful when applied to an image data type. Instead, a functional index that uses the darkness() function can improve performance. You might also have a user-defined function that executes frequently enough that performance improves when you create an index on its values.
When you create a functional index, the database server computes the values of the user-defined function and stores them as key values in the index. When a change in the table data causes a change in one of the values of an index key, the database server automatically updates the functional index.
You can use a functional index for functions that return values of both user-defined data types (opaque and distinct) and built-in data types. However, you cannot define a functional index if the function returns a simple-large-object data type (TEXT or BYTE).
The optimizer considers whether to use a functional index to access the results of functions that are in one of the following query clauses:
A functional index can be a B-tree index, an R-tree index, or a user-defined index type that a DataBlade module provides. For more information on the types of indexes, see Defining Indexes for User-Defined Data Types. For information on space requirements for functional indexes, see "Estimating Index Pages" on page 3-13.
The function can be built in or user defined. A user-defined function can be either an external function or an SPL function.
CREATE FUNCTION darkness(im image) RETURNS decimal EXTERNAL NAME '/lib/image.so' LANGUAGE C NOT VARIANT
In this example, you can use the default operator class for the functional index because the return value of the darkness() function is a built-in data type, DECIMAL.
CREATE TABLE photos
(
name char(20),
picture image
...
);
CREATE INDEX dark_ix ON photos (darkness(picture));
In this example, assume that the user-defined data type of image has already been defined in the database.
The optimizer can now consider the functional index when you specify the darkness() function as a filter in the query:
SELECT count(*) FROM photos WHERE darkness(picture) > 0.5
You can also create a composite index with user-defined functions. For more information, see Using Composite Indexes.