Logs

For some DBMSs, logging is an optional step. Nevertheless it's very rare that database administrators will decide to turn logging off for performance reasons. So it's safe to assume that logging will happen when you do a data change.

When will logging happen? Before data changes become permanent. The reasoning is that, if a database gets smashed, then one can recover from the log. If the data change were written before the log entry though, then the last entry in the log would be absent. So the DBMS always logs first.

What goes in the log? A copy of the new data, or a copy of the old data, or both. This point requires emphasis. You might think that the only necessary data in the log is the data-change statement itselffor example, the DBMS could just write this SQL statement into the log file:

 INSERT INTO Table1 (column1)    VALUES (1) 

Well, it couldfor instance, Oracle does just that (Oracle calls the SQL-statement log a journal ). However, Oracle also writes a complete "before image" data copy into the log file, along with a complete "before image" index page if a data change also changes an index. And so will most other DBMSs. ^[1]

^[1] Some DBMSs also allow "archive logs" and "audit logs" but the phenomenon is not universal.

Wow, isn't that expensive? Not as much as it appears. In the first place, the writing is sequential and therefore quite fast. In the second place, the log writing can be a parallel operation on a different disk drive from the main file, and the log file can be recycled so there are no allocation problems. In the third place, logging is cheap insurance.

The main thing you should know about logging is that it's one of the reasons your data-change statements are slow. The easiest way to speed them up is to turn logging off for a while. You can ask your DBA to do that in a few situations:

• During a bulk INSERT or "load" because you have a copy of what you're inserting so you can repeat the operation if there's a failure

• For index updates because you can derive an index's keys from the original records if there's a failure

• During operations that copy from one database object to another such as " INSERT ... SELECT " or CREATE INDEX or ALTER statements

The Bottom Line: Logs

Assume logging will happen before data changes become permanent.

Logging is one of the reasons that data-change statements are slow. Speed them up by turning logging off (a) during a bulk INSERT, (b) for index updates/creations, and (c) during operations that copy from one database object to another. Don't turn logging off at any other time.