Understanding Indexes

Indexes are auxiliary data structures that greatly improve the performance of table searches.

You can use the Index Advisor to recommend indexes for a particular SQL workload. For more details, see Using the Index Advisor to Recommend Indexes.

Indexes are used automatically by the query optimizer to speed up the processing of a query. See The TimesTen Query Optimizer.

You can designate an index as unique, which means that each row in the table has a unique value for the indexed column or columns. Unique indexes can be created over nullable columns. In conformance with the SQL standard, multiple null values are permitted in a unique index, which enables a unique index to have multiple rows with the same set of values.

TimesTen Scaleout supports both local and global indexes. When you create a local index, then an index is created in each element that maps to rows in that element. A global index maps all rows in the database in a hash distribution scheme by creating a materialized view with a local index and a hash distribution scheme to the index key columns. See Understanding Indexes in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

When sorting data values, TimesTen considers null values to be larger than all non-null values. See Null Values in the Oracle TimesTen In-Memory Database SQL Reference.

To perform any operation that creates, drops, or alters an index, the user must have the appropriate privileges, which are described along with the syntax for all SQL statements in SQL Statements in the Oracle TimesTen In-Memory Database SQL Reference.

The following sections describe how to manage your index:

Overview of Index Types

TimesTen provides two types of indexes to enable fast access to tables. You can create up to 500 range or hash indexes on a table.

Range Indexes: Range indexes are useful for finding rows with column values within a certain range. You can create range indexes over one or more columns of a table.

Range indexes and equi-joins can be used for equality and range searches, such as greater than or equal to, less than or equal to, and so on. If you have a primary key on a field and want to see if FIELD > 10, then the primary key index does not expedite finding the answer, but a separate index will.

Range indexes are optimized for in-memory data management and provide efficient sorting by column value.
Hash Indexes: Hash indexes are useful for equality searches. A hash index is created with either of the following:
- You can create a hash index or a unique hash index on one or more columns of a table or materialized view with the CREATE INDEX statement.
- You can create a unique hash index on the primary key of a table during table creation with the CREATE TABLE... UNIQUE HASH ON statement.
Hash indexes are faster than range indexes for exact match lookups, but they require more space than range indexes. Hash indexes can only be used for exact value lookups. Hash indexes cannot be used if the SQL query returns a range of values. Also, hash indexes are not useful for sorting values that come from a table scan.

TimesTen may create temporary hash and range indexes automatically during query processing to speed up query processing. Alternatively, you can perform lookups by RowID for fast access to data. See ROWID Data Type in the Oracle TimesTen In-Memory Database SQL Reference.

See CREATE INDEX and CREATE TABLE sections of the Oracle TimesTen In-Memory Database SQL Reference for details on creating hash indexes. For details on how to size a hash table, see Size Hash Indexes Appropriately.

Creating an Index

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

TimesTen converts index names to upper case characters.

When you create an index on TimesTen Scaleout, it creates the index on all elements that are in the distribution map. TimesTen Scaleout populates each element's index with the rows that are stored on that element.

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.

Altering an Index

You can use the ALTER TABLE statement to add (or change) a primary key constraint to use either a range or hash index.

You cannot alter an index to be transformed from a hash to a range index or from a range to a hash index if it was created with the CREATE INDEX statement.

You can change a primary key constraint to use a range index instead of a hash index with the USE RANGE INDEX clause of the ALTER TABLE statement; you can change a primary key constraint to use a hash index instead of a range index with the USE HASH INDEX of the ALTER TABLE statement. See ALTER TABLE in the Oracle TimesTen In-Memory Database SQL Reference.

Dropping an Index

To drop a TimesTen index, issue the DROP INDEX SQL statement.

To uniquely refer to an index, an application must specify both its owner and name. If the application does not specify an owner, TimesTen looks for the index first under the user name of the caller, then under the user name SYS.

The following example drops the index named ixid.

Command> DROP INDEX ixid;

All indexes in a table are dropped automatically when the table is dropped. When you drop an index on TimesTen Scaleout, it drops the index on all elements.

Estimating the Size of an Index

Increasing the size of a TimesTen database can be done on first connect. Use the ttSize utility to estimate database size, including any indexes.

Correctly estimating the size of a database helps you to avoid having to resize the database in the future.

