Oracle DELETE Statement: Behind the Scenes

When you execute DELETE FROM employees WHERE employee_id = 101;, Oracle performs a complex series of operations involving row location, undo generation, lock management, and cascade operations. Understanding the DELETE process is essential for database administrators, especially for data management and performance optimization.

In this guide, we’ll explore the complete execution flow of a DELETE statement in Oracle Database.

The Complete DELETE Statement Execution Flow

┌─────────────────────────────────────────────────────────────────┐
│                USER EXECUTES DELETE STATEMENT                   │
│        DELETE FROM employees WHERE employee_id = 101;           │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 1: SYNTAX CHECK                          │
│  • Validate DELETE syntax                                       │
│  • Check FROM clause                                            │
│  • Verify WHERE clause structure                                │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                    [Valid Syntax?]
                           │
                    Yes ───┼─── No → Error: ORA-00936
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 2: SEMANTIC CHECK                        │
│  • Does table exist?                                            │
│  • Does user have DELETE privilege?                             │
│  • Check for dependencies (foreign keys)                        │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                    [Objects Valid?]
                           │
                    Yes ───┼─── No → Error: ORA-00942/ORA-01031
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 3: SHARED POOL CHECK                     │
│  • Generate SQL hash                                            │
│  • Search Library Cache                                         │
│  • Hard parse vs Soft parse                                     │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 4: LOCATE ROWS (WHERE Clause)            │
│  • Execute WHERE clause predicate                               │
│  • Use index if available                                       │
│  • Identify ROWIDs of rows to delete                            │
│  • Full Table Scan or Index Scan                                │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                    [Rows Found?]
                           │
                    Yes ───┼─── No → 0 rows deleted
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 5: CHECK FOREIGN KEY CONSTRAINTS         │
│  • Check if child records exist                                 │
│  • Verify referential integrity                                 │
│  • Check CASCADE DELETE rules                                   │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                    [FK Valid?]
                           │
                    Yes ───┼─── No → Error: ORA-02292
                           │         (Child records exist)
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 6: ACQUIRE LOCKS                         │
│  • Get TX (Transaction) lock on each row                        │
│  • Lock table in Row Exclusive (RX) mode                        |
│  • Wait if rows locked by another session                       │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 7: CREATE UNDO RECORDS                   │
│  • Store COMPLETE row data in undo                              │
│  • Largest undo generation of all DML                           │
│  • Save entire row for potential ROLLBACK                       │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 8: GENERATE REDO RECORDS                 │
│  • Create redo for delete operation                             │
│  • Log row deletion information                                 │
│  • Write to Redo Log Buffer                                     │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 9: MARK ROW AS DELETED                   │
│  • Don't physically remove row immediately                      │
│  • Mark row as deleted in block header                          │
│  • Row space not immediately reclaimed                          │
│  • Physical cleanup happens later (PCTFREE)                     │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 10: UPDATE ALL INDEXES                   │
│  • Remove entries from ALL indexes                              │
│  • Update every index on the table                              │
│  • Most expensive part of DELETE                                │
│  • Generate redo for index changes                              │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 11: CASCADE DELETE (if defined)          │
│  • Delete child records in referenced tables                    │
│  • Follow CASCADE DELETE rules                                  │
│  • Recursive deletion process                                   │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 12: FIRE TRIGGERS (if any)               │
│  • Execute BEFORE DELETE triggers                               │
│  • Can prevent deletion                                         │
│  • Execute AFTER DELETE triggers                                │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 13: RETURN SUCCESS                       │
│  • Return "N rows deleted" message                              │
│  • Rows marked deleted in memory                                │
│  • Visible only to current session                              │
│  • Locks held until COMMIT/ROLLBACK                             │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
                ┌──────────┴──────────┐
                │                     │
             COMMIT              ROLLBACK
                │                     │
                ▼                     ▼
    ┌──────────────────┐  ┌──────────────────────┐
    │ Make Permanent   │  │ Restore Deleted Rows │
    │ Release Locks    │  │ Release Locks        │
    │ Space Reclaimable│  │ Discard Changes      │
    └──────────────────┘  └──────────────────────┘

Key Differences: DELETE vs INSERT vs UPDATE

Detailed Step-by-Step Breakdown

