Statement Level Optimizer Hints

Statement level optimizer hints are comments in a SQL statement that pass instructions to the TimesTen query optimizer. The optimizer considers these hints when choosing the best execution plan for your query. It analyzes the SQL statements and generates a query plan which is then used by the SQL execution engine to execute the query and return the data.

See Use Optimizer Hints to Modify the Execution Plan in Oracle TimesTen In-Memory Database Operations Guide for information about statement level optimizer hints.

SQL Syntax

A SQL statement can have one comment that includes one or more statement level optimizer hints.

These hints are only supported in TimesTen Cache:

TT_DynamicLoadMultiplePKs
TT_DynamicLoadRootTbl
TT_DynamicPassThrough

Some hints are not supported in certain SQL statements:

TT_CommitDMLOnSuccess is supported in the DELETE, INSERT, and UPDATE statements. It is also valid in the INSERT...SELECT statement and must follow the SELECT keyword. This hint is supported in TimesTen Scaleout only.
The TT_GridQueryExec and TT_PartialResult hints are supported in the SELECT, INSERT...SELECT, and CREATE TABLE... AS SELECT SQL statements only and these hints must follow the SELECT keyword. These hints are supported in TimesTen Scaleout only.
The remaining hints are supported in the DELETE, INSERT, MERGE, SELECT, UPDATE, INSERT...SELECT, and CREATE TABLE...AS SELECT SQL statements and these hints must follow the DELETE, INSERT, MERGE, SELECT, or UPDATE keyword.

You embed statement level optimizer hints in comment syntax. TimesTen supports hints in comments that span one line and in comments that span more than one line. If your comment that contains the hint spans one or more lines, use the comment syntax, /*+...*/. If your comment that contains the hint spans one line, use the comment syntax, --+.

Syntax:

SQL VERB {/*+ [CommentText] hint [{hint|CommentText} [...]] */ |
          --+ [CommentText] hint [{hint|CommentText} [...]] }
hint::= ScaleoutHint | CacheHint | JoinOrderHint | IndexHint| FlagHint
ScaleoutHint::= TT_CommitDMLOnSuccess({0|1})|TT_GridQueryExec({LOCAL|GLOBAL})|
                TT_PartialResult(0|1)
CacheHint::= TT_DynamicLoadMultiplePKs ({0|1})|TT_DynamicLoadRootTbl ({0|1})|
             TT_DynamicPassthrough(N) 
JoinOrderHint::= TT_JoinOrder (CorrelationName CorrelationName [...])
IndexHint::= TT_Index (CorrelationName,IndexName,{0|1} [;...])
FlagHint::= FlagName (0|1)
FlagName::= TT_BranchAndBound|TT_CountAsInt|TT_DynamicLoadEnable|
            TT_DynamicLoadErrorMode| TT_FirstRow|TT_ForceCompile|
            TT_GenPlan|TT_HashGb|TT_HashScan|TT_IndexedOr|TT_MergeJoin|
            TT_NestedLoop|TT_NoRemRowIdOpt|TT_Range|TT_Rowid|TT_RowLock|
            TT_ShowJoinOrder|TT_TblLock|TT_TblScan|TT_TmpHash|TT_TmpRange|
            TT_TmpTable|TT_UseBoyerMooreStringSearch|

Parameters

