CREATE INDEX

The CREATE INDEX statement creates an index on one or more columns of a table or a materialized view.

Required Privilege

  • If the owner, no privilege is required.
  • If not the owner, the CREATE ANY INDEX system privilege or the INDEX object privilege is required.

SQL Syntax

The syntax to create a range index follows:

CREATE [GLOBAL][UNIQUE] INDEX [Owner.]IndexName ON
[Owner.]TableName (ColumnName [ASC | DESC][,... ])
[INCLUDE (ColumnName[,…])]

The syntax to create a hash index follows:

CREATE [GLOBAL][UNIQUE] HASH INDEX [Owner.]IndexName ON
[Owner.]TableName (ColumnName [ASC | DESC][,... ] ) 
[INCLUDE (ColumnName [,…])]
[ PAGES = RowPages | CURRENT ]

Parameters

Parameter Description

UNIQUE

You can specify UNIQUE for both range and hash indexes. If you specify UNIQUE each possible combination of index key column values can occur in only one row of the table.

HASH

Specify HASH to create a hash index. Specify UNIQUE with HASH to create a unique hash index.

[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.

ON [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 32 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 clause is currently ignored.

PAGES = {RowPages | CURRENT}

The PAGES clause sizes the hash index to reflect the expected number of pages in the table. If you do not specify the PAGES clause when defining a hash index, the default is CURRENT.

If you specify CURRENT, the current number of rows in the table is used to calculate the page count value. If you specify RowPages, the number of pages is used. To determine the value for RowPages, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for RowPages (256000/256=1000).

The value for RowPages must be a positive constant and must be greater than 0.

Do not specify PAGES=CURRENT if there are no rows in your table. This is because when rows are added to the table, the hash index does not perform optimally.

General Description of Indexes

  • TimesTen creates a nonunique range index by default. Specify CREATE UNIQUE INDEX to create a unique range index.

  • To create a nonunique hash index, specify CREATE HASH INDEX. To create a unique hash index, specify CREATE UNIQUE HASH INDEX.

  • 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.

  • 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.

  • To change the size or type of a hash index, drop the hash index and create a new index.

  • A hash index is created with a fixed size that remains constant for the life of the table. To resize the hash index, drop and recreate the index. If the hash index has insufficient pages it results in hash collisions which slows down the index look-up. Hash key comparison is a fast operation, so a small number of hash collisions should not cause a performance problem for TimesTen.

    To ensure that your hash index is sized correctly, your application must indicate the expected size of your table with the value of the RowPages parameter of the SET PAGES clause. Compute this value by dividing the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for the value of RowPages (256000/256=1000).

  • The maximum number of columns that can be specified for an index is 32.

Using Indexes in Query Processing

Proper indexes can improve query performance. Some queries can benefit from the use of indexes and some queries do not benefit from the use of indexes. Additionally, the choice of indexes for your queries is important.

A range index is ideal for processing range searches and exact matches, especially if most of the values in the index columns are unique. For example, if a range index is defined on columns (C1,C2), the index can be used to process the following types of predicates. ConstantOrParam refers to a constant value or dynamic parameter and range refers to the operators >,<,>=, or <=:

  • C1 = ConstantOrParam AND C2 = ConstantOrParam

  • C1 = ConstantOrParam AND C2 range ConstantOrParam

  • C1 = ConstantOrParam

  • C1 range ConstantOrParam

A range index efficiently processes equality and range predicates and efficiently processes sort and group operations. Use range indexes on index columns with many unique values. The order of columns you specify in a range index is relevant. The order of expressions in the predicate of a query that uses the range index is not relevant. When your query is processed, only one range index is used for each scan of your table even if you have defined multiple range indexes on your table.

A hash index efficiently processes equality predicates. You must size your hash index correctly for optimal performance. Use the PAGES parameter to size your hash index. If you specify a PAGES value that is too small, a large number of hash collisions may result, leading to performance degradation for statements that access the hash index. The order of columns specified in the hash index is not relevant and the order of expressions in the predicate of the query that uses the hash index is not relevant. If either a hash index or a range index can be used to process a particular equality predicate, the hash index is chosen because a lookup in a hash index is faster than a scan of a range index.

You can influence the indexes used by the optimizer by setting statement level or transaction level optimizer hints. see Statement Level Optimizer Hints for information on statement level optimizer hints. For more information on transaction level optimizer hints, see ttOptSetFlag, ttOptSetOrder, or ttOptUseIndex in the Oracle TimesTen In-Memory Database Reference.

Examples

Create a table and then create a unique hash index on col2. Do not specify the PAGES clause. If PAGES is not specified, the current table page count is used for the size of the hash table. Use INDEXES to verify the index was created. Insert a row in the table, set SHOWPLAN to 1 and then verify the optimizer uses the hash index. 

Command> CREATE TABLE tab (col1 NUMBER PRIMARY KEY NOT NULL, col2 VARCHAR2 (30));
Command> CREATE UNIQUE HASH INDEX hash1 ON tab (col2);
Command> INDEXES;
 
Indexes on table TESTUSER.TAB:
  HASH1: unique hash index on columns:
    COL2
  TAB: unique range index on columns:
    COL1
  2 indexes found.
 
2 indexes found on 1 table.
Command> INSERT INTO tab VALUES (10, 'ABC');
Command> SHOWPLAN 1;
Command> SELECT * FROM tab where col2 = 'ABC';
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkHashScan
  TBLNAME:             TAB
  IXNAME:              HASH1
  INDEXED CONDITION:   TAB.COL2 = 'ABC'
  NOT INDEXED:         <NULL>
 
< 10, ABC >
1 row found.

Create a table and create a nonunique hash index on col1. Use PAGES = CURRENT to use the current table page count to size the hash index. Use INDEXES to verify the nonunique hash index is created. 

Command> CREATE TABLE tab2 (col1 NUMBER);
Command> CREATE HASH INDEX hash_index ON tab2 (col1) PAGES = CURRENT;
Command> INDEXES;
 
Indexes on table TESTUSER.TAB2:
  HASH_INDEX: non-unique hash index on columns:
    COL1
  1 index found.
 
1 index found on 1 table.

Create table and create unique hash index on col3. Use PAGES = 100 to specify a page count of 100 for the size of the hash table. Use INDEXES to verify the unique hash index is created. 

Command> CREATE TABLE tab3 (col1 NUMBER, col2 NUMBER, col3 TT_INTEGER);
Command> CREATE UNIQUE HASH INDEX unique_hash1 on tab3 (col3) PAGES = 100;
Command> INDEXES;
 
Indexes on table TESTUSER.TAB3:
  UNIQUE_HASH1: unique hash index on columns:
    COL3
  1 index found.
 
1 index found on 1 table.

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 index found on 1 table.

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 index found on 1 table.

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. 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.

See Also

DROP INDEX