CREATE INDEX

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

Required Privilege

For a global index (supported in TimesTen Scaleout) and for a local index (supported in TimesTen Scaleout and in TimesTen Classic:
  • If the owner, no privilege is required.
  • If not the owner, the CREATE ANY INDEX system privilege or the INDEX object privilege is required.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout. You can create a global or a local index.

SQL Syntax

The syntax to create a range index follows. Use the GLOBAL keyword, the optional INCLUDE clause, and the optional IndexDistributionClause clause to create a global index. A global index is supported only in TimesTen Scaleout: 

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

The syntax to create a hash index follows. Use the GLOBAL keyword to create a global index. The optional INCLUDE clause, and the optional IndexDistributionClause clause can only be used with a global index. A global index is supported only in TimesTen Scaleout: 

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

The syntax for IndexDistributionClause can only be used for a global index (supported in TimesTen Scaleout): 

IndexDistributionClause::=
DISTRIBUTE BY HASH [(ColumnName [,…])]

Parameters

Parameter Description

GLOBAL

The GLOBAL keyword is only supported in TimesTen Scaleout.

In TimesTen Scaleout:
  • Specify GLOBAL to create a global index. A global index maps all rows in the table to a hash distribution scheme. When you create a global index, TimesTen Scaleout creates a materialized view with a local index and a hash distribution scheme to the index key columns.
  • If you do not specify GLOBAL, a local index is created in each database element. The index in this database element maps to rows in the table in the database element.

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.

INCLUDE (ColumnName [,…])

The INCLUDE clause is only supported in TimesTen Scaleout and can only be used when defining a global index. Use the INCLUDE clause to add non-index columns to the index definition. These non-index columns can be used to satisfy some queries without accessing the base table. This is especially useful if such columns are accessed frequently in queries (both for equality and for range conditions).

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.

IndexDistributionClause :: = DISTRIBUTE BY HASH [( ColumnName [,…])]

You can specify the DISTRIBUTE BY HASH clause only if you are defining a global index. This clause is optional.

If you do not specify this clause, the column(s) defined in the global index definition form the distribution key.

If you do specify this clause, you can optionally specify the ColumnName clause:

  • If specified, you must then specify one or more columns for the distribution key. These columns must include one or more of the columns specified in the global index definition.

  • If not specified, the column(s) defined in the global index definition form the distribution key.

    Note:

    The distribution key of the global index cannot be the same as the distribution key of the table.

Indexes in TimesTen Scaleout

TimesTen Scaleout supports global and local indexes:
  • Global index: Maps all rows in the table to a hash distribution scheme. When you create a global index, TimesTen Scaleout creates a materialized view with a local index and a hash distribution scheme to the index key columns. The local index that is created on the materialized view further optimizes query performance.
  • Local index: Is created in each database element. The index in this database element maps to rows in the table in this database element. Queries on index columns that do not include the distribution key columns on the table require communication with an element in every replica set.

See Understanding Indexes in the Oracle TimesTen In-Memory Database Scaleout User's Guide for information on using indexes in TimesTen Scaleout.

Choosing a Global or a Local index in TimesTen Scaleout

Consider the following when deciding whether to use a global or a local index:

  • Use a global index for:

    • Unique columns: A global unique index optimizes query execution by performing unique constraint checks more efficiently. TimesTen Scaleout uses the distribution key columns for uniqueness verification instead of accessing all replica sets. However, if the distribution key is a subset of the index key, create a local index.
    • Queries that have an equality predicate that do not include all of the columns in the distribution key of the table.
    • A group of columns that are frequently joined in queries with primary key columns.
    • Non-index columns that are frequently used in queries: Define a global index with the INCLUDE clause to include those non-index columns. In such a case, the table does not need to be accessed to satisfy the query.
    • An index where the index key is a prefix of the distribution key of the table.
  • Use a local index for:
    • Non-unique columns: If the index key consists of only non-unique columns, create a local non-unique index.
    • An index key that has the same columns as the distribution key for the table.
    • The situation where the distribution key of the table is a prefix of the index key.
    • Queries that have an equality predicate that includes all columns in the distribution key of the table.

See Understanding Indexes in the Oracle TimesTen In-Memory Database Scaleout User's Guide for more information.

Description of Global Indexes

Global index usage:
  • You must specify the GLOBAL keyword to create a global index. An index is local by default.
  • Global indexes by default are distributed by hash and can only be distributed by hash. Local indexes are not distributed.
  • When you create a global index, TimesTen Scaleout internally creates its own materialized view and its own local index on that materialized view.
  • Global indexes result in more efficient query execution with joins. However DML operations are slower due to the maintenance of the internal materialized view (that is created when you define a global index).
  • When a new element is added in the grid, the schema is replicated on the new element. In addition, the rows are redistributed, and the indexes are rebuilt. This includes the global indexes. Similarly, when an element is removed from the grid, the rows are redistributed and the indexes are rebuilt.
Distribution scheme of table:
  • You can define a global index on a table distributed by hash and on a table distributed by reference. Global indexes on parent and child (first-level reference) tables are supported. However, you cannot define a global index on grandchild tables or any tables that are not first-level reference tables.
  • You cannot define a global index on a table distributed by duplicate.

Restrictions on global indexes:

  • The column list for the distribution key cannot contain the ROWID pseudocolumn or a column of type ROWID.
  • Not supported on a global temporary table.
  • Not supported on readonly cache groups.
  • Not supported on a materialized view.

Syntax and Semantic Rules for Global Indexes

You must specify the GLOBAL keyword to create a global index. If you do not specify the GLOBAL keyword, a local index is created. Global indexes are distributed by hash on index key columns.

If you specify the GLOBAL keyword, you can optionally specify these clauses that are specific to global indexes:
  • INCLUDE clause: Optional clause that enables you to include non-key columns in the index. If such columns are frequently accessed by queries that use the index, this may improve performance.

  • IndexDistributionClause: Optional clause that enables you to specify what columns to use for the hash distribution. If you do not specify this clause, then the index columns form the distribution key. The distribution key of the index cannot be the same as the distribution key of the table.

  • Examples:

    Global range index:

    Command> CREATE GLOBAL INDEX globalindex1 ON mytab (a) INCLUDE (b,c) DISTRIBUTE BY HASH (a);
Command> indexes mytab;

Indexes on table SAMPLEUSER.MYTAB:
  MYTAB: unique range index on columns:
    C
    B
  GLOBALINDEX1: global non-unique range index on columns:
    A
    Included columns:
      B
      C
  2 indexes found.

2 indexes found on 1 table.
Command> drop table mytab;

    Global hash index:

    Command> CREATE TABLE mytab (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, 
           a TT_INTEGER NOT NULL, PRIMARY KEY (c,b)) DISTRIBUTE BY HASH (a,b);
Command> CREATE GLOBAL HASH INDEX globalhashindex1 ON mytab(a) INCLUDE (b,c) PAGES=200 DISTRIBUTE BY HASH (a);
Command> indexes MYTAB;

Indexes on table SAMPLEUSER.MYTAB:
  MYTAB: unique range index on columns:
    C
    B
  GLOBALHASHINDEX1: global non-unique hash index on columns:
    A
    Included columns:
      B
      C
  2 indexes found.

2 indexes found on 1 table.

See Examples: TimesTen Scaleout for additional examples.

General Description of Indexes in TimesTen Scaleout

  • 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: TimesTen Scaleout

These examples illustrate the syntax requirements for creating a global index. You must specify the GLOBAL keyword to create a global index.

Illustrate Global Index Syntax

This example illustrates the supported syntax for a global index.

Create a table with three columns (c,b,a) and define a primary key on two of those columns (c,b). Distribute the table by hash on columns (a,b).

Create a global unique range index on columns (c,b). 
Command> CREATE TABLE mytab1 (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, a TT_INTEGER NOT NULL, 
PRIMARY KEY (c,b)) DISTRIBUTE BY HASH (a,b);
Command> CREATE GLOBAL UNIQUE INDEX mygix1 ON mytab1 (c,b);
Command> indexes mytab1;

Indexes on table SAMPLEUSER.MYTAB1:
  MYGIX1: global unique range index on columns:
    C
    B
  MYTAB1: unique range index on columns:
    C
    B
  2 indexes found.

2 indexes found on 1 table.
Create a second global range index and specify the INCLUDE clause.

Command> CREATE GLOBAL INDEX mygix2 ON MYTAB1(b) include (a);
Command> indexes mytab1

Indexes on table SAMPLEUSER.MYTAB1:
  MYGIX1: global unique range index on columns:
    C
    B
  MYTAB1: unique range index on columns:
    C
    B
  MYGIX2: global non-unique range index on columns:
    B
    Included columns:
      A
  3 indexes found.

3 indexes found on 1 table.
Drop the second index and recreate it. Distribute the index by hash on column b. 
Command> DROP INDEX mygix2;
Command> CREATE GLOBAL INDEX mygix2 ON MYTAB1(b) INCLUDE (a) DISTRIBUTE BY HASH(b);
Command> INDEXES mytab1

Indexes on table SAMPLEUSER.MYTAB1:
  MYGIX1: global unique range index on columns:
    C
    B
  MYTAB1: unique range index on columns:
    C
    B
  MYGIX2: global non-unique range index on columns:
    B
    Included columns:
      A
  3 indexes found.

3 indexes found on 1 table.

Create a global hash index.

Command> CREATE GLOBAL HASH INDEX mygix3 ON mytab1(a) PAGES =200;
Command> indexes mytab1;

Indexes on table SAMPLEUSER.MYTAB1:
  MYGIX1: global unique range index on columns:
    C
    B
  MYTAB1: unique range index on columns:
    C
    B
  MYGIX3: global non-unique hash index on columns:
    A
  MYGIX2: global non-unique range index on columns:
    B
    Included columns:
      A
  4 indexes found.

4 indexes found on 1 table.

Distribution Key of Global Index is Same as Distribution Key of Table

This example illustrates that you cannot create a global index whose distribution key is the same as the distribution key of the table. In this example, the mytab1 table is distributed by hash on columns (a,b). An attempt to create a global index, with columns (a,b) as the distribution key, results in an error. 

Command> CREATE TABLE mytab1 (a TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL,
           c TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> CREATE GLOBAL INDEX gix1 ON mytab1(a,b,c) DISTRIBUTE BY HASH (a,b);
 2253: Distribution key for global index cannot be same as that of the table or
 other global index. Consider creating a local index.
The command failed.

Global Index Creates Its Own Materialized View and its Own Local Index

This example illustrates that when you create a global index, TimesTen Scaleout creates its own internal materialized view and its own local index. Create the mytab2 table distributed by hash on columns (a,b). Create a global non-unique range index distributed by hash on columns (b,a). Run the ttIsql indexes command to show the gix2 global index is created as well as an internal local index on the internal materialized view. Then, run the ttIsql views command to show an internal materialized view is also created as a result of creating the global index. Run the ttIsql describe command to show the internal materialized view. Note that you cannot explicitly drop the internal materialized view or the internal local index. 

Command> CREATE TABLE mytab2 (a TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, 
           c TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> CREATE GLOBAL INDEX gix2 ON mytab2(a,b,c) DISTRIBUTE BY HASH (b,a);
Command> indexes;

Indexes on materialized view SAMPLEUSER.$GV9B55D3955D52:
  $GVI9B55D3955D52: non-unique range index on columns:
    A
    B
    C
  1 index found.

Indexes on table SAMPLEUSER.MYTAB2:
  GIX2: global non-unique range index on columns:
    A
    B
    C
  1 index found.

2 indexes found on 2 tables.
Command> views;
  SAMPLEUSER.$GV9B55D3955D52
1 view found.
Command> describe SAMPLEUSER.$GV9B55D3955D52;

Materialized view SAMPLEUSER.$GV9B55D3955D52:
  Global index: GIX2 (table: MYTAB2)
  Columns:
    A                               TT_INTEGER NOT NULL
    B                               TT_INTEGER NOT NULL
    C                               TT_INTEGER NOT NULL
  DISTRIBUTE BY HASH (B, A)

1 view found.

Examples: TimesTen Classic

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