Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-03
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


The CREATE INDEX statement creates either a range index or a bitmap index on one or more columns of a table or materialized view and assigns a name to the new index.

Required privilege

No privilege is required for table or materialized view owner.

INDEX for another user's table or materialized view.

SQL syntax

[Owner.]TableName ({ColumnName [ASC | DESC]}
[, ... ] )


The CREATE INDEX statement has the parameters:

Parameter Description
UNIQUE Prohibits duplicates in the index. If UNIQUE is specified, each possible combination of index key column values can occur in only one row of the table. If UNIQUE is omitted, duplicate values are allowed. When you create a unique index, all existing rows must have unique values in the indexed columns. If you specify UNIQUE, TimesTen creates a range index.

A range index:

  • Speeds up range searches (but can also be used for efficient equality searches)

  • Is optimized for in-memory data management

  • Provides efficient sorting by column value

BITMAP Specify CREATE BITMAP INDEX to create an index where the information about rows with each unique value is encoded in a bitmap. Each bit in the bitmap corresponds to a row in the table.

Use a bitmap index for columns that do not have many unique values.

[Owner.] IndexName Name to be assigned to the new index. A table cannot have two indexes with the same name. If the owner is specified, it must be the same as the owner of the table.
[Owner.] TableName Designates the table or materialized view for which an index is to be created.
ColumnName Name of a column to be used as an index key. You can specify up to 16 columns in order from major index key to minor index key.
[ASC|DESC] Specifies the order of the index to be either ascending (the default) or descending. In TimesTen, this parameter is currently ignored.



Create a table and then create a bitmap index on the table. Use the ttIsql SHOWPLAN command to verify that the bitmap index is used in the query:

Command> CREATE TABLE tab1 (id NUMBER);
Command> INSERT INTO tab1 VALUES (10);
1 row inserted.
Command> INSERT INTO tab1 VALUES (20);
1 row inserted.
Command> CREATE BITMAP INDEX bitmap_id ON tab1 (id);
Command> COMMIT;
Command> SHOWPLAN 1;
Command> SELECT * FROM tab1 WHERE id = 10;
Query Optimizer Plan:
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkBitmapScan
  TBLNAME:             TAB1
  IXNAME:              BITMAP_ID
  NOT INDEXED:         <NULL>
< 10 >
1 row found.

The regions table in the HR schema creates a unique index on region_id. Issue the ttIsql INDEXES command on table regions. You see the unique range index regions.


Indexes on table SAMPLEUSER.REGIONS:
  REGIONS: unique range index on columns:
    (referenced by foreign key index COUNTR_REG_FK on table SAMPLEUSER.COUNTRIES)
  1 index found.

1 table found.

Attempt to create a unique index i on table regions indexing on column region_id. You see a warning message:

Command> CREATE UNIQUE INDEX i ON regions (region_id);
Warning  2232: New index I is identical to existing index REGIONS; consider dropping index I

Call ttRedundantIndexCheck to see warning message for this index:

Command> CALL ttRedundantIndexCheck ('regions');
< Index SAMPLEUSER.REGIONS.I is identical to index SAMPLEUSER.REGIONS.REGIONS; consider dropping index SAMPLEUSER.REGIONS.I >
1 row found.

Create table redundancy and define columns co11 and col2. Create two user indexes on col1 and col2. You see an error message when you attempt to create the second index r2. Index r1 is created. Index r2 is not created.

Command> CREATE TABLE redundancy (col1 CHAR (30), col2 VARCHAR2 (30));
Command> CREATE INDEX r1 ON redundancy (col1, col2);
Command> CREATE INDEX r2 ON redundancy (col1, col2);
 2231: New index R2 would be identical to existing index R1
The command failed.

Issue the ttIsql command INDEXES on table redundancy to show that only index r1 is created:

Command> INDEXES redundancy;

  R1: non-unique range index on columns:
  1 index found.

1 table found.

This unique index ensures that all part numbers are unique.

CREATE UNIQUE INDEX purchasing.partnumindex
ON (partnumber);

Create a linguistic index named german_index on table employees1. If you wish to have more than one linguistic sort, create a second linguistic index.

Command> CREATE TABLE employees1 (id CHARACTER (21),
id2 character (21));
Command> CREATE INDEX german_index ON employees1
Command> CREATE INDEX german_index2 ON employees1
  NLSSORT(id2, 'nls_sort=german_ci'));
Command> indexes employees1;
  GERMAN_INDEX: non-unique range index on columns:
  GERMAN_INDEX2: non-unique range index on columns:
  2 indexes found.
1 table found.

See also