ttOptSetFlag

Description

Allows applications to alter the generation of execution plans by the TimesTen query optimizer. It sets flags to enable or disable the use of various access methods. The changes made by this call take effect during preparation of statements and affect all subsequent calls to the ODBC functions SQLPrepare and SQLExecDirect or the JDBC methods Connection.prepareCall and Statement.execute in the current transaction. 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, they are ignored because each statement is executed within its own transaction.

Required privilege

This procedure requires no privilege.

Syntax

ttOptSetFlag('optFlag', optVal)

Parameters

ttOptSetFlag has these parameters:

Parameter	Type	Description
`optFlag`	TT_CHAR(32) NOT NULL	Name of optimizer flag.
`optVal`	TT_INTEGER NOT NULL	0 (disable) or 1 (enable).

Optimizer flags

When setting the optimizer flags, use the following character strings, which are not case sensitive:

Flag	Description
`DynamicLoadEnable`	Enables or disables dynamic load of Oracle data to a TimesTen dynamic cache group. By default, dynamic load of Oracle data is enabled.
`DynamicLoadErrorMode`	Enables or disables dynamic load error mode. It controls output of error messages upon failure of a transparent load operation on a TimesTen dynamic cache group. Disabled by default.
`FirstRow`	Enable or disable first row optimization in a SELECT, UPDATE or DELETE statement. If the SQL keyword FIRST is used in the SQL statement, it takes precedence over this optimizer hint. The FIRST keyword enables first row optimization.
`GenPlan`	Enable or disable the creation of entries in the PLAN table for the rest of the transaction. See "Generating a query plan" in Oracle TimesTen In-Memory Database Operations Guide.
`Hash`	Allow or disallow the use of existing hash indexes in indexed table scans.
`HashGb`	Allow or disallow the use of hash groups.
`NestedLoop`	Refers to a common way of joining two tables.
`PassThrough`	Temporarily changes the pass through level for IMDB Cache applications. The pass through level can be set at any time and takes effect immediately. Legal values for this flag are: 0 - SQL statements are executed only against TimesTen. 1 - Statements other than INSERT, DELETE or UPDATE and DDL are passed through if they generate a syntax error in TimesTen or if one or more tables referenced within the statement are not in TimesTen. All INSERT, DELETE and UPDATE statements will be passed through if the target table cannot be found in TimesTen. DDL statements will not be passed through. 2 - Same as 1, plus any INSERT, UPDATE and DELETE statement performed on READONLY cache group tables is passed through. 3 - All SQL statements, except COMMIT and ROLLBACK, and TimesTen built-in procedures that set or get optimizer flags are passed through. COMMIT and ROLLBACK are executed on both TimesTen and Oracle. 4 - All SELECT statements on global cache groups tables that cannot use transparent load are executed on Oracle. 5 - All SELECT statements on global cache groups tables that cannot use transparent load are executed on Oracle. The SELECT statement is not executed until after all committed changes to the global cache group are propagated to Oracle.
`RowLock`	Allow or disallow the optimizer to consider using row locks.
`Scan`	Refers to full table scans.
`Rowid`	Allow or disallow the use of Row IDs.
`ShowJoinOrder`	Shows the join order of the tables in an optimizer scan.
`TmpHash`	Allow or disallow the use of a temporary hash scan. This is an index that is created during execution for use in evaluating the statement. Though index creation is time-consuming, it can save time when evaluating join predicates.
`TblLock`	Allow or disallow the optimizer to consider using table locks.
`TmpTable`	Stores intermediate results into a temporary table. This operation is sometimes chosen to avoid repeated evaluation of predicates in join queries or sometimes just to allow faster scans of intermediate results in joins.
`TmpRange`	Performs a temporary range scan. Can also be used so that values are sorted for a merge join. Though index creation is time-consuming, it can save time when evaluating join predicates.
`Range`	Allow or disallow the use of existing range indexes in indexed table scans.

In addition, the string AllFlags can be used to refer to all optimizer flags, and the string Default can be used to refer to the default flags. Default excludes the GenPlan flag but includes all other optimizer flags.

Flag description

The value of each flag can be 1 or 0:

If 1, the operation is enabled.
If 0, the operation is disabled unless absolutely necessary.
Initially, all the flag values exceptGenPlan are 1 (all operations are permitted).

For example, an application can prevent the optimizer from choosing a plan that stores intermediate results:

ttOptSetFlag ( 'TmpTable', 0 )

Similarly, an application can specify a preference for MergeJoin:

ttOptSetFlag ( 'NestedLoop', 0 )

In the second example, the optimizer may still choose a nested loop join if a merge join is impossible (for example, if there is no merge-join predicate). Similarly, the optimizer may occasionally not be able to satisfy an application request to avoid table scans (when the Scan flag is set to 0).

You cannot specify that a particular operation is prohibited only at a certain step of a plan or that a particular join method always be done between two specific tables. Similarly, there is no way to specify that certain indexes be used or that a hash index be used to evaluate a specific predicate. Each operation is either fully permitted or fully restricted.

When a command is prepared, the current optimizer flags, index hints and join order are maintained in the structure of the compiled form of the command and are used if the command is ever reprepared by the system. See "The TimesTen Query Optimizer" in Oracle TimesTen In-Memory Database Operations Guide for an example of reprepared statements.

If both RowLock and TblLock are disabled, TimesTen uses row-locking. If both RowLock and TblLock are enabled, TimesTen uses the locking scheme that is most likely to have better performance:

TblLock status	RowLock status	Effect on the optimizer
Disabled	Disabled	Use row-level locking.
Enabled	Disabled	Use table-level locking.
Disabled	Enabled	Use row-level locking.
Enabled	Enabled	Optimizer chooses row-level or table-level locking.

In general, table-level locking is useful when a query accesses a significant portion of the rows of a table and/or when there are very few concurrent transactions accessing the table.

Result set

ttOptSetFlag returns no results.

Example

CALL ttOptSetFlag ('TmpHash', 1);