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.