Table 5-2. Operators which require B-tree
B-tree is also the default index created by the CREATE INDEX command.
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
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.