Index (Key) Overview

An index or key is an access method containing the values of one or more table columns that can be used to quickly locate a row or rows in the table. An index is a "quick lookup" internal table for finding rows that the user needs to search for frequently. RDM supports several indexing methods.

  • ROWID Index
  • B-Tree Index
  • Hash Index
  • R-Tree Index

Remember, while indexes increase performance of data retrieval, there is a performance cost to maintain the indexes when the row is created or updated.

You do not directly use an index from SQL, but indexes are used by the RDM SQL optimizer in the selection of an access plan for retrieving data from the database. More indexes provide the optimizer with more alternatives and can greatly improve SELECT execution performance. Unfortunately, the cost associated with a large number of indexes is a large amount of required storage and a lower performance, incurred by insert, update and delete operations.

Therefore, your selection of table columns to include in an index requires careful consideration. In general, create an index on the columns through which the table's rows typically will be accessed or sorted. Do not create an index for every possible sort or query that may be of interest to a user. Create indexes on the columns you expect will be used most often in order to speed access to the rows or to order the result rows.

Index Definitions

PRIMARY KEY

For RDM, the PRIMARY KEY uniquely identifies the row in the table. The characteristics of this index type are:

  • The column data cannot be NULL;
  • The column data cannot be updated after the row is inserted into the table;
  • No duplicates are allowed;
  • R-Trees cannot be designated as a PRIMARY KEY.

UNIQUE KEY

The characteristics of this index type are:

  • No duplicates are allowed (NULL columns will not be evaluated to be duplicate columns);
  • Columns included in key specification can be updated.

KEY

The characteristics of this index type are:

  • Duplicates allowed.

Compound Key

The compound key references a grouping of two or more columns into a single index. This type of index is typically used for creating a sorted group on one column with a sorted subtype of a second column. A compound key is typically used only for reporting (retrieval) but could be unique or primary key.

Index Methods

ROWID Index

The ROWID column is an index that uniquely identifies the rows in the table. The ROWID is defined as a 64 bit unsigned integer (UINT64). Upon creation of the row, the ROWID  can be set or it will be automatically generated with a unique value.

If the ROWID column is used as the retrieval access method, the rows will be ordered in numerical order for this column.

B-Tree Index

The default indexing method is a B-tree which organizes the indexed column values so that they are stored in sorted order. This allows fast access to all the rows that match a specified range of values. It also provides the ability to retrieve the table rows in the column order defined by the key specification grammar which avoids the need to do a separate sort when a data is retrieved using this access method.

Hash Index

This index method is called hashing in which the location of a row a determined from performing a "hash" of the indexed column value. However, the values are not sorted and, hence, hash indexes are only used to find rows that match a specific value. For RDM, this index method is intended for situations where the data in the index column or columns is very large and there is no need for ordered data retrieval. The method increases performance since the amount of data stored in the index nodes can be reduced significantly. Some characteristics of an Hash index are:

  • The index is always a UNIQUE indexes;
  • The index cannot be used for sorted range access (no next/previous navigation).

R-Tree Index

RDM supports the optimized storing of multi-dimensional data through an implementation of the R-tree index. Some characteristics of an R-tree column are:

  • The column must be an ARRAY of integer or real values. For more in formation on ARRAY types, refer to the Arrays section.
  • The R-tree index must be on a single column (no compound R-tree index definitions).
  • The R-tree specifies a low and high value in each dimension, therefore the column ARRAY dimension must be a multiple of two.
  • RDM will support an R-tree with a maximum of four dimensions so the column array dimension must be either 2 (one dimension), 4 (two dimensions), 6 (three dimensions), or 8 (four dimensions).
  • The R-tree column cannot be a PRIMARY KEY for the table.

For more information, refer to the R-tree Index Overview section.