Use Optimizer Hints to Modify the Execution Plan

You can apply hints to pass instructions to the TimesTen query optimizer.

• To apply a hint only for a particular SQL statement, use a statement level optimizer hint.

• To apply a hint for an entire transaction, use a transaction level optimizer hint with the appropriate TimesTen built-in procedure.

• To apply a hint for an entire TimesTen connection, use a connection level optimizer hint.

The order of precedence for optimizer hints is statement level hints, transaction level hints and then connection level hints.

Note:

TimesTen concurrently processes read and write operations optimally. Your read operations can be optimized for read-only concurrency when you use transaction level optimizer hints such as ttOptSetFlag ('tblLock',1) or statement level optimizer hints such as /*+ tt_tbllock(1) tt_rowlock(0) */. Write operations that operate concurrently with read optimized operations may result in contention.

You can control read optimization during periods of concurrent write operations with the ttDbWriteConcurrencyModeSet built-in procedure. For more information, see Control Read Optimization During Concurrent Write Operations.

Directions for applying hints are described in the following sections:

• Apply Statement Level Optimizer Hints for a SQL Statement

• Apply Transaction Level Optimizer Hints for a Transaction

• Apply Connection Level Optimizer Hints for a TimesTen Connection