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-3 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-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 1.
Table 6-3 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-4 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:
-
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:
Example
This example illustrates the use of the TT_CommitDMLOnSuccess hint at the statement level. When setting the hint to 1, TimesTen inserts a row into the database despite a rollback being issued.
Command> CREATE TABLE TV(c1 NUMBER);
Command> SET AUTOCOMMIT 0;
Command> INSERT INTO t VALUES (0);
1 row inserted.
Command> ROLLBACK;
Command> SELECT * FROM t;
0 rows found.
Command> INSERT /*+ TT_CommitDMLOnSuccess(1) */ INTO t VALUES (0);
1 row inserted.
Command> ROLLBACK;
Command> SELECT * FROM t;
< 0 >
1 row found.
Command> INSERT INTO t VALUES (1);
1 row inserted.
Command> ROLLBACK;
Command> SELECT * FROM t;
< 0 >
1 row found.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.