ttOptUseIndex

This procedure enables applications to alter the generation of execution plans by the TimesTen query optimizer. Applications can call this procedure to disable the use of a set of indexes or enable the consideration of only a set of indexes for each correlation used in a query. Enabling the consideration of an index does not guarantee that the plan generated uses the index. Depending on the estimated cost, the optimizer might choose to use a serialization scan or a materialization scan to access the associated correlation if these scans resulted in a better plan than the ones that use the specified index.

The changes made by this call take effect immediately and affect all subsequent calls to the ODBC functions SQLPrepare and SQLExecDirect or the JDBC methods Connection.prepareCall and Statement.execute in the current transaction until the applications explicitly issue a call to clear it. The setting is cleared whenever a new transaction is started.

AutoCommit must be set to OFF when running this built-in procedure.

Required Privilege

This procedure requires no privilege.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in TimesTen Classic.

TimesTen Scaleout applications can call this built-in procedure.

In TimesTen Scaleout, this procedure runs locally on the element from which it is called.

Related Views

This procedure has no related views.

Syntax

ttOptUseIndex('IndexName, CorrelationName, 0 | 1')

Parameters

ttOptUseIndex has a single comma-delimited string parameter, indOption, of type TT_VARCHAR(1024) with these components:

Component	Description
`IndexName`	The name of the user-defined index or `'_TMPRANGE'` for temporary range index or `'_TMPHASH'` for temporary hash index. If index name is omitted, the setting applies to all indexes of the specified correlation.
`CorrelationName`	The correlation name of the table. If a table is defined with a correlation name in the `FROM` clause, use this correlation name instead of the table name when specifying the index hint for this table. If correlation name is omitted for an entry, the setting affects all tables with the specified index name.
`0 \| 1`	Disables(`0`) or enables (`1`) the use of the index specified by `IndexName`.

Result Set

ttOptUseIndex returns no results.

Examples

CALL ttOptUseIndex('"3456"."1234", t1, 0');

CALL ttOptUseIndex('data1.i1, data1.t1, 0');

CALL ttOptUseIndex('i1, t1, 0');

Note:

If ttOptUseIndex is called without a parameter or with a NULL value, TimesTen clears the previous index hint.