Oracle UPDATE Statement Execution Flow with example of Hotel Room Service

Rameshdba.com

Just imagine one guest from Room number 101 calls Reception and asks to change their meal order.

Let’s see how the journey works step by step:




 User issues the UPDATE statement

Oracle: Client sends an UPDATE SQL query.
Hotel: Guest from Room 101 calls Reception and asks to change their meal order.

Syntax Check

Oracle: Server checks SQL statement for correct spelling, structure, and semicolons.
Hotel: The receptionist checks if the request is properly mentioned, including room number, food item name, etc.

Semantic Check

Oracle: Checks if the table and column names exist and the user has permission.
Hotel: Confirms if the room exists and the guest can change their order.

Object Resolution

Oracle: Verifies which table the data belongs to and identifies related objects.
Hotel: Reception finds which kitchen team handles this room’s service.

 Optimization

Oracle: Chooses the best way (execution plan) to perform the update — table scan or index.
Hotel: Decides the fastest waiter and shortest route to the kitchen to get this done.

 Row Source Generation

Oracle: Builds a plan on how to fetch and update the rows.
Hotel: Waiter gets instructions on where to go and what to do.

Server Process → PGA

Oracle: Server process uses memory (PGA) to track the execution state.
Hotel: Waiter uses a notepad (PGA) to write the new order details.

Data Fetch (Buffer Cache or Disk)

Oracle: Retrieves current data from Buffer Cache or datafiles if not cached.
Hotel: Waiter checks current order in the kitchen records (Buffer or Log book).

Apply Update (in Buffer Cache)

Oracle: Updates the data in memory (Buffer Cache) — not yet permanent.
Hotel: Waiter updates the kitchen slip with new order — not yet served.

Write to Redo Log

Oracle: Writes the update operation to Redo Log for recovery purpose.
Hotel: Logs this order change in the hotel register in case of issues later.

Commit / Rollback

Oracle: If COMMIT is issued, the change becomes permanent.
Hotel: Guest confirms the change (COMMIT) or cancels it (ROLLBACK).

 Control Returns to User

Oracle: Oracle sends a success message to the client.
Hotel: The receptionist calls the guest and confirms that the new order is updated.


FInally!!

Data updated successfully in the database 

Guest's order updated in the kitchen
















0 Comments

DELETE Statement in ORACLE DB— Step by Step Internal Flow with classroom example.

by
Imagine your Oracle database like a school, and you’re Teacher decides to remove a student (row) from the classroom(table). Let’s see how the journey works step by step: 1)Classroom = Oracle Table 2)Students = Rows (Records) 3)Class Teacher = Oracle Server Process 4)Attendance Register = Data Dictionary User fires DELETE statement: DELETE FROM students WHERE grade = 'D'; Internal Processing Steps: Client Process Sends DELETE Statement to Server Process The teacher receives a request to remove a student from the classroom. Syntax Check (Parsing) The teacher checks if the request is grammatically correct — proper command, keywords, and a semicolon. Semantic Check Teacher verifies if the classroom exists and whether the condition (like grade = 'D') makes sense. Object Resolution Teacher confirms that the ‘students’ classroom (table) exists in the school records (data dictionary). Optimization Decides the fastest way to find those students — by roll number, grade, or al...
Image