CREATE INDEX

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

CREATE [UNIQUE|BITMAP] INDEX [Owner.]IndexName ON
[Owner.]TableName ({ColumnName [ASC | DESC]}
[, ... ] )

Parameters

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.

Description

If you do not specify UNIQUE or BITMAP, TimesTen creates a range index.
Specify a bitmap index on each column to increase the performance of complex queries that specify multiple predicates on multiple columns connected by the AND or OR operator. At runtime, TimesTen finds bitmaps of rows that satisfy each predicate and bitmaps from different predicates are combined using bitwise logical operation and then the resultant bitmaps are converted to qualified rows.
Bitmap indexes are used to satisfy these predicates:
- Equality predicates. For example: 'x1 = 1'
- Range predicates. For example: 'y1 > 10' and'z1 BETWEEN 1 and 10'
- AND predicates. For example: 'x1 > 10 AND y1 > 10'
- OR predicates. For example: 'x1 > 10 OR y1 > 10'
- Complex predicates with AND or OR. For example: '(x1 > 10 AND y1 > 10) OR (z1 > 10)'
- NOT EQUAL predicate with AND. For example: 'x1 = 1 and y1 != 1'
Bitmap indexes:
- COUNT (*) optimization counts rowids from bitmaps.
- Are used to optimize queries that group by a prefix of columns of the bitmap index.
- Are used to optimize distinct queries and order by queries.
- Are used in a MERGE join.
The CREATE INDEX statement enters the definition of the index in the system catalog and initializes the necessary data structures. Any rows in the table are then added to the index. In TimesTen, performance is the same regardless of whether the table is created, indexed and populated or created, then populated and indexed.
If UNIQUE is specified, all existing rows must have unique values in the indexed column(s).
The new index is maintained automatically until the index is deleted by a DROP INDEX statement or until the table associated with it is dropped.
Any prepared statements that reference the table with the new index are automatically prepared again the next time they are executed. Then the statements can take advantage, if possible, of the new index.
NULL compares higher than all other values for sorting.
An index on a temporary table cannot be created by a connection if any other connection has a non-empty instance of the table.
If you are using linguistic comparisons, you can create a linguistic index. A linguistic index uses sort key values and storage is required for these values. Only one unique value for NLS_SORT is allowed for an index. For more information on linguistic indexes and linguistic comparisons, see "Using linguistic indexes" in Oracle TimesTen In-Memory Database Operations Guide.
If you create indexes that are redundant, TimesTen generates warnings or errors. Call ttRedundantIndexCheck to see the list of redundant indexes for your tables.

Examples

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> SET AUTOCOMMIT OFF;
Command> SHOWPLAN 1;
Command> SELECT * FROM tab1 WHERE id = 10;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkBitmapScan
  TBLNAME:             TAB1
  IXNAME:              BITMAP_ID
  INDEXED CONDITION:   TAB1.ID = 10
  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.

Command> INDEXES REGIONS;

Indexes on table SAMPLEUSER.REGIONS:
  REGIONS: unique range index on columns:
    REGION_ID
    (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;

Indexes on table SAMPLEUSER.REDUNDANCY:
  R1: non-unique range index on columns:
    COL1
    COL2
  1 index found.

1 table found.

This unique index ensures that all part numbers are unique.

CREATE UNIQUE INDEX purchasing.partnumindex
ON purchasing.parts (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
  (NLSSORT(id, 'NLS_SORT=GERMAN'));
Command> CREATE INDEX german_index2 ON employees1
  NLSSORT(id2, 'nls_sort=german_ci'));
Command> indexes employees1;
Indexes on table SAMPLEUSER.EMPLOYEES1:
  GERMAN_INDEX: non-unique range index on columns:
    NLSSORT(ID,'NLS_SORT=GERMAN')
  GERMAN_INDEX2: non-unique range index on columns:
    NLSSORT(ID2,'nls_sort=german_ci')
  2 indexes found.
1 table found.