...
When there are many records that need to be changed in one table
| Code Block |
|---|
| title | For Autocommit 모드의 경우mode |
|---|
| language | sql |
|---|
|
iSQL> AUTOCOMMIT ON;
iSQL> UPDATE T1 SET C1=100 LIMIT 1000; -- Reduce the UPDATE target range with the limit statement and repeat the UPDATE statement.
|
| Code Block |
|---|
| title | For Non Autocommit 모드의 경우mode |
|---|
| language | sql |
|---|
|
iSQL> AUTOCOMMIT OFF;
iSQL> UPDATE T1 SET C1=100 LIMIT 1000; -- Reduce the UPDATE target range with the limit statement and repeat the UPDATE statement.
iSQL> COMMIT; -- The commit cycle is also short.
|
...
This is how to estimate the amount of transaction log to be used by a particular transaction to change TRX_UPDATE_MAX_LOGSIZE.
| Session 1-UPDATE statement execution | Session 2-Query Performance View |
|---|
| Code Block |
|---|
| iSQL> AUTOCOMMIT OFF;
iSQL> UPDATE ORDER_LINE SET OL_DELIVERY_D = SYSDATE LIMIT 1; -- Give and execute limit 1
|
| |
| | Among the results of executing the query below, UPDATE_SIZE * 'Number of records to be updated' is the size of the transaction log that will be created in the transaction. | Code Block |
|---|
| iSQL> SELECT ST.SESSION_ID SESSION_ID, TX.ID TX_ID
, TX.UPDATE_SIZE
, SUBSTR(ST.QUERY, 1, 100) QUERY
FROM V$TRANSACTION TX, V$STATEMENT ST
WHERE TX.ID = ST.TX_ID AND TX.UPDATE_SIZE <> 0 ; |
|
| Code Block |
|---|
| iSQL> ROLLBACK; |
| |
...