Understanding Indexes

TimesTen Scaleout supports both local and global indexes.

Local index: TimesTen Scaleout creates the index on all elements of the database. The index in each element maps to rows in that element. Queries against index columns that do not also include all the distribution key columns of the table require communication with an element in every replica set.
Global index: A global index maps all rows in the database in 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 materialized view optimizes query execution by making predictable in which replica set any given value of the index key columns is located. The local index on the materialized view further optimizes query performance.

Note:

There is an execution cost overhead incurred for DML operations against the columns that are defined in a global index. Also, a global index has a storage cost overhead when compared to a local index. These are key tuning trade-offs in TimesTen Scaleout.

To increase query performance, consider using a global index instead a local index for:

Unique indexes. With a global unique index, TimesTen Scaleout can perform unique constraint checks more efficiently. See Create a Unique Index.

Note:

Create a local unique index instead if the distribution key is a subset of the index key. TimesTen Scaleout uses the distribution key columns for uniqueness verification instead of accessing all replica sets as it would do for any other local unique index case.
Columns that are frequently joined with primary key columns in queries. If one or more of the joined sets of columns are neither the primary key or distribution key, then creating global indexes on such sets of columns optimizes query performance by reducing the number of replica sets that need to be accessed. See Use Global Indexes to Optimize Query with Joins to Primary Key Columns.
Indexes that include non-index columns that are frequently accessed in queries. Global indexes enable you to add non-index columns in index structure with the INCLUDE clause. These non-index columns in the index can be used to satisfy some queries without needing to access the base table. See Use Global Indexes to Optimize Query with Joins to Primary Key Columns.
Indexes where the index key is a prefix of the distribution key of the table.

Note:

To reduce space usage and improve DML performance, it is recommended that you combine all global indexes with the same prefix in the index key into a single global index using the columns in the prefix as index key.

Likewise, consider using a local index instead of a global index for:

Indexes where the index key:
- Consists of only non-unique columns
- Is the same as the distribution key of the table
Indexes where the distribution key of the table is a prefix of the index key.

Global indexes are not supported on these cases:

Tables using a duplicate distribution scheme
Tables using a reference distribution scheme where the distribution key references a table using a reference distribution scheme.

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

Examples include:

Create a Unique Index

The following example illustrates how to create a unique index (local and global) on an existing table and shows the query optimizer plan for inserting values into the table.

Consider that you need to ensure that phone numbers inserted into the accounts table are unique and the table already uses the account_id column as primary key and hash distribution key.

Command> DESCRIBE accounts;

Table TERRY.ACCOUNTS:
  Columns:
   *ACCOUNT_ID                      NUMBER (10) NOT NULL
    PHONE                           VARCHAR2 (16) INLINE NOT NULL
    ACCOUNT_TYPE                    CHAR (1) NOT NULL
    STATUS                          NUMBER (2) NOT NULL
    CURRENT_BALANCE                 NUMBER (10,2) NOT NULL
    PREV_BALANCE                    NUMBER (10,2) NOT NULL
    DATE_CREATED                    DATE NOT NULL
    CUST_ID                         NUMBER (10) NOT NULL
  PRIMARY KEY (ACCOUNT_ID) RANGE INDEX
  DISTRIBUTE BY HASH (ACCOUNT_ID)

1 table found.
(primary key columns are indicated with *)

If you create a local unique index, TimesTen Scaleout would need to connect to every replica set of the database to verify the uniqueness of the values inserted or updated in the phone column, as shown next.

Command> CREATE UNIQUE INDEX phone_ix ON accounts(phone);
Command> INDEXES;

Indexes on table TERRY.ACCOUNTS:
  ACCOUNTS: unique range index on columns: 
    ACCOUNT_ID
  PHONE_IX: unique range index on columns: 
    PHONE
  2 indexes found.

2 indexes found on 1 table.
Command> EXPLAIN INSERT INTO accounts VALUES(?,?,?,?,?,?,?,?);