The following example shows that the ttSize utility estimates the rows, inline row bytes, size of any indexes on the table, and the total size of the table:

% ttSize -tbl pat.tab1 mydb

Rows = 2

Total in-line row bytes = 17524
Indexes:

 Index PAT adds 6282 bytes
  Total index bytes = 6282

Total = 23806

Using the Index Advisor to Recommend Indexes

The right set of indexes can make a difference in query performance. Use the Index Advisor to recommend indexes for improving the performance of a specific SQL workload.

The Index Advisor is intended for read-intensive complex queries. The use of the Index Advisor is not recommended for a write-intensive workload.

The Index Advisor evaluates a SQL workload and recommends indexes that can improve the performance for the following: joins, single table scans, and ORDER BY or GROUP BY operations. The Index Advisor does not differentiate tables that are used for specific intentions, such as the base table for a materialized view or as a table within a cache group. As long as the table is used in queries in the SQL workload, the Index Advisor may recommend indexes on that table.

The Index Advisor generates the CREATE statement for each recommended index, which you can choose to issue. A database administrator should review each CREATE statement recommended for new indexes before they are applied since the Index Advisor may recommend the following:

Indexes that are duplicates of existing indexes.
Indexes for tables or columns of tables that are created and dropped during a SQL workload. However, you could add the CREATE statement for the recommended index in the SQL workload after the DDL that creates the tables or columns of tables and before they are dropped.
Indexes that cannot be created, such as a unique index for a data set where the data is not unique. In this case, you should ignore this recommendation.
Index creation options where you can create an index as either a UNIQUE or non-unique index. The Index Advisor suggests both index types. You can only create one of the indexes as both suggested indexes have the same index name. While the optimizer thinks that the UNIQUE index is better for the specified workload, you can choose to create the non-unique index. Consider creating the UNIQUE index if the column only contains unique values. Consider creating the non-unique index if the column contains non-unique value.

The Index Advisor does not cover the following:

It does not optimize for memory use.
It does not consider maintenance costs.
It does not recommend that existing indexes be dropped if they are not useful.
It does not recommend indexes for global temporary tables.

The recommended steps to use the Index Advisor are as follows:

Prepare for Running the Index Advisor

Before you run the Index Advisor, you can optionally set any relevant optimizer hints and update statistics for the tables included in the SQL workload and force statements to be re-prepared during the capture.

Since the Index Advisor relies on the query plan, set any relevant optimizer hints that you would use for the SQL workload before enabling the Index Advisor and running the workload. See Use Optimizer Hints to Modify the Execution Plan.
Update statistics for tables included in the SQL workload and force statements to be re-prepared during the capture. This provides the most up-to-date statistics for the data collection and causes the statements to be re-prepared based on the latest statistics.

Update statistics for tables included in the SQL workload with one of the following built-in procedures: ttOptUpdateStats, ttOptEstimateStats, or ttOptSetTblStats. In the built-in procedures, set the invalidate parameter to 1 to invalidate all commands that reference the indicated tables and force these commands to be automatically prepared again when re-rund. This ensures that statistics are up to date.

The ttOptUpdateStats built-in procedure provides a full update of all statistics for the tables. However, it can be time consuming.
The ttOptEstimateStats evaluates statistics based upon a small percentage of rows in the indicated tables.
The ttOptSetTblStats sets the statistics to known values provided by you.

Note:

See ttOptUpdateStats, ttOptEstimateStats, and ttOptSetTblStats in the Oracle TimesTen In-Memory Database Reference.

The following example estimates statistics for all tables for the current user by evaluating a random sample of ten percent of the rows in these tables. It also invalidates all commands already prepared that reference these tables.

Command> call ttOptEstimateStats ( '', 1, '10 PERCENT' );

Capture the Data Used for Generating Index Recommendations

Call the ttIndexAdviceCaptureStart and ttIndexAdviceCaptureEnd built-in procedures to capture the information needed by the Index Advisor to generate index recommendations.

Call the ttIndexAdviceCaptureStart built-in procedure to start the process to collect index information.
Run the SQL workload.
Call the ttIndexAdviceCaptureEnd built-in procedure to end the index information collection process.

Note:

After the data collection process ends, you can retrieve the index recommendations as described in Retrieve Index Recommendations and Data Collection Information.