Parameter	Description
`SQL` `VERB`	`SQL` `VERB` refers to one of the keywords: `DELETE`, `INSERT`, `MERGE`, `SELECT`, or `UPDATE`. You embed a statement level optimizer hint in comment syntax and if the comment syntax contains a statement level optimizer hint, the comment syntax must follow the `SQL` `VERB`. The `TT_GridQueryExec` and `TT_PartialResult` hints are valid for the `SELECT` keyword only.
`/+` `hint` `/`	One or more hints that are embedded in comment syntax. The comment syntax can span one or more lines. The plus sign (`+`) denotes the start of a statement level optimizer hint. Make sure there is no space between the star (`*`) and the plus sign (`+`).
`--+` `hint`	One or more hints that are embedded in comment syntax. The comment syntax can only span one line. The plus sign (`+`) denotes the start of a statement level optimizer hint. Make sure there is no space between the dash (`-`) and the plus sign (`+`).
`hint`	A statement level optimizer hint. A SQL statement supports one or more statement level optimizer hints as one comment string. For one SQL statement, you can specify one comment that contains one or more hints and that comment must follow a `DELETE`, `INSERT`, `MERGE`, `SELECT`, or `UPDATE` keyword (or for `TT_GridQueryExec` and `TT_PartialResult`, the `SELECT` keyword). `TT_CommitDMLOnSuccess` must follow a `DELETE`, `INSERT`, or `UPDATE` keyword and in the `INSERT...SELECT` statement, it must follow the `SELECT` keyword. If you specify more than one hint within the comment, make sure there is a space between the hints. Statement level optimizer hints are scoped to a SQL statement and have per query semantics. For hints other than `TT_GridQueryExec`, `TT_PartialResult`, or `TT_CommitDMLOnSuccess`: The name and type of statement level optimizer hints map to the transaction level optimizer hints. Transaction level optimizer hints are invoked by calling the built-in procedures `ttOptSetFlag`, `ttOptSetOrder`, or `ttOptUseIndex`. Transaction level hints are scoped to the transaction and have transaction semantics. You must set autocommit to 0 and the transaction level optimizer hint is in effect for the duration of your transaction. For more information, see ttOptSetFlag," "ttOptSetOrder or ttOptUseIndex in the Oracle TimesTen In-Memory Database Reference.
`CommentText`	Text within a comment string. You can use both statement level optimizer hints and commenting text within one comment. Make sure to include a space between the hint and the commenting text.
`ScaleoutHint`	`ScaleoutHint` refers to the `TT_CommitDMLOnSuccess` statement level hint as well as the `TT_GridQueryExec` and the `TT_PartialResult` statement level optimizer hints. These hints are supported in TimesTen Scaleout only. See TT_CommitDMLOnSuccess Optimizer Hint for detailed information. `TT_GridQueryExec (LOCAL\|GLOBAL)` returns query results for the local element (if `LOCAL`) or for all elements (if `GLOBAL`). If K-safety is set to 2 and `GLOBAL` is specified, the results include the data in all elements in a replica set. `LOCAL`: Queries are executed in the local element only. If the local element does not have a full copy of the data, TimesTen Scaleout returns partial results. `GLOBAL`: TimesTen Scaleout retrieves data from all elements, including copies of the rows from all tables from all replica sets to generate the results. This results in duplicate data returned if K-safety is set to 2 or if tables have a duplicate distribution scheme. The default is neither local nor global. If you do not specify this hint, the query is executed in one logical data space. Exactly one full copy of the data is used to compute the query. `TT_PartialResult (0\|1)` returns an error (if `0`) or partial results (if `1`) when data is not available. `0`: Returns error if the required data is not available in the case where all elements in a replica set are not available. If at least one element from each replica set is available or the data required by the query is available, the optimizer returns the query result correctly without error. This is the default. `1`: Returns partial results if all elements in a replica set are not available. SELECT /+TT_GridQueryExec(LOCAL)/ COUNT(), elementId# FROM t GROUP BY elementId#; SELECT /+TT_GridQueryExec(GLOBAL)/ COUNT(), elementId# FROM t GROUP BY elementId#; SELECT /+TT_PartialResult(0)/ COUNT (), elementId# FROM t GROUP BY elementId#; SELECT /+TT_PartialResult(1)/ COUNT (), elementId# FROM t GROUP BY elementId#;
`CacheHint`	`CacheHint` refers to the supported optimizer hints for TimesTen Cache. These hints are `TT_DynamicLoadMultiplePKs`, `TT_DynamicLoadRootTbl`, and `TT_DynamicPassthrough`. These hints are described later in this table (in alphabetical order).
`JoinOrderHint::=` `TT_JoinOrder` `(` `CorrelationName` `CorrelationName` `[...] )`	`JoinOrderHint` refers to the syntax for the `TT_JoinOrder` statement level optimizer hint. The `TT_JoinOrder` hint instructs the optimizer to join your tables in a specified order. The join order is in effect for the statement only. Specify `TT_JoinOrder` and two or more correlation names. Make sure to enclose the correlation names in parentheses. `CorrelationName` refers to the alias of your table specified in the query or subquery of your SQL statement. Make sure to use the correlation name rather than the actual table name when using this hint. For example, if you are joining the `employees` and `departments` tables and you specify the `emps` correlation name for the `employees` table and the `depts` correlation name for the `departments` table, then to specify the `TT_JoinOrder` hint in `ttIsql`: Command> SELECT /+ TT_JoinOrder (EMPS DEPTS)/... If your `TT_JoinOrder` hint contains syntax errors, TimesTen ignores the hint. If your `TT_JoinOrder` hint does not contain a closing parenthesis, then the remainder of the comment string is ignored. So if you specify additional statement level optimizer hints after the `TT_JoinOrder` hint, and the `TT_JoinOrder` hint is missing the closing parenthesis, these additional statement level optimizer hints are ignored. You can execute the built-in procedure, `ttOptSetOrder`, to specify a join order for the duration of your transaction. The `TT_JoinOrder` statement level optimizer hint is in effect for the statement only. For more information on `ttOptSetOrder`, see ttOptSetOrder" in the Oracle TimesTen In-Memory Database Reference.
`IndexHint::=` `TT_INDEX` `(CorrelationName` `IndexName` `{0`\|`1} [;...]` `)`	`IndexHint` refers to the syntax for the `TT_INDEX` statement level optimizer hint. Use the `TT_INDEX` hint to direct the optimizer to use or not use an index for your table. The index hint is in effect for the statement only. `CorrelationName` refers to the alias of your table specified in the query or subquery of your SQL statement. Make sure to use the correlation name rather than the actual table name. If you omit the correlation name, the setting affects all tables with the specified index name. `IndexName` refers to the index you want the optimizer to consider or not consider for the table If you omit the index name, the setting applies to all indexes of the specified correlation. Specify a value of 0 to ask the optimizer not to consider the index. Specify a value of 1 to ask the optimizer to consider the index. For example, To direct the optimizer to use the index `emp_name_ix` for a query on the `employees` table and you specify the correlation name, `e`, for the `employees` table, then to specify the `TT_INDEX` hint in `ttIsql`: Command> SELECT /+ TT_INDEX (E,EMP_NAME_IX,1) / ... Use a semicolon (;) to include more than one `CorrelationName`, `IndexName`, {`0`\|`1`} for the `TT_INDEX` hint. You must specify each of the parameters: the `CorrelationName`, the `IndexName`, and either 0 or 1. If your `TT_Index` hint contains syntax errors, TimesTen ignores the hint. If your `TT_Index` hint does not contain a closing parenthesis, then the remainder of the comment string is ignored. So if you specify additional statement level optimizer hints after the `TT_Index` hint and the `TT_Index` hint is missing the closing parenthesis, these additional statement level optimizer hints are ignored. You can execute the built-in procedure, `ttOptUseIndex`, to specify an index hint for the duration of your transaction. The `TT_INDEX` statement level optimizer hint is in effect for the statement only. For more information on `ttOptUseIndex`, see ttOptUseIndex" in the Oracle TimesTen In-Memory Database Reference.
`FlagHint`	`FlagHint` refers to statement level optimizer flags that you enable or disable to influence the execution plan of the TimesTen query optimizer. These flags map to the flags used in the `ttOptSetFlag` built-in procedure. Statement level optimizer hint flags are in effect for the statement only whereas transaction level optimizer hint flags are in effect for the duration of your transaction.
`TT_BranchAndBound`	Flag that maps to the flag `BranchAndBound` in the `ttOptSetFlag` built-in procedure.
`TT_CountAsInt`	This hint controls the return data type for the `COUNT` function (used in a query). Specify `1` to have the return data type be `TT_INTEGER`. Specify `0` to have the return data type be `TT_BIGINT`. If you do not specify this hint, the default return data type is `TT_BIGINT`. This hint is supported at the statement and at the connection levels. See "COUNT" for information on the `COUNT` function. This hint is provided for backward compatibility. If you specify the hint with a value of `1`, it may result in an unexpected integer overflow. New applications should not specify this hint. This ensures TimesTen uses the default return data type of `TT_BIGINT` for the `COUNT` function. This example specifies a value of `1` for the hint. The return data type is `TT_INTEGER`: Command> describe SELECT /+TT_CountAsInt(1)/ COUNT () FROM dual; Prepared Statement: Columns: EXP TT_INTEGER NOT NULL This example specifies a value of `0` for the hint. The return data type is `TT_BIGINT`. Command> describe SELECT /+TT_CountAsInt(0)/ COUNT () FROM dual; Prepared Statement: Columns: EXP TT_BIGINT NOT NULL This example does not set the optimizer hint. The default return data type is `TT_BIGINT`. describe SELECT COUNT (*) FROM dual; Prepared Statement: Columns: EXP TT_BIGINT NOT NULL
`TT_DynamicLoadEnable`	Flag that maps to the flag `DynamicLoadEnable` in the `ttOptSetFlag` built-in procedure.
`TT_DynamicLoadErrorMode`	Flag that maps to the flag `DynamicLoadErrorMode` in the `ttOptSetFlag` built-in procedure.
`TT_DynamicLoadMultiplePKs{ 0\|1}`	TimesTen Cache optimizer hint, supported in TimesTen Classic. This hint enables (if set to `1`) or disables (if set to `0`), the ability to dynamically load multiple cache instances on a single table cache group. The dynamic load operation must be triggered by a qualified `SELECT` statement that contains a `WHERE` clause, in which the `WHERE` clause references multiple primary key values of the root table of the cache group. The default is `1`. When both the `TT_DynamicLoadMultiplePKs` and the `TT_DynamicLoadRootTbl` hints are specified, the `TT_DynamicLoadMultiplePKs` take precedence.
`TT_DynamicLoadRootTbl`	TimesTen Cache optimizer hint, supported in TimesTen Classic. This hint enables (if set to `1`) or disables (if set to `0`), the ability to dynamically load multiple cache instances on a single table cache group. The dynamic load operation must be triggered by a qualified `SELECT` statement that contains a `WHERE` clause, in which the `WHERE` clause does not reference multiple primary key values of the root table of the cache group. The default is `0`. When both the `TT_DynamicLoadMultiplePKs` and the `TT_DynamicLoadRootTbl` hints are specified, the `TT_DynamicLoadMultiplePKs` take precedence.
`TT_DynamicPassThrough(N)`	TimesTen Cache optimizer hint, supported in TimesTen Classic. If specified, this hint limits the number of rows that can be dynamically loaded into a TimesTen cache instance. Specifically, if a dynamic load operation triggered by a qualified `SELECT` statement results in a number of rows that is greater than the specified `N` row limit, the cache instance is not loaded and instead the query is passed to the Oracle database. The dynamic load must be triggered by a qualified `SELECT` statement and the cache group must not have a `WHERE` clause. The hint is ignored for non-`SELECT` statements. Set this hint to the maximum number of rows you want dynamically loaded. If you set the hint to a value less than or equal to `0` or if you do not specify the hint, the dynamic load has no row limit. In this case, there is not a limit in the number of rows can be loaded into the cache instance. See Automatic Passthrough of Dynamic Load to the Oracle Database in the Oracle TimesTen In-Memory Database Cache Guide for details.
`TT_FirstRow`	Flag that maps to the flag `FirstRow` in the `ttOptSetFlag` built-in procedure.
`TT_ForceCompile`	Flag that maps to the flag `ForceCompile` in the `ttOptSetFlag` built-in procedure.
`TT_GenPlan`	Flag that maps to the flag `GenPlan` in the `ttOptSetFlag` built-in procedure.
`TT_HashGb`	Flag that maps to the flag `HashGb` in the `ttOptSetFlag` built-in procedure.
`TT_HashScan`	Flag that maps to the flag `Hash` in the `ttOptSetFlag` built-in procedure.
`TT_IndexedOr`	Flag that maps to the flag `IndexedOr` in the `ttOptSetFlag` built-in procedure.
`TT_MergeJoin`	Flag that maps to the flag `MergeJoin` in the `ttOptSetFlag` built-in procedure.
`TT_NestedLoop`	Flag that maps to the flag `NestedLoop` in the `ttOptSetFlag` built-in procedure.
`TT_NoRemRowIdOpt`	Flag that maps to the flag `NoRemRowIdOpt` in the `ttOptSetFlag` built-in procedure.
`TT_Range`	Flag that maps to the flag `Range` in the `ttOptSetFlag` built-in procedure.
`TT_Rowid`	Flag that maps to the flag `Rowid` in the `ttOptSetFlag` built-in procedure.
`TT_RowLock`	Flag that maps to the flag `Rowlock` in the `ttOptSetFlag` built-in procedure.
`TT_ShowJoinOrder`	Flag that maps to the flag `ShowJoinOrder` in the `ttOptSetFlag` built-in procedure.
`TT_TblLock`	Flag that maps to the flag `TblLock` in the `ttOptSetFlag` built-in procedure.
`TT_TblScan`	Flag that maps to the flag `Scan` in the `ttOptSetFlag` built-in procedure.
`TT_TmpHash`	Flag that maps to the flag `TmpHash` in the `ttOptSetFlag` built-in procedure.
`TT_TmpRange`	Flag that maps to the flag `TmpRange` in the `ttOptSetFlag` built-in procedure.
`TT_TmpTable`	Flag that maps to the flag `TmpTable` in the `ttOptSetFlag` built-in procedure.
`TT_UseBoyerMooreStringSearch`	Flag that maps to the flag `UseBoyerMooreStringSearch` in the `ttOptSetFlag` built-in procedure.

