ttOptSetFlag

This procedure resets all optimizer flags to their default values when the transaction has been committed or rolled back. This alters 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. If optimizer flags are set while AutoCommit is on, they are ignored.

Required Privilege

This procedure requires no privilege.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in TimesTen Classic.

TimesTen Scaleout applications can call this built-in procedure.

In TimesTen Scaleout, this procedure runs locally on the element from which it is called.

Related Views

This procedure has these related views.

SYS.GV$OPT_FLAG

SYS.V$OPT_FLAG

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`	The value of the optimizer flag. The value is generally `0` (disable/disallow) or `1` (enable/allow), except as described under Optimizer Flags below.

Optimizer Flags

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

Flag	Description
`BranchAndBound`	Enables or disables branch and bound optimization. If enabled, TimesTen calculates the maximum cost of the query plan during a "zero phase," at the very beginning of the optimization process. If disabled, TimesTen does not perform this cost analysis.
`DynamicLoadEnable`	Enables or disables dynamic load of a single cache instance from an Oracle database to a TimesTen dynamic cache group. By default, dynamic load of data from an Oracle database 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.
`DynamicLoadMultiplePKs`	Enables or disables dynamic load of multiple cache instances (providing multiple primary keys) from an Oracle database to a TimesTen dynamic cache group that contains only a root table. By default, dynamic load multiple cache instances using multiple primary keys from an Oracle database is enabled.
`DynamicLoadRootTbl`	Enables or disables dynamic load of multiple cache instances (without providing multiple primary keys) from an Oracle database to a TimesTen dynamic cache group that contains only a root table. By default, dynamic load multiple cache instances from an Oracle database is disabled.
`FirstRow`	Enables or disables 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.
`ForceCompile`	Enables or disables forced compilation. If enabled, TimesTen recompiles the query and regenerates the query plan each time. If disabled, TimesTen does not compile the query plan even if it is available.
`GenPlan`	Enables or disables the creation of entries in the `PLAN` table for the rest of the transaction. For an example, see Instruct TimesTen to Store the Plan in the System PLAN Table in Oracle TimesTen In-Memory Database Operations Guide.
`Hash`	Enables or disables the use of existing hash indexes in indexed table scans.
`HashGb`	Enables or disables the use of hash groups.
`IndexedOR`	Enables or disables serialized table scans. If disabled, TimesTen uses serialized table scans for `IN...`list conditions, else TimesTen uses multiple index scans for an `OR` condition.
`MergeJoin`	Enables or disables the use of merge joins.
`NestedLoop`	Refers to a common way of joining two tables.
`NoRemRowIdOpt`	Enables or disables internal generation of `RowID`s. If enabled, `RowID`s are not internally generated for optimization purposes. If disabled, `RowID`s may be internally generated, even if the row is not in the `SELECT` list.
`PassThrough`	Temporarily changes the pass through level for TimesTen Cache applications. The pass through level can be set at any time and takes effect immediately. Supported values for this flag are: `0` - (default) - SQL statements are run only on TimesTen. `1` - `INSERT`, `UPDATE` and `DELETE` statements are run on TimesTen unless they reference one or more tables that are not in TimesTen. If they reference one or more tables not in TimesTen, they are passed through to the Oracle database. DDL statements are run on TimesTen. Other statements are passed through to the Oracle database if they generate a syntax error in TimesTen or if one or more tables referenced within the statement are not in TimesTen. `2 - INSERT`, `UPDATE` and `DELETE` statements performed on tables in read-only cache groups or user managed cache groups with the `READONLY` cache table attribute are passed through to the Oracle database. Passthrough behavior for other cache group types is the same as `PassThrough=1`. `3` - All statements are passed through to the Oracle database.
`Range`	Enables or disables the use of existing range indexes in indexed table scans.
`Rowid`	Enables or disables the use of Row IDs.
`RowLock`	Allows or disallows the optimizer to consider using row locks.
`Scan`	Refers to full table scans.
`ShowJoinOrder`	Shows the join order of the tables in an optimizer scan.
`TblLock`	Enables or disables the optimizer to consider using table locks.
`TmpHash`	Enables or disables 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.
`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.
`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.
`UseBoyerMooreStringSearch`	Enables or disables the Boyer-Moore string search algorithm. If enabled, Boyer-Moore string search algorithm is enabled. This can improve performance of `LIKE` operations.

In addition, you can use the string AllFlags to refer to all optimizer flags, and the string Default 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 except GenPlan 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 ( 'MergeJoin', 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 DynamicLoadMultiplePKs and DynamicLoadRootTbl are enabled, DynamicLoadMultiplePKs has precedence.

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 or when there are very few concurrent transactions accessing the table.

Result Set

ttOptSetFlag returns no results.

Examples

CALL ttOptSetFlag ('TmpHash', 1);

Note:

You can also set the join order using statement level optimizer hints in certain SQL statements. For details, see Statement Level Optimizer Hints in the Oracle TimesTen In-Memory Database SQL Reference. Specifically, see the table Optimizer Hints to understand the behavior of each style of hint.