Secondary access methods

A secondary access method is an index that allows queries to be evaluated more efficiently.

When you create a table in SQL, you can choose to create a B-tree index on one or more columns in the table. The query processing engine can choose to use the index. For example, if there is an index on the population column of the cities table, the query processing engine has at least two choices for evaluating the following query:

SELECT name FROM cities WHERE population > 1000000;

The query processing engine can scan the cities table sequentially, examining each record in turn and comparing the population to one million, or it can use the B-tree index to quickly find only those records with populations of more than one million. When it chooses to use the B-tree index, the engine does not consider records with smaller populations and does not read them from the disk.

The B-tree index stores the key value (for example, the population) and a pointer to the record in the base table. The base table is the primary store, and the index is a secondary access method.

You can define many types of indexes. For example, most text search engines use a textual index to run searches quickly, while spatial data can be indexed in a number of ways, including grid files and R-trees.

You can allow the creation of other indexes on your data types. For example, a DataBlade® module that defines a new type that can be sorted can allow users to create B-tree indexes on that type. To do so, you create an operator class for the type. An operator class is a collection of routines that allows the type to be used in a given access method. For example, the operator class for B-trees includes the routines LessThan(), LessThanOrEqual(), Equal(), GreaterThanOrEqual(), and GreaterThan(). When you define those routines on a new data type, users can create B-tree indexes on the type.