Optimizer Hints Supported in TimesTen Scaleout Only
These optimizer hints are only supported in TimesTen Scaleout. They are valid at the statement and at the connection levels.
See OptimizerHint in the Oracle TimesTen In-Memory Database Reference for information on hints at the connection level and "Statement Level Optimizer Hints" in this book for information on statement level optimizer hints.
TT_GridQueryExec Optimizer Hint
The TT_GridQueryExec optimizer hint enables you to specify whether the query should return data from the local element or from all elements, including the elements in a replica set when K-safety is set to 2.
If you do not specify this hint, the query is executed in one logical data space. It is neither local nor global. This means that exactly one full copy of the data is used to compute the query. Use this hint in cases where obtaining some result is more important than obtaining the correct result (for example, where one or more replica sets are unavailable). Valid options for this hint are LOCAL and GLOBAL.
For more information, see:
-
TT_GridQueryExec in the Oracle TimesTen In-Memory Database Scaleout User's Guide for information on using this hint.
-
OptimizerHint in the Oracle TimesTen In-Memory Database Reference for information on using this hint at the connection level.
-
"Statement Level Optimizer Hints" for information on using this hint at the statement level.
This example illustrates how to use the TT_GridQueryExec(GLOBAL) hint on the dual table to determine the ids of all elements, replica sets, and dataspaces.
Command> SELECT /*+TT_GridQueryExec(GLOBAL)*/ elementId#, replicasetId#,
dataspaceId# FROM dual ORDER BY elementId#,replicasetId#,dataspaceId#;
ELEMENTID#, REPLICASETID#, DATASPACEID#
< 1, 1, 1 >
< 2, 1, 2 >
< 3, 2, 1 >
< 4, 2, 2 >
< 5, 3, 1 >
< 6, 3, 2 >
6 rows found.
See TT_GridQueryExec in the Oracle TimesTen In-Memory Database Scaleout User's Guide for more examples.
TT_PartialResult Optimizer Hint
The TT_PartialResult optimizer hint enables you to specify whether the query should return partial results if some data is not available.
Use TT_PartialResult(1) to direct the query to return partial results if all elements in a replica set are not available.
Use TT_PartialResult(0) to direct the query to return an 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.
The default is TT_PartialResult(0).
For more information, see:
-
TT_PartialResult in the Oracle TimesTen In-Memory Database Scaleout User's Guide for information on using this hint and for examples.
-
OptimizerHint in the Oracle TimesTen In-Memory Database Reference for information on using this hint at the connection level.
-
"Statement Level Optimizer Hints" for information on using this hint at the statement level.
TT_CommitDMLOnSuccess Optimizer Hint
Use the TT_CommitDMLOnSuccess hit to enable or disable a commit operation as part of DML execution.
-
At the statement level,
TT_CommitDMLOnSuccessis used in a DML statement (DELETE,INSERT,INSERT... SELECT, andUPDATE) to enable or disable the commit behavior of the transaction when the DML operation is executed. For theINSERT...SELECTstatement, specifyTT_CommitDMLOnSuccessafter theSELECTkeyword.TT_CommitDMLOnSuccessis valid in DML operations only. It is not valid for queries or DDL operations and, if specified in a non-DML statement, is ignored and no error is returned. See Statement Level Optimizer Hints for information on the syntax and semantics. -
At the connection level,
TT_CommitDMLOnSuccessis also used to enable or disable the commit behavior of the transaction when a DML operation is executed. However, you specifyTT_CommitDMLOnSuccessas a parameter to theOptimizerHintconnection attribute. See OptimizerHint in the Oracle TimesTen In-Memory Database Reference for information on usingTT_CommitDMLOnSuccessat the connection level.
At both levels, valid options are 0 and 1. If you do not specify TT_CommitDMLOnSuccess, there are no changes to the normal commit behavior. The order of precedence is statement level followed by connection level.
The TT_CommitDMLOnSuccess commit behavior at the statement level is:
-
TT_CommitDMLOnSuccess(1)commits the current transaction if the DML statement in which the hint is specified is executed successfully. If there are open cursors at commit time, all cursors are closed and the transaction is committed. If the statement with this hint fails, the transaction is not committed. -
TT_CommitDMLOnSuccess(0)disables the commit of the current transaction if the DML statement in which the hint is specified is executed successfully.
Table 6-4 shows the commit behavior when not setting TT_CommitDMLOnSuccess as well as setting TT_CommitDMLOnSuccess to 0 and 1 at the statement and connection levels. The table shows the commit behavior when autocommit is set to 0.
Table 6-5 shows the commit behavior when not setting TT_CommitDMLOnSuccess as well as setting TT_CommitDMLOnSuccess to 0 and 1 at the statement and connection levels. The table shows the commit behavior when autocommit is set to 1.
Table 6-4 TT_CommitDMLOnSuccess Commit Behavior: Autocommit 0
| Blank | Not Set at Connection Level | Set to 0 at Connection Level | Set to 1 at Connection Level |
|---|---|---|---|
|
Not set at statement level |
|
|
|
|
Set to 0 at statement level |
|
|
|
|
Set to 1 at statement level |
|
|
|
Table 6-5 TT_CommitDMLOnSuccess Commit Behavior: Autocommit 1
| Blank | Not Set at Connection Level | Set to 0 at Connection Level | Set to 1 at Connection Level |
|---|---|---|---|
|
Not Set at Statement Level |
|
|
|
|
Set to 0 at Statement Level |
|
|
|
|
Set to 1 at Statement Level |
|
|
|
For more information, see:
-
Using the TT_CommitDMLOnSuccess Hint in the Oracle TimesTen In-Memory Database Scaleout User's Guide for additional information.
-
OptimizerHint in the Oracle TimesTen In-Memory Database Reference for information on using
TT_CommitDMLOnSuccessat the connection level. -
Statement Level Optimizer Hints for information on the syntax for
TT_CommitDMLOnSuccessat the statement level.
These examples illustrate the use of the TT_CommitDMLOnSuccess optimizer hint:
Setting TT_CommitDMLOnSuccess to 1
This example first creates the mytable table. It then sets autocommit to 0 and inserts a row into the mytable table. A second connection (conn2) connects to the database and issues a SELECT query against the mytable table. The query returns 0 rows. The ttIsql use command returns the application to the first connection (database1) and issues a second INSERT operation, setting TT_CommitDMLOnSuccess to 1 at the statement level. A second ttIsql use command returns the application to the conn2 connection. A SELECT query shows two rows have been inserted into the mytable table. This example illustrates that issuing TT_CommitDMLOnSuccess(1) commits the transaction after the successful execution of the second INSERT operation (which set the hint).
Command> CREATE TABLE mytable (col1 TT_INTEGER, col2 VARCHAR2(4000)); Command> autocommit 0; Command> INSERT INTO mytable VALUES (10, 'ABC'); 1 row inserted.
Establish a second connection (conn2)
Command> connect as conn2; Using the connection string of connection database1 to connect... ... (Default setting AutoCommit=1)
Issue a SELECT query and expect 0 rows due to autocommit set to 0.
conn2: Command> SELECT * FROM mytable; 0 rows found.
Return to the first connection (database1) and issue an INSERT operation with TT_CommitDMLOnSuccess set to 1.
conn2: Command> use database1;
database1: Command> INSERT /*+TT_CommitDMLOnSuccess(1)*/
INTO mytable VALUES (10, 'ABC');
1 row inserted.
Return to the second connection (conn2) and issue a SELECT query. Expect 2 rows (due to the two INSERT statements. (The transaction is committed due to the TT_CommitDMLOnSuccess statement level hint set to 1 and the successful execution of the two INSERT operations.)
database1: Command> use conn2 conn2: Command> SELECT * FROM mytable; < 10, ABC > < 10, ABC > 2 rows found.
Using TT_CommitDMLOnSuccess at Connection Level
This example first creates the mytable table. It then uses PL/SQL to insert 1000 rows into the table. There is a second connection to the database (conn2) and this connection connects with TT_CommitDMLOnSuccess set to 1 at the connection level. Various operations are performed to illustrate the behavior of TT_CommitDMLOnSuccess at both the statement and connection levels.
Command> CREATE TABLE mytable (col1 TT_INTEGER NOT NULL PRIMARY KEY,
col2 VARCHAR2 (4000));
Command> BEGIN
FOR i in 1..1000
LOOP
INSERT INTO mytable VALUES (i,i);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Establish a second connection (conn2) and connect setting TT_CommitDMLOnSuccess at the connection level to 1.
Command> CONNECT adding "OptimizerHint=TT_CommitDMLOnSuccess(1)" as conn2; Connection successful: ...
Set autocommit to 0 and issue a DELETE operation.
conn2: Command> autocommit 0; conn2: Command> DELETE FROM mytable WHERE col1=1000; 1 row deleted.
Return to the original connection (database1) and issue a SELECT query to see if the DELETE operation was committed. The operation was committed due to the TT_CommitDMLOnSuccess setting of 1 at the connection level.
conn2: Command> use database1; database1: Command> SELECT * FROM mytable WHERE col1=1000; 0 rows found.
Return to the second connection (conn2) and issue an INSERT operation. Then return to the original connection (database1). The transaction containing the INSERT operation was committed.
database1: Command> use conn2; conn2: Command> INSERT INTO mytable VALUES (1000,1000); 1 row inserted. conn2: Command> use database1 database1: Command> SELECT * FROM mytable WHERE col1=1000; < 1000, 1000 > 1 row found.
Return to the second connection (conn2) and issue a DELETE operation, followed by an INSERT operation, and then a second INSERT operation where TT_CommitDMLOnSuccess is set to 0 at the statement level (the second INSERT).
database1: Command> use conn2;
conn2: Command> DELETE FROM mytable WHERE col1=1000;
1 row deleted.
conn2: Command> INSERT INTO mytable VALUES (1001,1001);
1 row inserted.
conn2: Command> INSERT /*+TT_CommitDMLOnSuccess(0)*/ INTO mytable
VALUES (1002,1002);
1 row inserted.
Issue a SELECT query and notice the results of the query. The one DELETE operation and the two INSERT operations were successful.
conn2: Command> SELECT * FROM mytable where col1 >= 1000; < 1001, 1001 > < 1002, 1002 > 2 rows found.
Return to the original connection (database1) and issue the same SELECT query. Observe that the one DELETE statement and the first INSERT operation were committed. This is due to the TT_CommitDMLOnSuccess setting of 1 at the connection level. The second INSERT statement was not committed due to the TT_CommitDMLOnSuccess setting of 0 for this second INSERT statement.
conn2: Command> use database1; database1: Command> SELECT * FROM mytable where col1 >= 1000; < 1001, 1001 > 1 row found.
Return to the second connection (conn2) and issue a third INSERT operation. Then issue a SELECT query and observe the results.
database1: Command> use conn2; conn2: Command> INSERT INTO mytable VALUES (1003,1003); 1 row inserted. conn2: Command> SELECT * FROM mytable where col1 >= 1000 ORDER BY col1; < 1001, 1001 > < 1002, 1002 > < 1003, 1003 > 3 rows found.
Return to the original connection (database1) and issue the same SELECT query. Note the results are the same as in the conn2 connection. The transaction is committed due to the TT_CommitDMLOnSuccess setting of 1 at the connection level and the successful execution of the second and third INSERT operations.
conn2: Command> use database1 database1: Command> SELECT * FROM mytable where col1 >= 1000 ORDER BY col1; < 1001, 1001 > < 1002, 1002 > < 1003, 1003 > 3 rows found.