Step 1-3: Parse and Validate (Similar to UPDATE)

-- Valid syntax
DELETE FROM employees WHERE employee_id = 101;

-- Missing FROM keyword
DELETE employees WHERE employee_id = 101;
-- ORA-00971: missing SET keyword (confusing error!)

-- Correct alternative syntax
DELETE employees WHERE employee_id = 101;  -- FROM is optional

Step 4: Locate Rows to Delete

Like UPDATE, efficient row location is critical.

Access Path Examples:

-- Fast: Index scan on primary key
DELETE FROM employees WHERE employee_id = 101;
-- Uses: INDEX UNIQUE SCAN on EMP_PK

-- Slow: Full table scan
DELETE FROM employees WHERE last_name = 'Smith';
-- Uses: TABLE ACCESS FULL (if no index on last_name)

-- Very dangerous: No WHERE clause
DELETE FROM employees;
-- Deletes ALL rows! Always use WHERE clause

Check Execution Plan:

SET LINESIZE 200
SET PAGESIZE 100

EXPLAIN PLAN FOR
DELETE FROM employees WHERE employee_id = 101;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

/*
Expected Output:
---------------------------------------------------------
| Id  | Operation          | Name     | Rows | Cost |
---------------------------------------------------------
|   0 | DELETE STATEMENT   |          |    1 |    1 |
|   1 |  DELETE            | EMPLOYEES|      |      |
|*  2 |   INDEX UNIQUE SCAN| EMP_PK   |    1 |    1 |
---------------------------------------------------------
Predicate: 2 - access("EMPLOYEE_ID"=101)
*/

Monitor Row Location Performance:

SET LINESIZE 180
COLUMN sql_text FORMAT A60
COLUMN rows_deleted FORMAT 999,999

SELECT sql_id,
       sql_text,
       executions,
       rows_processed rows_deleted,
       buffer_gets,
       ROUND(buffer_gets/NULLIF(rows_processed,0)) gets_per_row
FROM v$sql
WHERE UPPER(sql_text) LIKE '%DELETE FROM EMPLOYEES%'
  AND sql_text NOT LIKE '%v$sql%'
ORDER BY buffer_gets DESC
FETCH FIRST 10 ROWS ONLY;

Step 5: Check Foreign Key Constraints (Critical!)

DELETE must verify no child records exist, unless CASCADE DELETE is defined.

Foreign Key Constraint Types:

┌────────────────────────────────────────────────────┐
│         FOREIGN KEY DELETE RULES                   │
├────────────────────────────────────────────────────┤
│                                                    │
│  1. NO ACTION (Default)                            │
│     └─ Prevents delete if children exist           │
│                                                    │
│  2. ON DELETE CASCADE                              │
│     └─ Automatically deletes child records         │
│                                                    │
│  3. ON DELETE SET NULL                             │
│     └─ Sets foreign key in children to NULL        │
│                                                    │
│  4. ON DELETE SET DEFAULT                          │
│     └─ Sets foreign key to default value           │
│                                                    │
└────────────────────────────────────────────────────┘

Example – Default Behavior (NO ACTION):

-- Create tables with foreign key
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(20),
    department_id NUMBER,
    CONSTRAINT emp_dept_fk 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
);

-- Insert test data
INSERT INTO departments VALUES (10, 'Sales');
INSERT INTO employees VALUES (101, 'John', 10);
COMMIT;

-- Try to delete parent with existing children
DELETE FROM departments WHERE department_id = 10;
-- ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated
-- child record found

-- Solution 1: Delete children first
DELETE FROM employees WHERE department_id = 10;
DELETE FROM departments WHERE department_id = 10;
COMMIT;

-- Solution 2: Delete only if no children
DELETE FROM departments 
WHERE department_id = 10
  AND NOT EXISTS (
      SELECT 1 FROM employees 
      WHERE department_id = 10
  );

Example – CASCADE DELETE:

-- Create with CASCADE DELETE
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(20),
    department_id NUMBER,
    CONSTRAINT emp_dept_fk 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
        ON DELETE CASCADE
);

-- Insert test data
INSERT INTO departments VALUES (10, 'Sales');
INSERT INTO employees VALUES (101, 'John', 10);
INSERT INTO employees VALUES (102, 'Jane', 10);
COMMIT;

