Understanding Indexes

Indexes are auxiliary data structures that greatly improve the performance of table searches.

You can use the Index Advisor to recommend indexes for a particular SQL workload. For more details, see Using the Index Advisor to Recommend Indexes.

Indexes are used automatically by the query optimizer to speed up the processing of a query. See The TimesTen Query Optimizer.

You can designate an index as unique, which means that each row in the table has a unique value for the indexed column or columns. Unique indexes can be created over nullable columns. In conformance with the SQL standard, multiple null values are permitted in a unique index, which enables a unique index to have multiple rows with the same set of values.

When sorting data values, TimesTen considers null values to be larger than all non-null values. See Null Values in the Oracle TimesTen In-Memory Database SQL Reference.

To perform any operation that creates, drops, or alters an index, the user must have the appropriate privileges, which are described along with the syntax for all SQL statements in SQL Statements in the Oracle TimesTen In-Memory Database SQL Reference.

The following sections describe how to manage your index:

• Overview of Index Types

• Creating an Index

• Altering an Index

• Dropping an Index

• Estimating the Size of an Index

• Using the Index Advisor to Recommend Indexes