Note:

For descriptions of flags discussed in the preceding table, see ttOptSetFlag" in the Oracle TimesTen In-Memory Database Reference

Description

Embed statement level optimizer hints in comment syntax. Begin the comment with either /* or --. Follow the beginning comment syntax with a plus sign (+). The plus sign (+) signals TimesTen to interpret the comment as a list of hints. The plus sign (+) must follow immediately after the comment delimiter. (For example, after /* or after --). No space is permitted between the comment delimiter and the plus sign (+).

In the following example, there is a space between the star (*) and the plus sign (+), so the hint is ignored:
```
Command> SELECT /* + TT_TblScan (1) This hint is ignored because there is a
          space between the star (*) and the plus (+) sign. */ ...
```
A hint is one of the statement level optimizer hints supported by TimesTen. There can be a space between the plus sign (+) and the hint. If the comment contains multiple hints, separate the hints by at least one space. For example, to specify two hints on one line:
```
Command> SELECT --+ TT_MergeJoin (0) TT_NestedLoop (1) 
          ...
```

You can intersperse commenting text with hints in a comment. For example,

Command> SELECT /*+ TT_HashScan (1) This demonstrates a hint followed by a
           comment string. */ ...

TimesTen ignores hints and does not return an error if:
- Your hint does not follow the DELETE, INSERT, MERGE, SELECT or UPDATE keyword (or for TT_GridQueryExec or TT_PartialResult, the SELECT keyword). TT_CommitDMLOnSuccess must follow the DELETE, INSERT, UPDATE keyword and for INSERT...SELECT, it must follow the SELECT keyword.
- Your hint contains misspellings or syntax errors. If you have hints that are within the same comment and some hints are correct syntactically and some hints are incorrect syntactically, TimesTen ignores the incorrect hints and accepts the correct hints.
- You use either the TT_JoinOrder or TT_Index hint and you do not supply a closing parenthesis, the remainder of the hint string is ignored.
For hints that conflict with each other, TimesTen uses the rightmost hint in the comment. For example, if the comment string is /*+TT_TblScan (0)...TT_TblScan (1) */, the rightmost hint, TT_TblScan(1), is used.
Statement level optimizer hints override conflicting transaction level optimizer hints. If you specify a transaction level optimizer hint that conflicts with a statement level optimizer hint, the statement level optimizer hint overrides the conflicting transaction level optimizer hint. For example, if you call ttOptSetFlag, and enable the Range flag and then you issue a SQL query and disable the statement level optimizer flag, TT_Range, TimesTen disables the range flag for the query. After the query is executed, the original range flag setting that was in place in the transaction before the query was executed remains in effect for the duration of the transaction. For more information, see Using Statement Level Optimizer Hints for a SELECT Query. The TT_GridQueryExec, TT_PartialResult, TT_CommitDMLOnSuccess, and TT_CountAsInt hints are not supported at the transaction level.
Do not use statement level optimizer hints in a subquery.
The TimesTen query optimizer does not recognize statement level optimizer hints for passthrough statements. TimesTen passes the SQL text for passthrough statements to the Oracle database and the SQL text is processed according to the SQL rules of the Oracle database. Passthrough statements are not supported in TimesTen Scaleout.

SQL Statements that Support Statement Level Optimizer Hints

You can specify statement level optimizer hints in SQL statements. Not all hints are supported in all statements. You must specify the hint within comment syntax and the comment syntax must immediately follow the SQL VERB. (For example, SELECT /*+ hint */...) Table 6-3 shows the correct placement of the statement level hint. It also indicates if a hint is not supported in the statement.

Table 6-3 Placement of Statement Level Hints in SQL Statements

SQL Statement	Placement of Hint
`CREATE` `TABLE... AS SELECT`	`CREATE TABLE...AS SELECT` [`hint`]... Do not use transaction level hints with the `CREATE` `TABLE...AS SELECT` statement. `TT_CommitDMLOnSuccess` is not supported in this statement.
`DELETE`	`DELETE` [`hint`]... The `TT_GridQueryExec` and `TT_PartialResult` hints are not supported in this statement.
`INSERT`	`INSERT` [`hint`]... The `TT_GridQueryExec` and `TT_PartialResult` hints are not supported in this statement.
`INSERT...SELECT`	`INSERT...SELECT` [`hint`]...
`MERGE`	`MERGE` [`hint`]... The `TT_GridQueryExec` and `TT_PartialResult` hints are not supported.
`SELECT`	`SELECT` [`hint`]... Do not specify a hint in a subquery. The `TT_CommitDMLOnSuccess` hint is not supported in this statement.
`SELECT...{UNION\|MINUS\| INTERSECT} SELECT...`	`SELECT` [`hint`] `{UNION\|MINUS\|INTERSECT}` `SELECT`... The `TT_CommitDMLOnSuccess` hint is not supported in this statement.
`UPDATE`	`UPDATE` [`hint`]... The `TT_GridQueryExec` and `TT_PartialResult` hints are not supported in this statement.

Understanding Hints

Use optimizer hints to influence the TimesTen query optimizer in determining the choice of the execution plan for your query.

TT_GridQueryExec, TT_PartialResult and TT_CommitDMLOnSuccess are supported at the connection and statement levels only. This section is not valid for these hints.

To view transaction level optimizer hints, execute the built-in procedure, ttOptSetFlag. For more information on the built-in procedure, ttOptGetFlag, see ttOptGetFlag in Oracle TimesTen In-Memory Database Reference.

Examples

For TT_CommitDMLOnSuccess examples, see TT_CommitDMLOnSuccess Optimizer Hint for information.

For TT_GridQueryExec and TT_PartialResult examples:

See TT_GridQueryExec in the Oracle TimesTen In-Memory Database Scaleout User's Guide.
See TT_PartialResult in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

The following examples illustrate usages of statement level and transaction level optimizer hints. The TimesTen optimizer is a cost based query optimizer and generates what it thinks is the most optimal execution plan for your statement. This plan differs from release to release. The plan is based on the indexes that exist on the referenced tables as well as the column and table statistics that are available. When you recompute statistics or change indexes, the TimesTen optimizer may change the execution plan based on the recomputed statistics and index changes. Because the execution plan may vary, these examples are included for demonstration purposes only. Examples include:

Using Statement Level Optimizer Hints for a SELECT Query

View the execution plan for a query. Then use statement level optimizer hints to influence the optimizer to choose a different execution plan. Consider the query:

Command> SELECT r.region_name, c.country_name
         FROM regions r, countries c
         WHERE r.region_id = c.region_id
         ORDER BY c.region_id;

Use the ttIsql EXPLAIN command to view the plan generated by the optimizer. Note:

The optimizer performs two range scans using table level locking for both scans.
The optimizer uses the MergeJoin operation to join the two tables.

Command> EXPLAIN SELECT r.region_name, c.country_name
         FROM regions r, countries c
         WHERE r.region_id = c.region_id
         ORDER BY c.region_id;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             COUNTRIES
  IXNAME:              COUNTR_REG_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
 
  STEP:                2
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             REGIONS
  IXNAME:              REGIONS
  INDEXED CONDITION:   R.REGION_ID >= C.REGION_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               1
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   C.REGION_ID = R.REGION_ID
  NOT INDEXED:         <NULL>

Now use statement level optimizer hints to direct the optimizer to perform the scans using row level locking and to use a NestedLoop operation to join the tables. Set autocommit to on to illustrate that the autocommit setting has no effect because statement level optimizer hints are scoped to the SQL statement.

Command> autocommit on;
Command> EXPLAIN SELECT /*+ TT_RowLock (1), TT_TblLock (0), TT_MergeJoin (0),
         TT_NestedLoop (1) */
         r.region_name, c.country_name
         FROM regions r, countries c
         WHERE r.region_id = c.region_id
         ORDER BY c.region_id;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           RowLkRangeScan
  TBLNAME:             REGIONS
  IXNAME:              REGIONS
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               3
  OPERATION:           RowLkRangeScan
  TBLNAME:             COUNTRIES
  IXNAME:              COUNTR_REG_FK
  INDEXED CONDITION:   C.REGION_ID = R.REGION_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               2
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                4
  LEVEL:               1
  OPERATION:           OrderBy
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Prepare the query again without statement level optimizer hints. The optimizer reverts back to the original execution plan because statement level optimizer hints are scoped to the SQL statement.

Command> EXPLAIN SELECT r.region_name, c.country_name
         FROM regions r, countries c
         WHERE r.region_id = c.region_id
         ORDER BY c.region_id;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             COUNTRIES
  IXNAME:              COUNTR_REG_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             REGIONS
  IXNAME:              REGIONS
  INDEXED CONDITION:   R.REGION_ID >= C.REGION_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               1
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   C.REGION_ID = R.REGION_ID
  NOT INDEXED:         <NULL>

Using On and Off Hinting

This example illustrates the importance of directing the optimizer to specifically enable or disable hints that perform a similar function. For example, the hash and range hints direct the optimizer to use either a hash or range access path for the table. In order to ensure the optimizer chooses the specific access path, enable one hint and disable all other related hints.

Create a table and create a hash index on the first column of the table and a range index on the second column.

Command> CREATE TABLE test (col1 NUMBER, col2 NUMBER);
Command> CREATE HASH INDEX h_index ON test (col1);
Command> CREATE INDEX hr_index ON test (col2);

Set autocommit to off and execute the built-in procedure, ttOptGetFlag, to review the current transaction level optimizer hint settings for the transaction. A setting of 1 means the flag is enabled.

Command> autocommit off;
Command> CALL ttOptGetFlag ('Hash');
< Hash, 1 >
1 row found.
Command> CALL ttOptGetFlag ('Scan');
< Scan, 1 >
1 row found.

Use the ttIsql EXPLAIN command to review the plan for a SELECT query using a WHERE clause and dynamic parameters. The optimizer uses a hash scan.

Command> EXPLAIN SELECT * FROM test WHERE col1 = ? and col2 = ?;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkHashScan
  TBLNAME:             TEST
  IXNAME:              H_INDEX
  INDEXED CONDITION:   TEST.COL1 = _QMARK_1
  NOT INDEXED:         TEST.COL2 = _QMARK_2

Use the statement level optimizer hint TT_Range to direct the optimizer to use a range scan. Note that the optimizer ignores the TT_Range hint and uses a hash scan because you did not direct the optimizer to disable the hash scan. Alter the statement and direct the optimizer to use a range scan and not use a hash scan. To accomplish this, enable the statement level optimizer hint TT_Range and disable the statement level optimizer hint TT_HashScan. The optimizer no longer ignores the TT_Range hint.

Command> EXPLAIN SELECT --+ TT_Range (1) Single line comment to set TT_Range
         * FROM TEST WHERE col1 = ? and col2 = ?;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkHashScan
  TBLNAME:             TEST
  IXNAME:              H_INDEX
  INDEXED CONDITION:   TEST.COL1 = _QMARK_1
  NOT INDEXED:         TEST.COL2 = _QMARK_2

Command> EXPLAIN SELECT /*+ TT_Range (1) TT_HashScan (0)
          Multiple line comment to enable TT_Range and disable TT_HashScan */
          * FROM TEST WHERE col1 = ? and col2 = ?;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkRangeScan
  TBLNAME:             TEST
  IXNAME:              HR_INDEX
  INDEXED CONDITION:   TEST.COL2 = _QMARK_2
  NOT INDEXED:         TEST.COL1 = _QMARK_1

Prepare the query again without using statement level optimizer hints and without issuing a commit or rollback. The optimizer uses the transaction level optimizer hints settings that were in effect before executing the query. The optimizer uses transaction level optimizer hints because statement level optimizer hints are scoped to the SQL statement.

Command> EXPLAIN SELECT * FROM TEST WHERE col1 = ? and col2 = ?;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkHashScan
  TBLNAME:             TEST
  IXNAME:              H_INDEX
  INDEXED CONDITION:   TEST.COL1 = _QMARK_1
  NOT INDEXED:         TEST.COL2 = _QMARK_2

Using TT_JoinOrder to Specify a Join Order

Use the statement level optimizer hint TT_JoinOrder to direct the optimizer to use a specific join order. First use a transaction level optimizer hint to direct the optimizer to use a specific join order for the transaction. Then use a statement level optimizer hint to direct the optimizer to change the join order for the statement only.

Command> CALL ttOptSetOrder ('e d j');
Command> EXPLAIN SELECT *
         FROM employees e, departments d, job_history j
         WHERE e.department_id = d.department_id
         AND e.hire_date = j.start_date;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMP_DEPT_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             DEPARTMENTS
  IXNAME:              DEPARTMENTS
  INDEXED CONDITION:   D.DEPARTMENT_ID >= E.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               2
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   E.DEPARTMENT_ID = D.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                4
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             JOB_HISTORY
  IXNAME:              JOB_HISTORY
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         E.HIRE_DATE = J.START_DATE
 
  STEP:                5
  LEVEL:               1
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Use the statement level optimizer hint, TT_JoinOrder, to direct the optimizer to override the transaction level join order optimizer hint for the SQL statement only.

Command> EXPLAIN SELECT --+ TT_JoinOrder (e j d)
          *
          FROM employees e, departments d, job_history j
          WHERE e.department_id = d.department_id
          AND e.hire_date = j.start_date;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMP_DEPT_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             JOB_HISTORY
  IXNAME:              JOB_HISTORY
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         E.HIRE_DATE = J.START_DATE
 
  STEP:                3
  LEVEL:               2
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                4
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             DEPARTMENTS
  IXNAME:              DEPARTMENTS
  INDEXED CONDITION:   D.DEPARTMENT_ID >= E.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                5
  LEVEL:               1
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   E.DEPARTMENT_ID = D.DEPARTMENT_ID
  NOT INDEXED:         <NULL>

Prepare the query again to verify that the join order that was in effect for the transaction remains in effect.

Command> EXPLAIN SELECT *
          FROM employees e, departments d, job_history j
          WHERE e.department_id = d.department_id
          AND e.hire_date = j.start_date;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMP_DEPT_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             DEPARTMENTS
  IXNAME:              DEPARTMENTS
  INDEXED CONDITION:   D.DEPARTMENT_ID >= E.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               2
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   E.DEPARTMENT_ID = D.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                4
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             JOB_HISTORY
  IXNAME:              JOB_HISTORY
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         E.HIRE_DATE = J.START_DATE
 
  STEP:                5
  LEVEL:               1
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Using the TT_INDEX Statement Level Optimizer Hint

Perform a query on the employees table that uses the index, emp_name_ix. Then use the statement level optimizer hint TT_INDEX to direct the optimizer not to use this index. First run the ttIsql command, indexes, to view the indexes for the employees table.

Command> indexes employees;
 
Indexes on table TESTUSER.EMPLOYEES:
  EMPLOYEES: unique range index on columns:
    EMPLOYEE_ID
    (referenced by foreign key index JHIST_EMP_FK on table TESTUSER.JOB_HISTORY)
  TTUNIQUE_0: unique range index on columns:
    EMAIL
  EMP_DEPT_FK: non-unique range index on columns:
    DEPARTMENT_ID
    (foreign key index references table TESTUSER.DEPARTMENTS(DEPARTMENT_ID))
  EMP_JOB_FK: non-unique range index on columns:
    JOB_ID
    (foreign key index references table TESTUSER.JOBS(JOB_ID))
  EMP_NAME_IX: non-unique range index on columns:
    LAST_NAME
    FIRST_NAME
  5 indexes found.
 
5 indexes found on 1 table.

Use the ttIsql command, EXPLAIN, to view the execution plan for a SELECT query on the employees table that uses a WHERE clause on the last_name column.

Command> EXPLAIN SELECT e.first_name
           FROM employees e
           WHERE e.last_name BETWEEN 'A' AND 'B';
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMP_NAME_IX
  INDEXED CONDITION:   E.LAST_NAME >= 'A' AND E.LAST_NAME <= 'B'
  NOT INDEXED:         <NULL>

Use the statement level optimizer hint, TT_INDEX, to direct the optimizer not to use the index, emp_name_ix.

Command> EXPLAIN SELECT --+ TT_INDEX (E,EMP_NAME_IX,0)
           e.first_name
           FROM employees e
           WHERE e.last_name BETWEEN 'A' AND 'B';
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMPLOYEES
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         E.LAST_NAME <= 'B' AND E.LAST_NAME >= 'A'