1. Introduction
  2. Query Optimization
  3. Indexes

Indexes

The query optimizer uses indexes to speed up the processing of a query. The optimizer uses existing indexes or creates temporary indexes to generate an execution plan.

An index is a map of keys to row locations in a table. Strategic use of indexes is essential to obtain maximum performance from a TimesTen system.

TimesTen uses these types of indexes:

TimesTen uses two types of indexes, each of which can be defined as unique or not unique (the default):

  • Hash index: Hash indexes are useful for finding rows with an exact match on one or more columns. You create a hash index with the CREATE HASH INDEX SQL statement. In general, hash indexes are faster than range indexes for exact match lookups and equi-joins. However, hash indexes cannot be used for lookups involving ranges or the prefix of a key and can require more space than range indexes.

  • Range index: Range indexes are useful for finding rows with column values within a range specified as an equality or inequality. Range indexes can be created over one or more columns of a table. You create a range index with the CREATE INDEX SQL statement.

See Understanding Indexes in Oracle TimesTen In-Memory Database Operations Guide. Also, see descriptions for CREATE INDEX in Oracle TimesTen In-Memory Database SQL Reference.

The Index Advisor tool can be used to recommend a set of indexes that could improve the performance of a specific SQL workload. See Using the Index Advisor to Recommend Indexes in the Oracle TimesTen In-Memory Database Operations Guide.