How Oracle SELECT Works – A House Analogy

Rameshdba.com

Imagine your Oracle database like a big house (villa), and you’re a guest who wants to get a cup of tea from the kitchen.

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


Flow of a SELECT Statement – Like Asking for Tea

You Ring the Doorbell

You (user) arrive at the house

Security guard (Listener) verifies you and allows entry

Butler Comes to Attend

Server Process assigned to you, takes your order

(“I want tea”) → SELECT * FROM TEA;

Butler Checks Recipe Book

Looks into the Library Cache (House Library)

If recipe (SQL plan) exists, reuse it (soft parse)

If not, note down new recipe (hard parse)

Butler Checks Inventory List

Verifies if tea leaves, milk, sugar are in stock

Refers Data Dictionary Cache (Inventory List)

If not, goes to basement and updates list (Disk I/O)

Pick Ingredients from Kitchen

Looks for ingredients in Buffer Cache (Kitchen Store)

If not available, fetches from Basement (Disk) and stocks it in kitchen

Chef Prepares the Tea

PGA (Chef’s Workspace) used for making tea (sorting, filtering data)

Serve the Tea

Once ready, Server Process brings the tea to you (user)















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