-- Delete parent - children deleted automatically
DELETE FROM departments WHERE department_id = 10;
-- 1 row deleted (department)
-- 2 child rows also deleted automatically!

COMMIT;

-- Verify
SELECT COUNT(*) FROM employees WHERE department_id = 10;
-- Result: 0 (all deleted)

Example – SET NULL:

-- Create with SET NULL
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(20),
    department_id NUMBER,
    CONSTRAINT emp_dept_fk 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
        ON DELETE SET NULL
);

-- Insert test data
INSERT INTO departments VALUES (10, 'Sales');
INSERT INTO employees VALUES (101, 'John', 10);
COMMIT;

-- Delete parent - children's FK set to NULL
DELETE FROM departments WHERE department_id = 10;
-- 1 row deleted

-- Verify
SELECT employee_id, first_name, department_id 
FROM employees 
WHERE employee_id = 101;
-- Result: 101, John, NULL

View Foreign Key Constraints:

SET LINESIZE 200
COLUMN constraint_name FORMAT A30
COLUMN table_name FORMAT A20
COLUMN r_table_name FORMAT A20
COLUMN delete_rule FORMAT A15

SELECT c.constraint_name,
       c.table_name,
       r.table_name r_table_name,
       c.delete_rule,
       c.status
FROM dba_constraints c
JOIN dba_constraints r ON c.r_constraint_name = r.constraint_name
WHERE c.constraint_type = 'R'
  AND c.owner = 'HR'
  AND c.table_name = 'EMPLOYEES'
ORDER BY c.constraint_name;

Step 6: Acquire Locks

Same as UPDATE – must lock rows before deletion.

-- Session 1
DELETE FROM employees WHERE employee_id = 101;
-- Lock acquired, not committed

-- Session 2
DELETE FROM employees WHERE employee_id = 101;
-- WAITS for Session 1

UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- WAITS for Session 1 (row is locked)

Find Blocking DELETE Operations:

SET LINESIZE 200
COLUMN blocker_sql FORMAT A50
COLUMN waiter_sql FORMAT A50

SELECT blocking_session blocker_sid,
       sid waiter_sid,
       username,
       event,
       seconds_in_wait,
       sql_id
FROM v$session
WHERE blocking_session IS NOT NULL
  AND event LIKE '%enq: TX%';

Step 7: Create Undo Records (Largest Undo)

DELETE generates the most undo because the entire row must be saved.

Undo Comparison:

-- Sample table
CREATE TABLE test_table (
    id NUMBER,
    col1 VARCHAR2(100),
    col2 VARCHAR2(100),
    col3 VARCHAR2(100),
    col4 VARCHAR2(100),
    col5 VARCHAR2(100)
);

-- INSERT: Minimal undo (~50 bytes)
-- Only needs to know "remove this row if rollback"
INSERT INTO test_table VALUES (1, 'A', 'B', 'C', 'D', 'E');

-- UPDATE: Before image of changed columns (~100 bytes)
-- Old value of col1 only
UPDATE test_table SET col1 = 'NEW' WHERE id = 1;

-- DELETE: Complete row (~500 bytes)
-- Entire row must be saved to restore if rollback
DELETE FROM test_table WHERE id = 1;

Monitor Undo for DELETE:

SET LINESIZE 180
COLUMN username FORMAT A15
COLUMN sql_text FORMAT A50
COLUMN undo_mb FORMAT 999.99

SELECT s.username,
       s.sid,
       ROUND(t.used_ublk * 8192/1024/1024, 2) undo_mb,
       t.start_time,
       sq.sql_text
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE UPPER(sq.sql_text) LIKE '%DELETE%'
ORDER BY undo_mb DESC;

Large DELETE Undo Impact:

-- Dangerous: Delete 1 million rows
DELETE FROM large_table WHERE status = 'INACTIVE';
-- May fail with: ORA-30036 (unable to extend undo segment)

-- Better: Batch delete
BEGIN
    LOOP
        DELETE FROM large_table 
        WHERE status = 'INACTIVE' 
        AND ROWNUM <= 10000;
        
        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;  -- Release undo space
    END LOOP;
END;
/

