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 RowIDs. If enabled, RowIDs are not internally generated for optimization purposes. If disabled, RowIDs 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.