Apply Transaction Level Optimizer Hints for a Transaction

To change the query optimizer behavior for all statements in a transaction, an application calls the ttOptSetFlag built-in procedure using the ODBC procedure call interface.

Note:

Make sure autocommit is off for transaction level optimizer hints. All optimizer flags are reset to their default values when the transaction has been committed or rolled back. If optimizer flags are set while autocommit is on, the optimizer flags are ignored because each statement runs within its own transaction.

• ttOptSetFlag—Sets certain optimizer parameters. Provides the optimizer with transaction level optimizer hints with a recommendation on how to best optimize a particular query.

• ttOptGetFlag—View the existing transaction level hints set for a database.

• ttOptSetOrder—Enables an application to specify the table join order.

• ttOptUseIndex—Enables an application to specify that an index be used or to disable the use of certain indexes; that is, to specify which indexes should be considered for each correlation in a query.

• ttOptClearStats, ttOptEstimateStats, ttOptSetColIntvlStats, ttOptSetTblStats, ttOptUpdateStats—Manipulate statistics that TimesTen maintains on the application's data that are used by the query optimizer to estimate costs of various operations.

Some of these built-in procedures require that the user have privileges to the objects on which the utility runs. See Built-In Procedures in the Oracle TimesTen In-Memory Database Reference.

The following examples provide an ODBC and JDBC method on how to use the ttOptSetFlag built-in procedure:

Note:

You can also experiment with optimizer settings using the ttIsql utility. The commands that start with "try" control transaction level optimizer hints. To view current transaction level optimizer hint settings, use the optprofile command.

Example: Using ttOptSetFlag in JDBC

This JDBC example illustrates the use of ttOptSetFlag to prevent the optimizer from choosing a merge join.

import java.sql.*; 
class Example 
{ 
 public void myMethod() { 
    CallableStatement cStmt; 
    PreparedStatement pStmt;
     . . . . . 
    try {
         . . . . . . . 
        // Prevent the optimizer from choosing Merge Join 
        cStmt = con.prepareCall("{ 
            CALL ttOptSetFlag('MergeJoin', 0)}"); 
        cStmt.execute();
        // Next prepared query 
        pStmt=con.prepareStatement( 
        "SELECT * FROM Tbl1, Tbl2 WHERE Tbl1.ssn=Tbl2.ssn");
        . . . . . . . 
        catch (SQLException ex) { 
            ex.printStackTrace(); 
        } 
    } 
    . . . . . . .
}

Example: Using ttOptSetFlag in ODBC

This ODBC example illustrates the use of ttOptSetFlag to prevent the optimizer from choosing a merge join.

#include <sql.h>
SQLRETURN rc;
SQLHSTMT hstmt; fetchStmt;
....
rc = SQLExecDirect (hstmt, (SQLCHAR *)
     "{CALL ttOptSetFlag (MergeJoin, 0)}",
     SQL_NTS)
/* check return value */
...
rc = SQLPrepare (fetchStmt, ...)
/* check return value */
...