Step 8-9: Redo Generation and Row Marking

DELETE marks rows as deleted rather than physically removing them immediately.

How Rows Are Deleted:

BEFORE DELETE:
┌──────────────────────────────────────────┐
│ Data Block 1234                           │
│ ┌──────────────────────────────────────┐ │
│ │ Row 1: Active, EmpID=100            │ │
│ │ Row 2: Active, EmpID=101            │ │
│ │ Row 3: Active, EmpID=102            │ │
│ │ Free Space: 2000 bytes               │ │
│ └──────────────────────────────────────┘ │
└──────────────────────────────────────────┘

AFTER DELETE (EmpID=101):
┌──────────────────────────────────────────┐
│ Data Block 1234 (DIRTY)                  │
│ ┌──────────────────────────────────────┐ │
│ │ Row 1: Active, EmpID=100             │ │
│ │ Row 2: DELETED (marked)              │ │
│ │ Row 3: Active, EmpID=102             │ │
│ │ Free Space: 2000 bytes (not changed) │ │
│ └──────────────────────────────────────┘ │
│                                          │
│ Note: Space not immediately reclaimed    │
│ Physical cleanup happens during:         │
│ • New INSERT into block                  │
│ • Block reorganization                   │
│ • Table rebuild                          │
└──────────────────────────────────────────┘

Row Directory Update:

Block Header Changes:

• Row directory entry marked as deleted
• Row flag set to “deleted”
• Space remains in block but unavailable until cleanup
• PCTFREE threshold determines when space is reusable

Step 10: Update ALL Indexes (Most Expensive)

Unlike UPDATE, DELETE must remove entries from every index on the table.

-- Table with multiple indexes
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,           -- Index 1: EMP_PK
    email VARCHAR2(50) UNIQUE,                -- Index 2: EMP_EMAIL_UK
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    salary NUMBER,                            -- Index 3: EMP_SALARY_IDX
    department_id NUMBER,                     -- Index 4: EMP_DEPT_IDX
    hire_date DATE                            -- Index 5: EMP_HIRE_DATE_IDX
);

CREATE INDEX emp_salary_idx ON employees(salary);
CREATE INDEX emp_dept_idx ON employees(department_id);
CREATE INDEX emp_hire_date_idx ON employees(hire_date);

-- Single DELETE affects ALL 5 indexes!
DELETE FROM employees WHERE employee_id = 101;

/*
Index Operations Required:
1. EMP_PK: Remove entry for employee_id=101
2. EMP_EMAIL_UK: Remove entry for email value
3. EMP_SALARY_IDX: Remove entry for salary value
4. EMP_DEPT_IDX: Remove entry for department_id value
5. EMP_HIRE_DATE_IDX: Remove entry for hire_date value

Result: 5 index maintenance operations!
*/

Performance Impact:

-- Measure DELETE with many indexes
SET TIMING ON

DELETE FROM employees WHERE employee_id = 101;
-- Elapsed: 00:00:00.15

-- Compare: Drop indexes, delete, recreate indexes
SET TIMING OFF

-- Drop indexes
DROP INDEX emp_salary_idx;
DROP INDEX emp_dept_idx;
DROP INDEX emp_hire_date_idx;

SET TIMING ON
DELETE FROM employees WHERE employee_id = 101;
-- Elapsed: 00:00:00.03 (5x faster!)

-- Recreate indexes
CREATE INDEX emp_salary_idx ON employees(salary);
CREATE INDEX emp_dept_idx ON employees(department_id);
CREATE INDEX emp_hire_date_idx ON employees(hire_date);
SET TIMING OFF

View Index Impact:

SET LINESIZE 180
COLUMN table_name FORMAT A20
COLUMN index_name FORMAT A30
COLUMN column_name FORMAT A20

SELECT i.table_name,
       i.index_name,
       ic.column_name,
       i.uniqueness,
       i.leaf_blocks,
       i.status
FROM dba_indexes i
JOIN dba_ind_columns ic ON i.index_name = ic.index_name
WHERE i.owner = 'HR'
  AND i.table_name = 'EMPLOYEES'
ORDER BY i.index_name, ic.column_position;

Step 11: CASCADE DELETE Operations

If ON DELETE CASCADE is defined, child records are automatically deleted.