When you call the ttIndexAdviceCaptureStart built-in procedure to initiate the data collection process, provide the following:

In the captureLevel parameter, specify whether the index information is to be collected for the current connection or for the entire database. You can run multiple connection-level captures concurrently for independent connections without conflict. A database-level capture can take place in parallel with a connection-level capture. Since there is no conflict between a database-level and a connection-level capture, any outstanding connection-level captures that are already in progress when a database-level capture is initiated completes as intended. However, an error is returned if you initiate a second request for a database-level capture while the first is still active; an error is also returned if a second request for a connection-level capture from the same connection is initiated while the first connection-level capture is still active.

If you invoke ttIndexAdviceCaptureStart for a database-level capture, any outstanding connection-level captures that are already in progress complete.
The captureMode parameter designates that you want the data collection performed on one of the following scenarios:
- Perform the collection of index information using the current processing of the SQL workload.
- Base the collection of index information not on a current processing of the SQL workload, but on existing computed statistics and query plan analysis. In this scenario, the SQL statements have been prepared, but not run. This mode can only be performed with a connection-level capture.

To complete the capture, call the ttIndexAdviceCaptureEnd built-in procedure that ends either an active connection-level capture from the same connection or an active database-level capture. Completing a database-level capture requires the ADMIN privilege.

If a connection fails during a capture, the following occurs:

If the capture is a connection-level capture, the capture ends and all associated resources are freed.
If the capture is a database-level capture, the capture continues until another user with ADMIN privileges connects and invokes the ttIndexAdviceCaptureEnd built-in procedure to end a database-level capture.

If temporary space becomes full during a capture, an active capture ends and the data collected during the capture is saved.

Note:

Run ttIndexAdviceCaptureDrop to free the temporary space after a capture. See Drop Data Collected for the Index Advisor and Finalize Results.

The following example starts a collection for the Index Advisor at the connection-level for the current processing of a SQL workload:

call ttIndexAdviceCaptureStart(0,0);

The following example ends the collection for the connection-level capture:

call ttIndexAdviceCaptureEnd(0);

Note:

See ttIndexAdviceCaptureStart and ttIndexAdviceCaptureEnd in the Oracle TimesTen In-Memory Database Reference.

Retrieve Index Recommendations and Data Collection Information

Use the ttIndexAdviceCaptureInfoGet and ttIndexAdviceCaptureOutput built-in procedures to retrieve the data collection overview and Index Advisor recommendations. Run either or both for the data you want.

Call the ttIndexAdviceCaptureInfoGet built-in procedure to retrieve data collection overview information for the Index Advisor. See Retrieve Data Collection Information with ttIndexAdviceCaptureInfoGet.
Call the ttIndexAdviceCaptureOutput built-in procedure to retrieve the recommended indexes. See Retrieve Index Recommendations with ttIndexAdviceCaptureOutput.
After a DBA has evaluated the recommended index creation statements, apply the desired index creation recommendations.

Retrieve Data Collection Information with ttIndexAdviceCaptureInfoGet

The ttIndexAdviceCaptureInfoGet built-in procedure retrieves information about the data collected for the Index Advisor.

For both a connection-level capture and a database-level capture, only a single row is returned.

Note:

The database-level capture row can only be returned to a user with ADMIN privileges.

The ttIndexAdviceCaptureInfoGet built-in procedure captures data if:

The data capture was started and has not ended.
A previous capture that was started and stopped, and the data was not deleted.

Note:

If no capture is in progress or no data exists, then no rows are returned.

The rows returned include the following information:

The capture state: Returns 0 if a capture is completed. Returns 1 if a capture is still in progress.
The connection identifier, if appropriate.
The capture level and mode set for this capture.
The number of prepared and run statements during the capture interval.
The time that the capture was started and stopped.

The following shows capture information for a completed connection-level capture for 363 prepared statements and 369 run statements:

Command> call ttIndexAdviceCaptureInfoGet();
< 0, 1, 0, 0, 363, 369, 2012-07-27 11:44:08.136833, 2012-07-27 12:07:35.410993 >
1 row found.

Note:

See ttIndexAdviceCaptureInfoGet in the Oracle TimesTen In-Memory Database Reference.

Retrieve Index Recommendations with ttIndexAdviceCaptureOutput

