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

Insert statement process explanation

Insert into table_name values (1);
What series of actions are performed to execute this statement? I know how an insert is performed, but what memory structures are referred and allocated?

As you may have guessed, there is quite a bit going on behind the scenes to perform something as simple as an insert statement. Oracle has to do a lot of checking before the statement can even begin to be processed. Does the table exist? Do you have privileges to perform this operation against that table? Oracle checks these things by querying the data dictionary. Any queries done to satisfy your original SQL statement are called "recursive SQL".

To perform an insert, the database has to do something like this:

  1. Does the object exist? Query OBJ$?
  2. Does the user have privileges? Query ACCESS$
  3. Find a block in the table to insert the record.
    Query the freelist(s) in the segment header for a free block.
  4. Load the block in the buffer cache.
  5. Insert the information in the block.
  6. Record the information in the online redo log.
  7. Record the information in the rollback segment.
  8. Record the transaction as committed or rolled back.

There is even more than this going on behind the scenes. The best way to understand how this all works is to read the Oracle Concepts Guide.

For More Information

Dig Deeper on Oracle database design and architecture