Problem solve Get help with specific problems with your technologies, process and projects.

How does an INSERT/UPDATE statement behave when executed compared to a SELECT statement?

An Oracle user asks expert Brian Peasland: How does an INSERT/UPDATE statement behave when executed compared to a SELECT statement?

How does an INSERT/UPDATE statement behave when executed compared to a SELECT statement?

I know a SELECT statement's result is first searched in the SGA for the result set, and if not found the server fetches the result set and returns the rows upon saving a copy of the result set in the buffer for further such SELECT statements.

I vaguely remember reading that an INSERT statement first updates the physical data blocks on disk and retains a dirty buffer in SGA and stores the data block id in the undo segment for rollback. The same goes with UPDATE statement too, updated data goes to the disk(a dirty buffer is retained in SGA) while the old data is sent to the undo segments, again to address rollback operation....Is this correct? Could you please throw some light on this?

Your reading of the behavior of the INSERT statement is incorrect. DML statements, (INSERT/UPDATE/DELETE) never modify data directly on disk. They only modify blocks in the buffer cache. Undo information will be written to blocks belonging to the Undo tablespace, but those blocks are also in the buffer cache. Information to redo the transaction is written to the log buffer…again in memory. The log buffer also contains information to redo the undo blocks as well.

The only time you know for sure that blocks on disk are being written is when the user issues a COMMIT. A commit is not complete until the relevant information is written from the log buffer to the online redo logs. In some cases, the database data files are not changed until some time after the commit. If Oracle crashes, it has everything it needs in the online redo logs to restore the committed transaction.

While all of this is going on, the checkpoint process (CKPT) is responsible for making sure that changes in the buffer cache get written to disk. CKPT will cause the database writer (DBWn) to write changes to disk. But these changes can be a mixture of committed and uncommitted data.

This was last published in June 2008

Dig Deeper on Oracle database administration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.