The ttIndexAdviceCaptureOutput built-in procedure retrieves the list of index recommendations from the last recorded capture at the specified level (connection or database-level).

The list contains the CREATE statement for each recommended index.

To request index recommendations for a connection-level capture, run ttIndexAdviceCaptureOutput with captureLevel set to 0 in the same connection that initiated the capture. For a database-level capture, run ttIndexAdviceCaptureOutput with captureLevel set to 1 in a connection where the user has ADMIN privilege.

The returned row contains:

stmtCount - The number of times the index would be useful to speed up the SQL workload.
createStmt - The statement that can be used to create the recommended index. All database object names in these statements are fully qualified.

The following example provides the CREATE statement for an index called PURCHASE_i1 on the HR.PURCHASE table, which would be useful 4 times for this SQL workload.

CALL ttIndexAdviceCaptureOutput();
< 4, create index PURCHASE_i1 on HR.PURCHASE(AMOUNT); >
1 row found.

Note:

See ttIndexAdviceCaptureOutput in the Oracle TimesTen In-Memory Database Reference.

Drop Data Collected for the Index Advisor and Finalize Results

After you have applied the CREATE statements for the new indexes that have been approved by the DBA, you can drop the captured data collected for the Index Advisor. The ttIndexAdviceCaptureDrop built-in procedure drops the existing data collected for the specified captureLevel, which can either be a connection or database-level capture.

Call ttIndexAdviceCaptureDrop(0);

You must call this built-in procedure twice to drop both a connection-level and database-level capture. You may not invoke this built-in procedure while a capture at the same level is in progress.

Note:

See ttIndexAdviceCaptureDrop in the Oracle TimesTen In-Memory Database Reference.

You can repeat the steps in Prepare for Running the Index Advisor and Retrieve Index Recommendations and Data Collection Information until a SQL workload runs with no more index recommendations. You can keep updating the statistics for the tables on which the new indexes were applied and re-run the Index Advisor to see if any new indexes are now recommended.

Example Using Index Advisor Built-In Procedures

The Index Advisor built-in procedures shows the flow of a data collection for a SQL workload and provides index advice.

Command> call ttOptUpdateStats();

Command> call ttIndexAdviceCaptureStart();

Command> SELECT employee_id, first_name, last_name FROM employees;
< 100, Steven, King >
< 101, Neena, Kochhar >
< 102, Lex, De Haan >
< 103, Alexander, Hunold >
< 104, Bruce, Ernst >
...
< 204, Hermann, Baer >
< 205, Shelley, Higgins >
< 206, William, Gietz >
107 rows found.

Command> SELECT MAX(salary) AS MAX_SALARY 
FROM employees 
WHERE employees.hire_date > '2000-01-01 00:00:00';
< 10500 >
1 row found.

Command> SELECT employee_id, job_id FROM job_history 
WHERE (employee_id, job_id) NOT IN (SELECT employee_id, job_id 
FROM employees);
< 101, AC_ACCOUNT >
< 101, AC_MGR >
< 102, IT_PROG >
< 114, ST_CLERK >
< 122, ST_CLERK >
< 176, SA_MAN >
< 200, AC_ACCOUNT >
< 201, MK_REP >
8 rows found.

Command> WITH dept_costs AS 
(SELECT department_name, SUM(salary) dept_total 
FROM employees e, departments d 
WHERE e.department_id = d.department_id 
GROUP BY department_name), 
avg_cost AS 
(SELECT SUM(dept_total)/COUNT(*) avg 
FROM dept_costs) 
SELECT * FROM dept_costs 
WHERE dept_total > 
(SELECT avg FROM avg_cost) 
ORDER BY department_name;
< Sales, 304500 >
< Shipping, 156400 >
2 rows found.

Command> call ttIndexAdviceCaptureEnd();

Command> call ttIndexAdviceCaptureInfoGet();
< 0, 1, 0, 0, 9, 6, 2012-07-27 11:44:08.136833, 2012-07-27 12:07:35.410993 >
1 row found.

Command> call ttIndexAdviceCaptureOutput();
< 1, create index EMPLOYEES_i1 on HR.EMPLOYEES(SALARY); >
< 1, create index EMPLOYEES_i2 on HR.EMPLOYEES(HIRE_DATE); >
2 rows found.

Command> call ttIndexAdviceCaptureDrop();