Avoiding logging in DB2 and Oracle
Avoiding logging in DB2 and Oracle
For performance reasons, especially when dealing with large volumes of data which is easily recoverable, one may want to suppress the generation of logs (redo logs). This is possible both in
Oracle and in DB2 but there are some interesting differences.
ORACLE
In Oracle, it is possible to avoid generating logs for inserts by specifying the /*+ APPEND */ hint. When such an insert is done, the table cannot be accessed unless a commit is issued. However, it is only INSERTS for which the log generation can be avoided; UPDATES, DELETES and index creation and maintenance will ALWAYS be logged and there is no way around it.
DB2
In DB2, it is possible to avoid generating logs for INSERTS, UPDATES, DELETES and INDEX maintenance by using the NOT LOGGED INITIALLY option on the CREATE TABLE (or ALTER TABLE) and then performing the DML operation in the same unit of work. This can be especially useful in data warehouses but, as with everything, it is important to understand what happens under the covers to use the feature effectively: with NOT LOGGED specified, an insert will create pages and fill them up with data in the bufferpool and the same will happen for indexes. When a COMMIT is issued, all of these pages will be flushed to disk before the commit returns (I guess Oracle must be doing something similar). It is possible, however, that some of the pages may have been flushed to disk even before the commit is issued and that raises a couple of interesting questions:
1. What if I specify NOT LOGGED and then issue a DELETE FROM TABLE and the database crashes before I COMMIT or ROLLBACK?
2. Better still, what if I ACTIVATE NOT LOGGED and then DELETE FROM TABLE and subsequently change my mind and ROLLBACK?
Tough luck! You will receive an error saying the table can't be accessed:
SQL1477N For table "ADMINISTRATOR.AB" an object "516" in table space "3"cannot be accessed. SQLSTATE=55019
Moral of the story:
NOT LOGGED can be very useful, especially in data warehouses but test it out and understand all implications before activating it.
For performance reasons, especially when dealing with large volumes of data which is easily recoverable, one may want to suppress the generation of logs (redo logs). This is possible both in
Oracle and in DB2 but there are some interesting differences.
ORACLE
In Oracle, it is possible to avoid generating logs for inserts by specifying the /*+ APPEND */ hint. When such an insert is done, the table cannot be accessed unless a commit is issued. However, it is only INSERTS for which the log generation can be avoided; UPDATES, DELETES and index creation and maintenance will ALWAYS be logged and there is no way around it.
DB2
In DB2, it is possible to avoid generating logs for INSERTS, UPDATES, DELETES and INDEX maintenance by using the NOT LOGGED INITIALLY option on the CREATE TABLE (or ALTER TABLE) and then performing the DML operation in the same unit of work. This can be especially useful in data warehouses but, as with everything, it is important to understand what happens under the covers to use the feature effectively: with NOT LOGGED specified, an insert will create pages and fill them up with data in the bufferpool and the same will happen for indexes. When a COMMIT is issued, all of these pages will be flushed to disk before the commit returns (I guess Oracle must be doing something similar). It is possible, however, that some of the pages may have been flushed to disk even before the commit is issued and that raises a couple of interesting questions:
1. What if I specify NOT LOGGED and then issue a DELETE FROM TABLE and the database crashes before I COMMIT or ROLLBACK?
2. Better still, what if I ACTIVATE NOT LOGGED and then DELETE FROM TABLE and subsequently change my mind and ROLLBACK?
Tough luck! You will receive an error saying the table can't be accessed:
SQL1477N For table "ADMINISTRATOR.AB" an object "516" in table space "3"cannot be accessed. SQLSTATE=55019
Moral of the story:
NOT LOGGED can be very useful, especially in data warehouses but test it out and understand all implications before activating it.
posted by Ram at
1:24 PM
0 Comments:
Post a Comment
<< Home