-- Create parent-child relationship with CASCADE
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(20),
    department_id NUMBER,
    CONSTRAINT emp_dept_fk 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
        ON DELETE CASCADE
);

CREATE TABLE employee_history (
    history_id NUMBER PRIMARY KEY,
    employee_id NUMBER,
    action_date DATE,
    CONSTRAINT hist_emp_fk 
        FOREIGN KEY (employee_id) 
        REFERENCES employees(employee_id)
        ON DELETE CASCADE
);

-- Insert test data
INSERT INTO departments VALUES (10, 'Sales');
INSERT INTO employees VALUES (101, 'John', 10);
INSERT INTO employees VALUES (102, 'Jane', 10);
INSERT INTO employee_history VALUES (1, 101, SYSDATE);
INSERT INTO employee_history VALUES (2, 101, SYSDATE-30);
INSERT INTO employee_history VALUES (3, 102, SYSDATE);
COMMIT;

-- Delete department - cascades through hierarchy
DELETE FROM departments WHERE department_id = 10;
-- Cascade effect:
-- 1. Deletes department (1 row)
-- 2. Deletes employees in dept 10 (2 rows)
-- 3. Deletes history for those employees (3 rows)
-- Total: 6 rows deleted with single DELETE statement!

COMMIT;

Monitor CASCADE DELETE:

-- Enable SQL trace to see cascade operations
ALTER SESSION SET SQL_TRACE = TRUE;

DELETE FROM departments WHERE department_id = 10;

-- Check trace file for cascade delete operations
-- You'll see multiple DELETE statements executed automatically

Performance Warning:

-- Cascade can delete thousands of rows unexpectedly!
DELETE FROM parent_table WHERE id = 1;
-- If deeply nested relationships exist, this could delete:
-- • 1 parent row
-- • 100 child rows
-- • 1,000 grandchild rows
-- • 10,000 great-grandchild rows
-- Total: 11,101 rows deleted!

-- Always check dependencies first
SELECT table_name, constraint_name, delete_rule
FROM dba_constraints
WHERE r_constraint_name IN (
    SELECT constraint_name 
    FROM dba_constraints 
    WHERE table_name = 'PARENT_TABLE'
)
AND constraint_type = 'R';

Step 12: Fire Triggers

DELETE triggers can access old values but not new values (row doesn’t exist after delete).

-- Audit DELETE operations
CREATE OR REPLACE TRIGGER emp_delete_audit
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name,
        operation,
        old_employee_id,
        old_first_name,
        old_salary,
        deleted_by,
        deleted_date
    ) VALUES (
        'EMPLOYEES',
        'DELETE',
        :OLD.employee_id,
        :OLD.first_name,
        :OLD.salary,
        USER,
        SYSDATE
    );
END;
/

-- Prevent accidental deletion of high-value employees
CREATE OR REPLACE TRIGGER emp_delete_prevent
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF :OLD.salary > 100000 THEN
        RAISE_APPLICATION_ERROR(-20001, 
            'Cannot delete employee with salary > 100000. Contact HR.');
    END IF;
END;
/

-- Archive before delete
CREATE OR REPLACE TRIGGER emp_delete_archive
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_archive
    VALUES (:OLD.employee_id, :OLD.first_name, :OLD.last_name, 
            :OLD.salary, :OLD.department_id, SYSDATE);
END;
/

-- Test triggers
DELETE FROM employees WHERE employee_id = 101;
-- If salary > 100000: Error prevented
-- Otherwise: Row deleted, archived, and audit logged

View DELETE Triggers:

SET LINESIZE 200
COLUMN trigger_name FORMAT A30
COLUMN trigger_type FORMAT A20

SELECT trigger_name,
       trigger_type,
       triggering_event,
       status,
       SUBSTR(trigger_body, 1, 50) body_preview
FROM dba_triggers
WHERE owner = 'HR'
  AND table_name = 'EMPLOYEES'
  AND triggering_event LIKE '%DELETE%'
ORDER BY trigger_name;

DELETE vs TRUNCATE

Understanding the difference is crucial for interviews and operations.

DELETE Example:

-- Can use WHERE clause
DELETE FROM employees WHERE department_id = 10;
-- Generates undo, can rollback
ROLLBACK;  -- Restores deleted rows

