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

• What do you think about this answer? E-mail us at [email protected] with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.