Interview Question 15 : How INSERT and DELETE Statements Are Executed in Oracle ?

 

1️⃣ How an INSERT Statement Is Executed in Oracle

Example

INSERT INTO emp (empno, ename, sal)

VALUES (101, 'ANURAG', 50000);

---

Step‑by‑Step Execution Flow

1️⃣ Parsing Phase

• Syntax and object validation
• Privilege check (INSERT on table)
• Hard or soft parse
• Execution plan preparation

✅ Uses Shared Pool (SGA)

---

2️⃣ Space Identification

Oracle determines:

• Which data block has free space
• Uses freelists / bitmap / ASSM
• No row locking needed (row doesn’t exist yet)

---

3️⃣ Undo Generation (Yes, Even for INSERT)

Oracle creates undo for the insert:

• Contains information to delete the inserted row if rollback happens

📌 Undo is mandatory for:

• Rollback
• Read consistency
• Flashback

---

4️⃣ Redo Generation

Redo is generated for:

• Undo records
• Inserted row data

Redo is placed in:

• Redo Log Buffer
• Later flushed to redo logs by LGWR

---

5️⃣ Data Written to Buffer Cache

• New row inserted in memory
• Data block marked DIRTY
• Not yet written to disk

---

6️⃣ Commit or Rollback

✅ COMMIT

• LGWR flushes redo
• Transaction becomes permanent
• Space becomes visible to others

❌ ROLLBACK

• Undo used to remove inserted row
• Block returns to original state

---

Key INSERT Characteristics

Aspect	Behavior
Locks	Only table TM lock (RX)
Row locks	❌ None
Undo generated	✅ Yes
Redo generated	✅ Yes
Disk write	❌ Deferred

---

2️⃣ How a DELETE Statement Is Executed in Oracle

Example

DELETE FROM emp WHERE empno = 100;

---

Step‑by‑Step Execution Flow

1️⃣ Parsing & Optimization

Same as UPDATE:

• Parse SQL
• Optimizer selects index or scan

---

2️⃣ Row Identification

• Oracle finds target ROWIDs
• Rows are identified, not yet removed

---

3️⃣ Acquire Locks

• Row‑level exclusive (RX) locks on rows
• TM lock on table

✅ Prevents concurrent UPDATE/DELETE on same rows
✅ SELECTs are NOT blocked

---

4️⃣ Undo Generation (BEFORE DELETE)

Undo stores:

• Complete old row image
• Required for:
  • Rollback
  • Consistent reads
  • Flashback

📌 DELETE generates a lot of undo

---

5️⃣ Redo Generation

Redo recorded for:

• Undo
• Delete operation

---

6️⃣ Row Is Marked Deleted (Not Removed)

Important concept:

DELETE does not physically remove the row immediately

Instead:

• Row is logically removed
• Space reusable later
• Cleaned by subsequent inserts or segment shrink

✅ This enables rollback

---

7️⃣ Commit or Rollback

✅ COMMIT

• Redo flushed
• Locks released
• Row permanently deleted

❌ ROLLBACK

• Row restored completely from undo

---

Key DELETE Characteristics

Aspect	Behavior
Row locks	✅ Yes
Undo volume	🔥 High
Redo	✅ Yes
Space freed	❌ Logical first
SELECT blocked	❌ No

---

3️⃣ UPDATE vs INSERT vs DELETE (Internal Comparison)

Feature	INSERT	UPDATE	DELETE
Row exists before	❌	✅	✅
Row‑level lock	❌	✅	✅
Undo usage	Medium	Medium	High
Redo generation	✅	✅	✅
Affects indexes	✅	✅	✅
Can rollback	✅	✅	✅

---

4️⃣ Why DELETE Is Slower Than TRUNCATE (Interview Favorite)

DELETE	TRUNCATE
DML	DDL
Row‑by‑row	Metadata only
Undo generated	✅
Redo	✅
Rollback	✅
Triggers fire	✅

🔑 TRUNCATE is fast because it doesn’t generate undo for each row

---

5️⃣ RAC‑Specific Behavior (Bonus DBA Knowledge)

In Oracle RAC:

• Row locks are tracked globally via GES
• Cache Fusion ensures consistency across instances
• UPDATE/DELETE on same row from two instances:
  • One waits for the other

✅ No data corruption
✅ Distributed locking handled automatically

---

6️⃣ Common Interview Traps (Correct Answers)

Q: Does DELETE free disk space immediately?

❌ No
✅ Row is logically removed; space reused later

---

Q: Does INSERT generate undo?

✅ Yes — for rollback

---

Q: Which DML generates most undo?

✅ DELETE

---

Q: Can SELECT run while DELETE is happening?

✅ Yes (read consistency via undo)

---

Q: When is data written to disk?

✅ Later by DBWR, not during DML

---

7️⃣ One‑Line Interview Answers ⭐

• INSERT:

  Adds new rows by generating undo and redo and modifying memory blocks, making changes permanent only after commit.

• DELETE:

  Logically removes rows by locking them, generating undo and redo, and permanently deleting them upon commit.

• UPDATE (recap):

  Modifies existing rows by generating undo and redo while maintaining read consistency.

---

✅ Final DBA Mental Model

DML Statement
   ↓
Parse & Optimize
   ↓
Row identification
   ↓
Locks
   ↓
Undo
   ↓
Redo
   ↓
Buffer Cache change
   ↓
COMMIT / ROLLBACK