Creating an Index

To create an index, issue the SQL statement CREATE INDEX.

TimesTen converts index names to upper case characters.

Every index has an owner. The owner is the user who created the underlying table. Indexes created by TimesTen itself, such as indexes on system tables, are created with the user name SYS or with the user name TTREP for indexes on replication tables.

Note:

You cannot create an index on LOB columns.

The following example creates an index ixid over column cust_id of table customer.

Command> CREATE INDEX ixid ON customer (cust_id);

The following creates a unique hash index on the customer table as part of the table creation:

Command> CREATE TABLE customer 
(cust_id NUMBER NOT NULL PRIMARY KEY, 
cust_name CHAR(100) NOT NULL, 
addr CHAR(100), 
zip NUMBER, 
region CHAR(10)) 
UNIQUE HASH ON (cust_id) PAGES = 30;

The following creates a non-unique hash index on the customer table over the customer name:

Command> CREATE HASH INDEX custname_idx ON customer(cust_name);

See CREATE INDEX and ALTER TABLE in the Oracle TimesTen In-Memory Database SQL Reference.