-- Check result
SELECT COUNT(*) FROM employees WHERE department_id = 10;
-- Result: Original count (rollback successful)

TRUNCATE Example:

-- No WHERE clause allowed
TRUNCATE TABLE employees;
-- Cannot specify WHERE department_id = 10

-- Cannot rollback
ROLLBACK;  -- Has no effect

-- Check result
SELECT COUNT(*) FROM employees;
-- Result: 0 (all rows gone, cannot restore)

When to Use Each:

-- Use DELETE when:
-- • Need to delete specific rows (WHERE clause)
-- • Need rollback capability
-- • Need triggers to fire
-- • Deleting small number of rows

DELETE FROM employees WHERE hire_date < '2020-01-01';

-- Use TRUNCATE when:
-- • Deleting ALL rows
-- • No need for rollback
-- • No triggers needed
-- • Performance is critical
-- • Want to reset high water mark

TRUNCATE TABLE staging_table;

TRUNCATE with Foreign Keys:

-- This will fail
TRUNCATE TABLE departments;
-- ORA-02266: unique/primary keys in table referenced by enabled foreign keys

-- Solutions:

-- 1. Disable foreign key constraints
ALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;
TRUNCATE TABLE departments;
ALTER TABLE employees ENABLE CONSTRAINT emp_dept_fk;

-- 2. Delete children first
DELETE FROM employees;
COMMIT;
TRUNCATE TABLE departments;

-- 3. Use CASCADE (if available - Oracle 12c+)
TRUNCATE TABLE departments CASCADE;
-- Truncates parent and all children

Performance Optimization for DELETE

1. Use Efficient WHERE Clauses

-- Bad: Full table scan
DELETE FROM employees WHERE UPPER(last_name) = 'SMITH';

-- Good: Use indexed column
CREATE INDEX emp_last_name_idx ON employees(last_name);
DELETE FROM employees WHERE last_name = 'SMITH';

-- Better: Use primary key when possible
DELETE FROM employees WHERE employee_id = 101;

2. Batch DELETE Operations

-- Bad: Large single transaction
DELETE FROM large_table WHERE status = 'INACTIVE';  -- 1 million rows
COMMIT;
-- Huge undo usage, long lock time

-- Good: Batch with commits
BEGIN
    LOOP
        DELETE FROM large_table 
        WHERE status = 'INACTIVE' 
        AND ROWNUM <= 10000;
        
        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
    END LOOP;
END;
/
-- Smaller undo segments, shorter lock durations

3. Consider TRUNCATE for Large Deletes

-- If deleting ALL or most rows, use TRUNCATE
-- 100x faster than DELETE

-- Instead of this:
DELETE FROM staging_table;  -- Slow if millions of rows
COMMIT;

-- Use this:
TRUNCATE TABLE staging_table;  -- Very fast, no undo

4. Drop Indexes for Bulk DELETE

-- For very large delete operations (millions of rows)

-- Step 1: Drop non-unique indexes
DROP INDEX emp_dept_idx;
DROP INDEX emp_salary_idx;

-- Step 2: Perform bulk delete
DELETE FROM employees WHERE hire_date < '2020-01-01';
COMMIT;

-- Step 3: Recreate indexes
CREATE INDEX emp_dept_idx ON employees(department_id);
CREATE INDEX emp_salary_idx ON employees(salary);

-- Step 4: Gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', CASCADE => TRUE);

5. Use Partitioning for Large Tables

-- Create partitioned table
CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    customer_id NUMBER,
    amount NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION orders_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION orders_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION orders_2025 VALUES LESS THAN (DATE '2026-01-01')
);

-- Instead of slow DELETE:
DELETE FROM orders WHERE order_date < DATE '2024-01-01';  -- Slow!

-- Use fast partition drop:
ALTER TABLE orders DROP PARTITION orders_2023;  -- Instant!

-- Or truncate partition:
ALTER TABLE orders TRUNCATE PARTITION orders_2023;  -- Very fast!

6. Disable Triggers During Bulk DELETE

-- Disable triggers temporarily
ALTER TABLE employees DISABLE ALL TRIGGERS;

