Using the Query Optimizer to Choose Optimal Plan

TimesTen invokes the optimizer for SQL statements when more than one execution plan is possible. The optimizer chooses what it thinks is the optimum plan. This plan persists until the statement is either invalidated or dropped by the application.

A statement is automatically invalidated under the following circumstances:

• An object that the command uses is dropped

• An object that the command uses is altered

• An index on a table or view that the command references is dropped

• An index is created on a table or view that the command references

You can manually invalidate statements with either of the following methods:

• Use the ttOptCmdCacheInvalidate built-in procedure to invalidate statements in the SQL command cache. See Control the Invalidation of Commands in the SQL Command Cache.

• Set the invalidation option to 1 in the ttOptUpdateStats or the ttOptEstimateStats built-in procedures. These built-in procedures also update statistics for either a specified table or all of the current user's tables.

Note:

For complete details on when to calculate statistics, see Compute Exact or Estimated Statistics. In addition, see ttOptUpdateStats, or ttOptEstimateStats in the Oracle TimesTen In-Memory Database Reference.

An invalid statement is usually re-prepared automatically just before it is re-run. This means that the optimizer is invoked again at this time, possibly resulting in a new plan. Thus, a single statement may be prepared several times.

Note:

When using JDBC, you must manually reprepare statement when a table has been altered.

A statement may have to be prepared manually if, for example, the table that the statement referenced was dropped and a new table with the same name was created. When you prepare a statement manually, you should commit the prepare statement so it can be shared. If the statement is recompiled because it was invalid, and if recompilation involves DDL on one of the referenced tables, then the prepared statement must be committed to release the command lock.

For example, in ODBC a command joining tables T1 and T2 may undergo the following changes:

Action	Description
SQLPrepare	Command is prepared.
SQLExecute	Command is run.
SQLExecute	Command is re-run.
Create Index on T1	Command is invalidated.
SQLExecute	Command is reprepared, then is run.
SQLExecute	Command is re-run.
ttOptUpdateStats on T1	Command is invalidated if the invalidate flag is passed to the ttOptUpdateStats procedure.
SQLExecute	Command is reprepared, then is run.
SQLExecute	Command is re-run.
SQLTransact	Command is committed.
SQLFreeStmt	Command is dropped.

In JDBC, a command joining tables T1 and T2 may undergo the following changes:

Action	Description
Connection.prepareStatement()	Command is prepared.
PreparedStatement.execute()	Command is run.
PreparedStatement.execute()	Command is re-run.
Create Index on T1	Command is invalidated.
PreparedStatement.execute()	Command is reprepared, then is run.
PreparedStatement.execute()	Command is re-run.
ttOptUpdateStats on T1	Command is invalidated if the invalidate flag is passed to the ttOptUpdateStats procedure.
PreparedStatement.execute()	Command is reprepared, then is run.
PreparedStatement.execute()	Command is re-run.
Connection.commit()	Command is committed.
PreparedStatement.close()	Command is dropped.

As illustrated, optimization is generally performed at prepare time, but it may also be performed later when indexes are dropped or created, or when statistics are modified. Optimization does not occur if a prepare can use a command in the cache.

If a command was prepared with the genPlan flag set, it is recompiled with the same flag set. Thus, the plan is generated even though the plan for another query was found in the SYS.PLAN table.

If an application specifies optimizer hints to influence the optimizer's behavior, these hints persist until the command is deleted. See Modifying a SQL Query Execution Plan. For example, when the ODBC SQLPrepare function or JDBC Connection.prepareStatement() method is called again on the same handle or when the SQLFreeStmt function or PreparedStatement.close() method is called. This means that any intermediate reprepare operations that occur because of invalidations use those same hints.