There are three main index types. Each type is used for a specific query because they rely upon different algorithm calculations. The three types of indices are:

B-tree Index

The B-tree index originated with Lehman-Yao's high-concurrency B-trees. This index is used to calculate comparison queries which involve the operators in the following chart.

Table 5-2. Operators which require B-tree

B-tree is also the default index created by the CREATE INDEX command.

R-tree Index

This type of index performs searches for special data. It uses Guttman's quadratic split algorithm to perform an R-tree search. Creating an R-tree index requires you to specify USING RTREE in the command. To create an R-tree index, use the following syntax:

Example 5-3. RTREE index

        CREATE INDEX name ON table USING RTREE (column); 
        

The table below shows the operators used in a query that would initialize the system to use this R-tree index:

Table 5-3. Operators which require R-tree

Hash Index

A hash index uses the implementaion of Litwin's linear hashing. The only operator that requires a hash index to perform a comparison is the equal to (=) operator. Similar to an R-tree index, a hash index requires that you specify hash in the command statement. Use the following to create a hash index:

Example 5-4. Creating a Hash Index

   CREATE INDEX name 
             ON table 
     USING HASH (column); 
        

Hash indices are rarely used. You may have noticed that B-tree indices are also used when the equal to (=) operator is specified in a query. Currently, there is no evidence to support that a hash index is faster than using a B-tree index.