-- Perform bulk delete
DELETE FROM employees WHERE status = 'TERMINATED';
COMMIT;

-- Re-enable triggers
ALTER TABLE employees ENABLE ALL TRIGGERS;

Common DELETE Errors and Solutions

Error: ORA-02292 (Child Records Exist)

-- Problem
DELETE FROM departments WHERE department_id = 10;
-- ORA-02292: integrity constraint violated - child record found

-- Solution 1: Check child records
SELECT COUNT(*) FROM employees WHERE department_id = 10;
-- Result: 5 employees exist

-- Solution 2: Delete children first
DELETE FROM employees WHERE department_id = 10;
DELETE FROM departments WHERE department_id = 10;
COMMIT;

-- Solution 3: Use CASCADE DELETE (if defined)
-- ALTER TABLE employees ADD CONSTRAINT emp_dept_fk
-- FOREIGN KEY (department_id) REFERENCES departments(department_id)
-- ON DELETE CASCADE;

DELETE FROM departments WHERE department_id = 10;
-- Automatically deletes child records

Error: ORA-01031 (Insufficient Privileges)

-- Problem
DELETE FROM hr.employees WHERE employee_id = 101;
-- ORA-01031: insufficient privileges

-- Solution: Grant DELETE privilege
-- As HR user:
GRANT DELETE ON employees TO other_user;

-- Or grant through role:
GRANT DELETE ANY TABLE TO other_user;

Error: ORA-01013 (User Requested Cancel)

-- Problem: Long-running delete
DELETE FROM large_table WHERE status = 'OLD';
-- Takes too long, user cancels

-- Solution: Batch the operation
BEGIN
    FOR i IN 1..100 LOOP
        DELETE FROM large_table 
        WHERE status = 'OLD' 
        AND ROWNUM <= 10000;
        
        COMMIT;
        
        EXIT WHEN SQL%ROWCOUNT = 0;
        
        DBMS_OUTPUT.PUT_LINE('Batch ' || i || ' completed');
    END LOOP;
END;
/

Error: ORA-30036 (Unable to Extend Undo)

-- Problem: Undo tablespace full
DELETE FROM huge_table WHERE created_date < '2023-01-01';
-- ORA-30036: unable to extend segment in undo tablespace

-- Solution 1: Add undo datafile
ALTER TABLESPACE undotbs1 ADD DATAFILE 
'/path/to/undotbs02.dbf' SIZE 5G;

-- Solution 2: Batch with commits
BEGIN
    LOOP
        DELETE FROM huge_table 
        WHERE created_date < DATE '2023-01-01'
        AND ROWNUM <= 5000;
        
        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;  -- Release undo
    END LOOP;
END;
/

Monitoring DELETE Performance

Complete Monitoring Script

SET ECHO OFF
SET LINESIZE 200
SET PAGESIZE 100

PROMPT ========================================
PROMPT   DELETE OPERATION MONITORING
PROMPT ========================================
PROMPT 

PROMPT 1. Active DELETE Operations
PROMPT ----------------------------------------
COLUMN username FORMAT A15
COLUMN sql_text FORMAT A60 TRUNCATE
COLUMN elapsed_sec FORMAT 999.99

SELECT s.username,
       s.sid,
       s.serial#,
       SUBSTR(sq.sql_text, 1, 60) sql_text,
       ROUND(s.last_call_et, 2) elapsed_sec,
       s.blocking_session
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE UPPER(sq.sql_text) LIKE '%DELETE%'
  AND s.username IS NOT NULL
  AND sq.sql_text NOT LIKE '%v$sql%';

PROMPT 
PROMPT 2. Undo Usage for DELETE
PROMPT ----------------------------------------
COLUMN undo_mb FORMAT 999.99

SELECT s.username,
       s.sid,
       ROUND(t.used_ublk * 8192/1024/1024, 2) undo_mb,
       TO_CHAR(t.start_time, 'HH24:MI:SS') start_time
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
ORDER BY undo_mb DESC;

PROMPT 
PROMPT 3. Top DELETE Statements by Execution Time
PROMPT ----------------------------------------
COLUMN rows_deleted FORMAT 999,999
COLUMN buffer_gets FORMAT 999,999,999

