Using an Operator Class
An operator class is the set of functions that is associated with a secondary access method. For most situations, use the default operators that are defined for a secondary access method. However, when you want to order the data in a different sequence or provide index support for a user-defined data type, you must extend an operator class.
This section provides a brief introduction to a secondary access method and an operator class. For a more detailed discussion of these topics, see the INFORMIX-Universal Server Performance Guide. For information on how to extend an operator class, see "Extending an Existing Operator Class" and "Creating an Operator Class".
What Is a Secondary Access Method?
The 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 in particular ways.
Universal Server provides definitions for the following secondary access methods in the system catalog tables of each database:
DataBlade modules can provide additional secondary access methods for use with user-defined data types. For more information about secondary access methods of DataBlade modules, refer to the user guide for each DataBlade module.
The Generic B-Tree Index
Universal 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 can compare only two keys of built-in data types. To support user-defined data types, Universal Server provides an extended version of a B-tree, the generic B-tree access method.
Universal Server uses the generic B-tree 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, Universal Server creates a generic B-tree index. The CREATE INDEX statement in Figure 4-1 creates a B-tree index on the zipcode column of the customer table.
For more information, see the CREATE INDEX statement in the Informix Guide to SQL: Syntax. For more information on the structure of a B-tree index, and on estimating the size of an B-tree index, refer to the INFORMIX-Universal Server Performance Guide.
The R-Tree Index
Universal Server can support the R-tree index for columns that contain spatial data such as maps and diagrams.
Universal Server automatically defines the R-tree access method in the sysams system catalog table with the name rtree. Universal Server databases define the default rtree operator class in the system catalog tables but do not provide the functions to implement this secondary access method.
To create an R-tree index, include the USING clause of the CREATE INDEX statement. For example, if you have one of the spatial DataBlade modules installed, the CREATE INDEX statement in Figure 4-2 creates an R-tree index on the picture column of the photos table.
For more information, see the CREATE INDEX statement in the Informix Guide to SQL: Syntax. For more information on the structure of an R-tree index and for information on estimating the size of an R-tree index, refer to INFORMIX-Universal Server Performance Guide.
Other User-Defined Secondary Access Methods
A DataBlade module can provide a user-defined data type 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 provides an index to search text data. For more information, refer to the Excalibur Text DataBlade User Guide. For more information on the types of data and functions each DataBlade module provides, refer to the user guide for each DataBlade module. For more information on how to determine which secondary access methods exist in your database, see the INFORMIX-Universal Server Performance Guide.
What Is an Operator Class?
An operator class is a group of functions that allow the secondary access method to store and search for values of a particular data type. The query optimizer uses an operator class to determine if an index can process the query with the least cost. For more information on the query optimizer, see the INFORMIX-Universal Server Performance Guide.
The operator-class functions fall into the following categories:
Universal Server uses the strategy functions of a secondary access method to help the query optimizer determine whether a specific index is applicable to a specific operation on a data type. The strategy functions are the operators that can appear in the filter of an SQL statement.
Universal Server uses the support functions of a secondary access method to build and access the index. These functions are not called directly by end users. When an operator in the filter of a query matches one of the strategy functions, the secondary access method uses the support functions to traverse the index and obtain the results.
Each secondary access method has a default operator class that is associated with it. By default, the CREATE INDEX statement associates the default operator class with an index.
Universal Server stores information about operator classes in the sysopclasses system catalog table. The database server defines the following operator classes in the system catalog tables of every database:
The Generic B-Tree Operator Class
The built-in secondary access method, the generic B-tree, has a single operator class defined in the sysopclasses system catalog table. This operator class, called btree_ops, is the default operator class for the btree secondary access method.
Universal Server uses the btree_ops operator class to specify:
The CREATE INDEX statement in Figure 4-1 shows how to create a B-tree index whose column uses the btree_ops operator class. This CREATE INDEX statement does not need to specify the btree_ops operator class because btree_ops is the default operator class for the btree access method.
For more information on the btree secondary access method, see "The Generic B-Tree Index".
The B-Tree Strategy Functions
The btree_ops operator class defines the following strategy functions for the btree access method:
These strategy functions are all operator functions. That is, each function is associated with an operator symbol; in this case, with a relational-operator symbol. For more information on relational-operator functions, see page 2-6.
The B-Tree Support Function
The btree_ops operator class has one support function, a comparison function called compare(). The compare() function is a user-defined function that returns an integer value to indicate whether its first argument is equal to, less than, or greater than its second argument, as follows:
The B-tree secondary access method uses the compare() function to traverse the nodes of the generic B-tree index. To search for data values in a generic B-tree index, the secondary access method uses the compare() function to compare the key value in the query to the key value in an index node. The result of the comparison determines if the secondary access method needs to search the next-lower level of the index or if the key resides in the current node.
The generic B-tree access method also uses the compare() function to perform the following tasks for generic B-tree indexes:
Universal Server uses the compare() function to evaluate comparisons in the SELECT statement. To provide support for these comparisons for opaque data types, you must write the compare() function. For more information, see "Comparing Data".
The R-Tree Index Operator Class
The R-tree secondary access method has an operator class defined in the sysopclasses system catalog table. This operator class, called rtree_ops, is the default operator class for the rtree secondary access method. Universal Server databases define the default R-tree operator class in the system catalog tables but do not provide the operator-class functions to implement this operator class.
Universal Server uses the rtree_ops operator class to specify:
The CREATE INDEX statement in Figure 4-2 shows how to create an R-tree index whose column uses the rtree_ops operator class. This CREATE INDEX statement does not need to specify the rtree_ops operator class because rtree_ops is the default operator class for the rtree access method. This statement assumes that you have one of the spatial DataBlade modules installed.
For more information on the rtree secondary access method, see "The R-Tree Index".
The R-Tree Strategy Functions
The rtree_ops operator class defines the following strategy functions for the rtree secondary access method:
When the query optimizer examines a query that contains a column, it checks to see if this column has an R-tree index defined on it. If such an index exists and if the query contains one of the strategy functions that the rtree_ops operator class supports, the optimizer can choose an R-tree index to execute the query.
The R-Tree Support Functions
The rtree_ops operator class contains the following support functions:
The rtree secondary access method, if it is defined in the database, uses these support functions to build and access an R-tree index.
|
