Overview of Index Types

TimesTen provides two types of indexes to enable fast access to tables. You can create up to 500 range or hash indexes on a table.

  • Range Indexes: Range indexes are useful for finding rows with column values within a certain range. You can create range indexes over one or more columns of a table.

    Range indexes and equi-joins can be used for equality and range searches, such as greater than or equal to, less than or equal to, and so on. If you have a primary key on a field and want to see if FIELD > 10, then the primary key index does not expedite finding the answer, but a separate index will.

    Range indexes are optimized for in-memory data management and provide efficient sorting by column value.

  • Hash Indexes: Hash indexes are useful for equality searches. A hash index is created with either of the following:

    • You can create a hash index or a unique hash index on one or more columns of a table or materialized view with the CREATE INDEX statement.

    • You can create a unique hash index on the primary key of a table during table creation with the CREATE TABLE... UNIQUE HASH ON statement.

    Hash indexes are faster than range indexes for exact match lookups, but they require more space than range indexes. Hash indexes can only be used for exact value lookups. Hash indexes cannot be used if the SQL query returns a range of values. Also, hash indexes are not useful for sorting values that come from a table scan.

    TimesTen may create temporary hash and range indexes automatically during query processing to speed up query processing. Alternatively, you can perform lookups by RowID for fast access to data. See ROWID Data Type in the Oracle TimesTen In-Memory Database SQL Reference.

    See CREATE INDEX and CREATE TABLE sections of the Oracle TimesTen In-Memory Database SQL Reference for details on creating hash indexes. For details on how to size a hash table, see Size Hash Indexes Appropriately.