Query Optimizer Plan:

  STEP:                1
  LEVEL:               5
  OPERATION:           RowLkInsert
  TBLNAME:             ACCOUNTS
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       


  STEP:                2
  LEVEL:               4
  OPERATION:           GridRoute(Dist: DistHash, Kind: 1ProducerNConsumer)
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       


  STEP:                3
  LEVEL:               3
  OPERATION:           DMLScan
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       opNodeCnt=1, RowLkInsert(ACCOUNTS)


  STEP:                4
  LEVEL:               2
  OPERATION:           GridRoute(Dist: Duplicate, Kind: NProducerNConsumer)
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       


  STEP:                5
  LEVEL:               1
  OPERATION:           GlobalCheckConstraint
  TBLNAME:             ACCOUNTS
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:       UniqueKeyInsert(idx:PHONE_IX)

If you instead create a global unique index, TimesTen Scaleout would be able to verify the uniqueness of the values in the phone column more efficiently since the location of a row in the materialized view that the global index creates would be determined by the value in the phone column. The local index that the global index creates on the materialized view further ensures optimum query performance.

Use Global Indexes to Optimize Query with Joins to Primary Key Columns

The following example illustrates how to use global indexes to optimize queries to columns that are commonly joined in queries to primary key columns.

Consider that the customers table uses the cust_id column as both primary and distribution key, the accounts table uses the account_id column as both primary and distribution key, and the call_records table uses the call_id columns as both primary and distribution key.

Command> DESCRIBE customers;

Table TERRY.CUSTOMERS:
  Columns:
   *CUST_ID                         NUMBER (10) NOT NULL
    FIRST_NAME                      VARCHAR2 (30) INLINE NOT NULL
    LAST_NAME                       VARCHAR2 (30) INLINE NOT NULL
    ADDR1                           VARCHAR2 (64) INLINE
    ADDR2                           VARCHAR2 (64) INLINE
    ZIPCODE                         VARCHAR2 (5) INLINE
    ACCOUNT_ID                      NUMBER (10)
    MEMBER_SINCE                    DATE NOT NULL
  PRIMARY KEY (CUST_ID) RANGE INDEX
  DISTRIBUTE BY HASH (CUST_ID)

1 table found.
(primary key columns are indicated with *)
Command> DESCRIBE accounts;

Table TERRY.ACCOUNTS:
  Columns:
   *ACCOUNT_ID                      NUMBER (10) NOT NULL
    PHONE                           VARCHAR2 (16) INLINE NOT NULL
    ACCOUNT_TYPE                    CHAR (1) NOT NULL
    STATUS                          NUMBER (2) NOT NULL
    CURRENT_BALANCE                 NUMBER (10,2) NOT NULL
    PREV_BALANCE                    NUMBER (10,2) NOT NULL
    DATE_CREATED                    DATE NOT NULL
    CUST_ID                         NUMBER (10) NOT NULL
  PRIMARY KEY (ACCOUNT_ID) RANGE INDEX
  DISTRIBUTE BY HASH (ACCOUNT_ID)

1 table found.
(primary key columns are indicated with *)
Command> DESCRIBE call_records;

Table TERRY.CALL_RECORDS:
  Columns:
   *CALL_ID                         NUMBER (10) NOT NULL
    CALLER                          NUMBER (10) NOT NULL
    RECEIVER                        NUMBER (10) NOT NULL
    CALL_TIME                       TIMESTAMP (6) NOT NULL
    CODE                            NUMBER (38) NOT NULL
  PRIMARY KEY (CALL_ID) RANGE INDEX
  DISTRIBUTE BY HASH (CALL_ID)

1 table found.
(primary key columns are indicated with *)

Also, consider that you need to report on the accounts and customers that made a call with a specific code, as shown in the next query.

SELECT accounts.account_id, customers.cust_id, call_records.code
    FROM accounts, customers, call_records
    WHERE customers.cust_id = call_records.caller
        AND call_records.code = ?
        AND customers.account_id = accounts.account_id;

Given that the customers.cust_id and accounts.account_id columns are the primary keys of their respective tables, queries to those columns are already optimized. However, to optimize the join between the customers and call_records tables, the example creates the customer_calls_gix global index on the call_records.caller column and includes the call_records.code column to avoid having to further access the call_records table during the execution of the query.

CREATE GLOBAL INDEX customer_calls_gix 
    ON call_records(caller)
    INCLUDE (code)
    DISTRIBUTE BY HASH;

Furthermore, the example creates the customer_account_gix global index on the customers.account_id column to optimize the join between the customers and accounts tables.