SELECT sql_id,
       SUBSTR(sql_text, 1, 60) sql_text,
       executions,
       rows_processed rows_deleted,
       ROUND(elapsed_time/1000000, 2) elapsed_sec,
       buffer_gets
FROM v$sql
WHERE UPPER(sql_text) LIKE '%DELETE FROM%'
  AND sql_text NOT LIKE '%v$sql%'
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

PROMPT 
PROMPT 4. Long-Running DELETE Progress
PROMPT ----------------------------------------
COLUMN pct_complete FORMAT 990.99

SELECT sid,
       serial#,
       opname,
       sofar,
       totalwork,
       ROUND(sofar/NULLIF(totalwork,0)*100, 2) pct_complete,
       time_remaining,
       elapsed_seconds
FROM v$session_longops
WHERE opname LIKE '%DELETE%'
  AND sofar <> totalwork;

Best Practices Summary

1. Always Use WHERE Clause

-- Dangerous: Deletes ALL rows
DELETE FROM employees;

-- Safe: Deletes specific rows
DELETE FROM employees WHERE department_id = 10;

-- Safest: Verify count first
SELECT COUNT(*) FROM employees WHERE department_id = 10;
-- Check count, then delete
DELETE FROM employees WHERE department_id = 10;

2. Check Foreign Key Dependencies

-- Before deleting parent records
SELECT table_name, constraint_name
FROM dba_constraints
WHERE r_constraint_name = 'DEPT_PK'
  AND constraint_type = 'R';

3. Use Bind Variables

-- Bad
DELETE FROM employees WHERE employee_id = 101;
DELETE FROM employees WHERE employee_id = 102;

-- Good
DELETE FROM employees WHERE employee_id = :emp_id;

4. Consider TRUNCATE for Full Table DELETE

-- Slow
DELETE FROM staging_table;

-- Fast
TRUNCATE TABLE staging_table;

5. Archive Before DELETE

-- Create archive first
INSERT INTO employees_archive
SELECT * FROM employees WHERE status = 'TERMINATED';
COMMIT;

-- Then delete
DELETE FROM employees WHERE status = 'TERMINATED';
COMMIT;

6. Monitor Space Usage

-- After large DELETE, space is not immediately reclaimed
-- Consider:

-- Option 1: Rebuild table
ALTER TABLE employees MOVE;
ALTER INDEX emp_pk REBUILD;

-- Option 2: Shrink table (if possible)
ALTER TABLE employees ENABLE ROW MOVEMENT;
ALTER TABLE employees SHRINK SPACE CASCADE;

Quick Reference: DELETE Syntax

-- Basic DELETE
DELETE FROM table_name WHERE condition;

-- DELETE with subquery
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location = 'BOSTON'
);

-- DELETE with EXISTS
DELETE FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.department_id = e.department_id
);

-- DELETE with RETURNING clause
DELETE FROM employees 
WHERE employee_id = 101
RETURNING first_name, last_name INTO :fname, :lname;

-- DELETE all rows (be careful!)
DELETE FROM table_name;  -- Better to use TRUNCATE

-- DELETE with join (using EXISTS)
DELETE FROM employees e
WHERE EXISTS (
    SELECT 1 FROM terminations t
    WHERE t.employee_id = e.employee_id
    AND t.termination_date < SYSDATE - 365
);

Conclusion

DELETE is the most resource-intensive DML operation, generating the largest undo records and requiring updates to all indexes. Understanding its execution flow is essential for efficient database management and troubleshooting.

Key Takeaways:

1. DELETE generates most undo – Complete row stored for rollback
2. All indexes must be updated – Unlike UPDATE (only changed columns)
3. Foreign keys must be checked – Child records prevent deletion
4. Space not immediately reclaimed – Rows marked deleted, cleanup delayed
5. CASCADE can delete many rows – Understand FK relationships
6. TRUNCATE is faster – For full table delete operations
7. Batch large deletes – Prevent undo exhaustion and long locks
8. Always use WHERE clause – Except when using TRUNCATE

By mastering DELETE execution internals and using the optimization techniques provided, you’ll be well-prepared for DBA interviews and capable of managing delete operations efficiently in production!

Remember: DELETE is permanent after COMMIT – always verify your WHERE clause and consider archiving data first!