Optimizer Hints

Optimizer hints are instructions that are passed to the TimesTen query optimizer. The optimizer considers these hints when choosing the best execution plan for your query. Most of the hints are supported both in TimesTen Scaleout and in TimesTen Classic. There are also hints that are supported only in TimesTen Scaleout. See Optimizer Hints Supported in TimesTen Scaleout Only for information.

TimesTen supports three levels of optimizer hints:

  • Statement level optimizer hints: When specified, the optimizer considers the hint for the particular statement. See Statement Level Optimizer Hints for details.

  • Transaction level optimizer hints: When specified (by calling the appropriate built-in procedure), the optimizer considers the hint for the entire transaction. See Use Optimizer Hints to Modify the Execution Plan in the Oracle TimesTen In-Memory Database Operations Guide.

  • Connection level optimizer hints: When specified, the optimizer considers the hint for the entire connection. See Use Optimizer Hints to Modify the Execution Plan in the Oracle TimesTen In-Memory Database Operations Guide and OptimizerHint in the Oracle TimesTen In-Memory Database Reference for details.

The order of precedence for optimizer hints is statement level hints, transaction level hints and then connection level hints. Table 6-2 provides a summary of the statement, transaction, and connection level optimizer hints.

Table 6-2 Summary of Statement, Transaction, and Connection Level Optimizer Hints

Statement Level Optimizer Hint Transaction Level Optimizer Hint Connection Level Optimizer Hint

You specify the hint within the comment syntax and after a SQL VERB in a SQL statement.

You specify the hint by calling the ttOptSetFlag, or the ttOptSetOrder, or the ttOptUseIndex built-in procedure.

You specify the hint in the OptimizerHint general connection attribute.

The hint is scoped to the SQL statement.

The hint is scoped to the transaction.

The hint is scoped to the connection.

The autocommit setting has no effect. After the statement containing the hint is executed, the hint has no effect on future statements or queries.

The autocommit setting has an effect. You must set autocommit to off. Doing so ensures the hint is in effect for the duration of your transaction (until you issue a commit or rollback). If you do not set autocommit to off, the statement is executed in its own transaction and the hint only has an effect on the statement.

The autocommit setting has no effect. The hint is in effect for the duration of the connection.

The optimizer considers the hint for the statement only.

The optimizer considers the hint for all statements in the transaction.

The optimizer considers the hint for all statements in the connection.

The hint is supported in the CREATE TABLE...AS SELECT statement.

The hint is not supported in the CREATE TABLE...AS SELECT statement. This is a DDL statement and TimesTen implicitly commits a DDL statement.

The hint is not supported in the CREATE TABLE...AS SELECT statement. This is a DDL statement and TimesTen implicitly commits a DDL statement.

If you specify the hint in a transaction in which transaction level optimizer hints or connection level optimizer hints are specified, the statement level optimizer hint overrides the transaction level hint or the connection level hint for the SQL statement. After TimesTen executes the SQL statement:

  • The original transaction level optimizer hint remains in effect for the duration of the transaction or

  • The original connection level optimizer hint remains in effect for the duration of the connection.

The hint is in effect for the duration of the transaction. If you specify a statement level optimizer hint in a SQL statement, the statement level optimizer hint is in effect for the statement and the optimizer does not use the transaction level hint for the statement. After TimesTen executes the statement, the original transaction level optimizer hint remains in effect for the duration of the transaction.

A hint specified at this level overrides the same hint specified at the connection level.

The hints are in effect for the duration of the connection. The order of precedence is statement level, transaction level, and then connection level.

You use the statement level optimizer hints if you want to influence the optimizer for a specific statement. You must specify the hint for each statement in which you want to influence the optimizer. This could result in multiple alterations to your statements.

You use the transaction level optimizer hints to influence the optimizer for all statements in a transaction. You do not have to specify a hint for each statement. The hint applies to all statements in the transaction. The hint can be overridden by specifying the hint at the statement level.

You use the connection level optimizer hint to influence the optimizer for all statements in the connection. The hint can be overridden by specifying the hint at the transaction or at the statement level.