Apply Connection Level Optimizer Hints for a TimesTen Connection

To change the query optimizer behavior for all statements of a specific connection, define the OptimizerHint connection attribute.

The value of the OptimizerHint connection attribute is a string that uses the same format as a statement level optimizer hint, but without the delimiters of /*+, */, and --+.

You cannot include comments with connection level optimizer hints.

Transaction level optimizer hints overwrite connection level optimizer hints for the current transaction. After a commit, the transaction level optimizer hints are lost and the connection level optimizer hints take effect. Statement level optimizer hints overwrite transaction level optimizer hints and connection level optimizer hints for the scope of the statement. Since this is a connection attribute, the ttConfiguration utility shows the connection level optimizer hints.

Note:

In a client server setting, the client connection setting of this connection attribute overwrites the Server DSN setting of this attribute.

This example illustrates how to use connection level optimizer hints. This example uses TT_RowLock, TT_TblLock, and TT_MergeJoin to enable row locking, disable table locking, and disable merge joins. Note that /disk1/timesten is the timesten_home.

...
[database1]
Driver=/disk1/timesten/install/lib/libtten.so
DataStore=/disk1/timesten/info/DemoDataStore/database1
PermSize=128
TempSize=64
DatabaseCharacterSet=AL32UTF8
OptimizerHint= TT_RowLock (1) TT_TblLock (0) TT_MergeJoin (0)
...

TimesTen does not support the use of the semicolon (;) character in connection attributes. Therefore, when you want to use more than one TT_INDEX optimizer hint at the connection level, you cannot use the same syntax that you use for the statement level.

For example, TT_INDEX(EMPLOYEES,EMP_NAME_IX,1;EMPLOYEES,EMP_MANAGER_IX,1) is a valid statement level optimizer hint, but it is an invalid connection level optimizer hint. TimesTen merges multiple TT_INDEX optimizer hints if they are specified on the same line. Therefore, TT_INDEX(EMPLOYEES,EMP_NAME_IX,1) TT_INDEX(EMPLOYEES,EMP_MANAGER_IX,1)is equivalent to TT_INDEX(EMPLOYEES,EMP_NAME_IX,1;EMPLOYEES,EMP_MANAGER_IX,1).

This example illustrates the use of multiple TT_INDEX optimizer hints at the connection level. Note that /disk1/timesten is the timesten_home.

...
[database1]
Driver=/disk1/timesten/install/lib/libtten.so
DataStore=/disk1/databases/info/DemoDataStore/database1
PermSize=128
TempSize=64
DatabaseCharacterSet=AL32UTF8
OptimizerHint= TT_INDEX(EMPLOYEES,EMP_NAME_IX,1) TT_INDEX(EMPLOYEES,EMP_MANAGER_IX,1)
...

See OptimizerHint in the Oracle TimesTen In-Memory Database Reference.