{"id":5437,"date":"2026-02-15T16:12:39","date_gmt":"2026-02-15T10:42:39","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5437"},"modified":"2026-02-15T16:12:41","modified_gmt":"2026-02-15T10:42:41","slug":"oracle-delete-statement-behind-the-scenes","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-delete-statement-behind-the-scenes\/","title":{"rendered":"Oracle DELETE Statement: Behind the Scenes"},"content":{"rendered":"\n

When you execute DELETE FROM employees WHERE employee_id = 101;<\/code>, 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.<\/p>\n\n\n\n

In this guide, we’ll explore the complete execution flow of a DELETE statement in Oracle Database.<\/p>\n\n\n\n

The Complete DELETE Statement Execution Flow<\/h2>\n\n\n\n
\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                USER EXECUTES DELETE STATEMENT                   \u2502\n\u2502        DELETE FROM employees WHERE employee_id = 101;           \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 1: SYNTAX CHECK                          \u2502\n\u2502  \u2022 Validate DELETE syntax                                       \u2502\n\u2502  \u2022 Check FROM clause                                            \u2502\n\u2502  \u2022 Verify WHERE clause structure                                \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                    [Valid Syntax?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 Error: ORA-00936\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 2: SEMANTIC CHECK                        \u2502\n\u2502  \u2022 Does table exist?                                            \u2502\n\u2502  \u2022 Does user have DELETE privilege?                             \u2502\n\u2502  \u2022 Check for dependencies (foreign keys)                        \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                    [Objects Valid?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 Error: ORA-00942\/ORA-01031\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 3: SHARED POOL CHECK                     \u2502\n\u2502  \u2022 Generate SQL hash                                            \u2502\n\u2502  \u2022 Search Library Cache                                         \u2502\n\u2502  \u2022 Hard parse vs Soft parse                                     \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 4: LOCATE ROWS (WHERE Clause)            \u2502\n\u2502  \u2022 Execute WHERE clause predicate                               \u2502\n\u2502  \u2022 Use index if available                                       \u2502\n\u2502  \u2022 Identify ROWIDs of rows to delete                            \u2502\n\u2502  \u2022 Full Table Scan or Index Scan                                \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                    [Rows Found?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 0 rows deleted\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 5: CHECK FOREIGN KEY CONSTRAINTS         \u2502\n\u2502  \u2022 Check if child records exist                                 \u2502\n\u2502  \u2022 Verify referential integrity                                 \u2502\n\u2502  \u2022 Check CASCADE DELETE rules                                   \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                    [FK Valid?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 Error: ORA-02292\n                           \u2502         (Child records exist)\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 6: ACQUIRE LOCKS                         \u2502\n\u2502  \u2022 Get TX (Transaction) lock on each row                        \u2502\n\u2502  \u2022 Lock table in Row Exclusive (RX) mode                        |\n\u2502  \u2022 Wait if rows locked by another session                       \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 7: CREATE UNDO RECORDS                   \u2502\n\u2502  \u2022 Store COMPLETE row data in undo                              \u2502\n\u2502  \u2022 Largest undo generation of all DML                           \u2502\n\u2502  \u2022 Save entire row for potential ROLLBACK                       \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 8: GENERATE REDO RECORDS                 \u2502\n\u2502  \u2022 Create redo for delete operation                             \u2502\n\u2502  \u2022 Log row deletion information                                 \u2502\n\u2502  \u2022 Write to Redo Log Buffer                                     \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 9: MARK ROW AS DELETED                   \u2502\n\u2502  \u2022 Don't physically remove row immediately                      \u2502\n\u2502  \u2022 Mark row as deleted in block header                          \u2502\n\u2502  \u2022 Row space not immediately reclaimed                          \u2502\n\u2502  \u2022 Physical cleanup happens later (PCTFREE)                     \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 10: UPDATE ALL INDEXES                   \u2502\n\u2502  \u2022 Remove entries from ALL indexes                              \u2502\n\u2502  \u2022 Update every index on the table                              \u2502\n\u2502  \u2022 Most expensive part of DELETE                                \u2502\n\u2502  \u2022 Generate redo for index changes                              \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 11: CASCADE DELETE (if defined)          \u2502\n\u2502  \u2022 Delete child records in referenced tables                    \u2502\n\u2502  \u2022 Follow CASCADE DELETE rules                                  \u2502\n\u2502  \u2022 Recursive deletion process                                   \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 12: FIRE TRIGGERS (if any)               \u2502\n\u2502  \u2022 Execute BEFORE DELETE triggers                               \u2502\n\u2502  \u2022 Can prevent deletion                                         \u2502\n\u2502  \u2022 Execute AFTER DELETE triggers                                \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 13: RETURN SUCCESS                       \u2502\n\u2502  \u2022 Return \"N rows deleted\" message                              \u2502\n\u2502  \u2022 Rows marked deleted in memory                                \u2502\n\u2502  \u2022 Visible only to current session                              \u2502\n\u2502  \u2022 Locks held until COMMIT\/ROLLBACK                             \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n                \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n                \u2502                     \u2502\n             COMMIT              ROLLBACK\n                \u2502                     \u2502\n                \u25bc                     \u25bc\n    \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510  \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n    \u2502 Make Permanent   \u2502  \u2502 Restore Deleted Rows \u2502\n    \u2502 Release Locks    \u2502  \u2502 Release Locks        \u2502\n    \u2502 Space Reclaimable\u2502  \u2502 Discard Changes      \u2502\n    \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518  \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Key Differences: DELETE vs INSERT vs UPDATE<\/h2>\n\n\n\n
Aspect<\/th>INSERT<\/th>UPDATE<\/th>DELETE<\/th><\/tr><\/thead>
Row Location<\/strong><\/td>Find free space<\/td>Locate existing rows<\/td>Locate existing rows<\/td><\/tr>
Undo Data<\/strong><\/td>Minimal<\/td>Full before image<\/td>Complete row<\/strong> (largest)<\/td><\/tr>
Index Impact<\/strong><\/td>Add to ALL indexes<\/td>Only changed columns<\/td>Remove from ALL indexes<\/strong><\/td><\/tr>
Space Reclaim<\/strong><\/td>N\/A<\/td>N\/A<\/td>Delayed (not immediate)<\/td><\/tr>
FK Checking<\/strong><\/td>Parent must exist<\/td>Parent must exist<\/td>Child must not exist<\/strong><\/td><\/tr>
Performance<\/strong><\/td>Fast<\/td>Medium<\/td>Slowest<\/strong> (all indexes)<\/td><\/tr>
Redo Generation<\/strong><\/td>Moderate<\/td>Moderate<\/td>High<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Detailed Step-by-Step Breakdown<\/h2>\n\n\n\n
Step 1-3: Parse and Validate (Similar to UPDATE)<\/h3>\n\n\n\n
-- Valid syntax\nDELETE FROM employees WHERE employee_id = 101;\n\n-- Missing FROM keyword\nDELETE employees WHERE employee_id = 101;\n-- ORA-00971: missing SET keyword (confusing error!)\n\n-- Correct alternative syntax\nDELETE employees WHERE employee_id = 101;  -- FROM is optional<\/code><\/pre>\n\n\n\n
Step 4: Locate Rows to Delete<\/h3>\n\n\n\n
Like UPDATE, efficient row location is critical.<\/p>\n\n\n\n
Access Path Examples:<\/strong><\/p>\n\n\n\n
-- Fast: Index scan on primary key\nDELETE FROM employees WHERE employee_id = 101;\n-- Uses: INDEX UNIQUE SCAN on EMP_PK\n\n-- Slow: Full table scan\nDELETE FROM employees WHERE last_name = 'Smith';\n-- Uses: TABLE ACCESS FULL (if no index on last_name)\n\n-- Very dangerous: No WHERE clause\nDELETE FROM employees;\n-- Deletes ALL rows! Always use WHERE clause<\/code><\/pre>\n\n\n\n
Check Execution Plan:<\/strong><\/p>\n\n\n\n
SET LINESIZE 200\nSET PAGESIZE 100\n\nEXPLAIN PLAN FOR\nDELETE FROM employees WHERE employee_id = 101;\n\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);\n\n\/*\nExpected Output:\n---------------------------------------------------------\n| Id  | Operation          | Name     | Rows | Cost |\n---------------------------------------------------------\n|   0 | DELETE STATEMENT   |          |    1 |    1 |\n|   1 |  DELETE            | EMPLOYEES|      |      |\n|*  2 |   INDEX UNIQUE SCAN| EMP_PK   |    1 |    1 |\n---------------------------------------------------------\nPredicate: 2 - access(\"EMPLOYEE_ID\"=101)\n*\/<\/code><\/pre>\n\n\n\n
Monitor Row Location Performance:<\/strong><\/p>\n\n\n\n
SET LINESIZE 180\nCOLUMN sql_text FORMAT A60\nCOLUMN rows_deleted FORMAT 999,999\n\nSELECT sql_id,\n       sql_text,\n       executions,\n       rows_processed rows_deleted,\n       buffer_gets,\n       ROUND(buffer_gets\/NULLIF(rows_processed,0)) gets_per_row\nFROM v$sql\nWHERE UPPER(sql_text) LIKE '%DELETE FROM EMPLOYEES%'\n  AND sql_text NOT LIKE '%v$sql%'\nORDER BY buffer_gets DESC\nFETCH FIRST 10 ROWS ONLY;<\/code><\/pre>\n\n\n\n
Step 5: Check Foreign Key Constraints (Critical!)<\/h3>\n\n\n\n
DELETE must verify no child records exist, unless CASCADE DELETE is defined.<\/p>\n\n\n\n
Foreign Key Constraint Types:<\/strong><\/p>\n\n\n\n
\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502         FOREIGN KEY DELETE RULES                   \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                                    \u2502\n\u2502  1. NO ACTION (Default)                            \u2502\n\u2502     \u2514\u2500 Prevents delete if children exist           \u2502\n\u2502                                                    \u2502\n\u2502  2. ON DELETE CASCADE                              \u2502\n\u2502     \u2514\u2500 Automatically deletes child records         \u2502\n\u2502                                                    \u2502\n\u2502  3. ON DELETE SET NULL                             \u2502\n\u2502     \u2514\u2500 Sets foreign key in children to NULL        \u2502\n\u2502                                                    \u2502\n\u2502  4. ON DELETE SET DEFAULT                          \u2502\n\u2502     \u2514\u2500 Sets foreign key to default value           \u2502\n\u2502                                                    \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Example – Default Behavior (NO ACTION):<\/strong><\/p>\n\n\n\n
-- Create tables with foreign key\nCREATE TABLE departments (\n    department_id NUMBER PRIMARY KEY,\n    department_name VARCHAR2(50)\n);\n\nCREATE TABLE employees (\n    employee_id NUMBER PRIMARY KEY,\n    first_name VARCHAR2(20),\n    department_id NUMBER,\n    CONSTRAINT emp_dept_fk \n        FOREIGN KEY (department_id) \n        REFERENCES departments(department_id)\n);\n\n-- Insert test data\nINSERT INTO departments VALUES (10, 'Sales');\nINSERT INTO employees VALUES (101, 'John', 10);\nCOMMIT;\n\n-- Try to delete parent with existing children\nDELETE FROM departments WHERE department_id = 10;\n-- ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated\n-- child record found\n\n-- Solution 1: Delete children first\nDELETE FROM employees WHERE department_id = 10;\nDELETE FROM departments WHERE department_id = 10;\nCOMMIT;\n\n-- Solution 2: Delete only if no children\nDELETE FROM departments \nWHERE department_id = 10\n  AND NOT EXISTS (\n      SELECT 1 FROM employees \n      WHERE department_id = 10\n  );<\/code><\/pre>\n\n\n\n
Example – CASCADE DELETE:<\/strong><\/p>\n\n\n\n
-- Create with CASCADE DELETE\nCREATE TABLE employees (\n    employee_id NUMBER PRIMARY KEY,\n    first_name VARCHAR2(20),\n    department_id NUMBER,\n    CONSTRAINT emp_dept_fk \n        FOREIGN KEY (department_id) \n        REFERENCES departments(department_id)\n        ON DELETE CASCADE\n);\n\n-- Insert test data\nINSERT INTO departments VALUES (10, 'Sales');\nINSERT INTO employees VALUES (101, 'John', 10);\nINSERT INTO employees VALUES (102, 'Jane', 10);\nCOMMIT;\n\n-- Delete parent - children deleted automatically\nDELETE FROM departments WHERE department_id = 10;\n-- 1 row deleted (department)\n-- 2 child rows also deleted automatically!\n\nCOMMIT;\n\n-- Verify\nSELECT COUNT(*) FROM employees WHERE department_id = 10;\n-- Result: 0 (all deleted)<\/code><\/pre>\n\n\n\n
Example – SET NULL:<\/strong><\/p>\n\n\n\n
-- Create with SET NULL\nCREATE TABLE employees (\n    employee_id NUMBER PRIMARY KEY,\n    first_name VARCHAR2(20),\n    department_id NUMBER,\n    CONSTRAINT emp_dept_fk \n        FOREIGN KEY (department_id) \n        REFERENCES departments(department_id)\n        ON DELETE SET NULL\n);\n\n-- Insert test data\nINSERT INTO departments VALUES (10, 'Sales');\nINSERT INTO employees VALUES (101, 'John', 10);\nCOMMIT;\n\n-- Delete parent - children's FK set to NULL\nDELETE FROM departments WHERE department_id = 10;\n-- 1 row deleted\n\n-- Verify\nSELECT employee_id, first_name, department_id \nFROM employees \nWHERE employee_id = 101;\n-- Result: 101, John, NULL<\/code><\/pre>\n\n\n\n
View Foreign Key Constraints:<\/strong><\/p>\n\n\n\n
SET LINESIZE 200\nCOLUMN constraint_name FORMAT A30\nCOLUMN table_name FORMAT A20\nCOLUMN r_table_name FORMAT A20\nCOLUMN delete_rule FORMAT A15\n\nSELECT c.constraint_name,\n       c.table_name,\n       r.table_name r_table_name,\n       c.delete_rule,\n       c.status\nFROM dba_constraints c\nJOIN dba_constraints r ON c.r_constraint_name = r.constraint_name\nWHERE c.constraint_type = 'R'\n  AND c.owner = 'HR'\n  AND c.table_name = 'EMPLOYEES'\nORDER BY c.constraint_name;<\/code><\/pre>\n\n\n\n
Step 6: Acquire Locks<\/h3>\n\n\n\n
Same as UPDATE – must lock rows before deletion.<\/p>\n\n\n\n
-- Session 1\nDELETE FROM employees WHERE employee_id = 101;\n-- Lock acquired, not committed\n\n-- Session 2\nDELETE FROM employees WHERE employee_id = 101;\n-- WAITS for Session 1\n\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\n-- WAITS for Session 1 (row is locked)<\/code><\/pre>\n\n\n\n
Find Blocking DELETE Operations:<\/strong><\/p>\n\n\n\n
SET LINESIZE 200\nCOLUMN blocker_sql FORMAT A50\nCOLUMN waiter_sql FORMAT A50\n\nSELECT blocking_session blocker_sid,\n       sid waiter_sid,\n       username,\n       event,\n       seconds_in_wait,\n       sql_id\nFROM v$session\nWHERE blocking_session IS NOT NULL\n  AND event LIKE '%enq: TX%';<\/code><\/pre>\n\n\n\n
Step 7: Create Undo Records (Largest Undo)<\/h3>\n\n\n\n
DELETE generates the most undo because the entire row<\/strong> must be saved.<\/p>\n\n\n\n
Undo Comparison:<\/strong><\/p>\n\n\n\n
-- Sample table\nCREATE TABLE test_table (\n    id NUMBER,\n    col1 VARCHAR2(100),\n    col2 VARCHAR2(100),\n    col3 VARCHAR2(100),\n    col4 VARCHAR2(100),\n    col5 VARCHAR2(100)\n);\n\n-- INSERT: Minimal undo (~50 bytes)\n-- Only needs to know \"remove this row if rollback\"\nINSERT INTO test_table VALUES (1, 'A', 'B', 'C', 'D', 'E');\n\n-- UPDATE: Before image of changed columns (~100 bytes)\n-- Old value of col1 only\nUPDATE test_table SET col1 = 'NEW' WHERE id = 1;\n\n-- DELETE: Complete row (~500 bytes)\n-- Entire row must be saved to restore if rollback\nDELETE FROM test_table WHERE id = 1;<\/code><\/pre>\n\n\n\n
Monitor Undo for DELETE:<\/strong><\/p>\n\n\n\n
SET LINESIZE 180\nCOLUMN username FORMAT A15\nCOLUMN sql_text FORMAT A50\nCOLUMN undo_mb FORMAT 999.99\n\nSELECT s.username,\n       s.sid,\n       ROUND(t.used_ublk * 8192\/1024\/1024, 2) undo_mb,\n       t.start_time,\n       sq.sql_text\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr\nLEFT JOIN v$sql sq ON s.sql_id = sq.sql_id\nWHERE UPPER(sq.sql_text) LIKE '%DELETE%'\nORDER BY undo_mb DESC;<\/code><\/pre>\n\n\n\n
Large DELETE Undo Impact:<\/strong><\/p>\n\n\n\n
-- Dangerous: Delete 1 million rows\nDELETE FROM large_table WHERE status = 'INACTIVE';\n-- May fail with: ORA-30036 (unable to extend undo segment)\n\n-- Better: Batch delete\nBEGIN\n    LOOP\n        DELETE FROM large_table \n        WHERE status = 'INACTIVE' \n        AND ROWNUM <= 10000;\n        \n        EXIT WHEN SQL%ROWCOUNT = 0;\n        COMMIT;  -- Release undo space\n    END LOOP;\nEND;\n\/<\/code><\/pre>\n\n\n\n
Step 8-9: Redo Generation and Row Marking<\/h3>\n\n\n\n
DELETE marks rows as deleted rather than physically removing them immediately.<\/p>\n\n\n\n
How Rows Are Deleted:<\/p>\n\n\n\n
BEFORE DELETE:\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 Data Block 1234                           \u2502\n\u2502 \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502\n\u2502 \u2502 Row 1: Active, EmpID=100            \u2502 \u2502\n\u2502 \u2502 Row 2: Active, EmpID=101            \u2502 \u2502\n\u2502 \u2502 Row 3: Active, EmpID=102            \u2502 \u2502\n\u2502 \u2502 Free Space: 2000 bytes               \u2502 \u2502\n\u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518 \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\nAFTER DELETE (EmpID=101):\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 Data Block 1234 (DIRTY)                  \u2502\n\u2502 \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502\n\u2502 \u2502 Row 1: Active, EmpID=100             \u2502 \u2502\n\u2502 \u2502 Row 2: DELETED (marked)              \u2502 \u2502\n\u2502 \u2502 Row 3: Active, EmpID=102             \u2502 \u2502\n\u2502 \u2502 Free Space: 2000 bytes (not changed) \u2502 \u2502\n\u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518 \u2502\n\u2502                                          \u2502\n\u2502 Note: Space not immediately reclaimed    \u2502\n\u2502 Physical cleanup happens during:         \u2502\n\u2502 \u2022 New INSERT into block                  \u2502\n\u2502 \u2022 Block reorganization                   \u2502\n\u2502 \u2022 Table rebuild                          \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Row Directory Update:<\/p>\n\n\n\n
Block Header Changes: <\/p>\n\n\n\n
    \n
  • Row directory entry marked as deleted <\/li>\n\n\n\n
  • Row flag set to “deleted” <\/li>\n\n\n\n
  • Space remains in block but unavailable until cleanup <\/li>\n\n\n\n
  • PCTFREE threshold determines when space is reusable<\/li>\n<\/ul>\n\n\n\n
    Step 10: Update ALL Indexes (Most Expensive)<\/h3>\n\n\n\n
    Unlike UPDATE, DELETE must remove entries from every index<\/strong> on the table.<\/p>\n\n\n\n
    -- Table with multiple indexes\nCREATE TABLE employees (\n    employee_id NUMBER PRIMARY KEY,           -- Index 1: EMP_PK\n    email VARCHAR2(50) UNIQUE,                -- Index 2: EMP_EMAIL_UK\n    first_name VARCHAR2(20),\n    last_name VARCHAR2(25),\n    salary NUMBER,                            -- Index 3: EMP_SALARY_IDX\n    department_id NUMBER,                     -- Index 4: EMP_DEPT_IDX\n    hire_date DATE                            -- Index 5: EMP_HIRE_DATE_IDX\n);\n\nCREATE INDEX emp_salary_idx ON employees(salary);\nCREATE INDEX emp_dept_idx ON employees(department_id);\nCREATE INDEX emp_hire_date_idx ON employees(hire_date);\n\n-- Single DELETE affects ALL 5 indexes!\nDELETE FROM employees WHERE employee_id = 101;\n\n\/*\nIndex Operations Required:\n1. EMP_PK: Remove entry for employee_id=101\n2. EMP_EMAIL_UK: Remove entry for email value\n3. EMP_SALARY_IDX: Remove entry for salary value\n4. EMP_DEPT_IDX: Remove entry for department_id value\n5. EMP_HIRE_DATE_IDX: Remove entry for hire_date value\n\nResult: 5 index maintenance operations!\n*\/<\/code><\/pre>\n\n\n\n
    Performance Impact:<\/strong><\/p>\n\n\n\n
    -- Measure DELETE with many indexes\nSET TIMING ON\n\nDELETE FROM employees WHERE employee_id = 101;\n-- Elapsed: 00:00:00.15\n\n-- Compare: Drop indexes, delete, recreate indexes\nSET TIMING OFF\n\n-- Drop indexes\nDROP INDEX emp_salary_idx;\nDROP INDEX emp_dept_idx;\nDROP INDEX emp_hire_date_idx;\n\nSET TIMING ON\nDELETE FROM employees WHERE employee_id = 101;\n-- Elapsed: 00:00:00.03 (5x faster!)\n\n-- Recreate indexes\nCREATE INDEX emp_salary_idx ON employees(salary);\nCREATE INDEX emp_dept_idx ON employees(department_id);\nCREATE INDEX emp_hire_date_idx ON employees(hire_date);\nSET TIMING OFF<\/code><\/pre>\n\n\n\n
    View Index Impact:<\/strong><\/p>\n\n\n\n
    SET LINESIZE 180\nCOLUMN table_name FORMAT A20\nCOLUMN index_name FORMAT A30\nCOLUMN column_name FORMAT A20\n\nSELECT i.table_name,\n       i.index_name,\n       ic.column_name,\n       i.uniqueness,\n       i.leaf_blocks,\n       i.status\nFROM dba_indexes i\nJOIN dba_ind_columns ic ON i.index_name = ic.index_name\nWHERE i.owner = 'HR'\n  AND i.table_name = 'EMPLOYEES'\nORDER BY i.index_name, ic.column_position;<\/code><\/pre>\n\n\n\n
    Step 11: CASCADE DELETE Operations<\/h3>\n\n\n\n
    If ON DELETE CASCADE is defined, child records are automatically deleted.<\/p>\n\n\n\n
    -- Create parent-child relationship with CASCADE\nCREATE TABLE departments (\n    department_id NUMBER PRIMARY KEY,\n    department_name VARCHAR2(50)\n);\n\nCREATE TABLE employees (\n    employee_id NUMBER PRIMARY KEY,\n    first_name VARCHAR2(20),\n    department_id NUMBER,\n    CONSTRAINT emp_dept_fk \n        FOREIGN KEY (department_id) \n        REFERENCES departments(department_id)\n        ON DELETE CASCADE\n);\n\nCREATE TABLE employee_history (\n    history_id NUMBER PRIMARY KEY,\n    employee_id NUMBER,\n    action_date DATE,\n    CONSTRAINT hist_emp_fk \n        FOREIGN KEY (employee_id) \n        REFERENCES employees(employee_id)\n        ON DELETE CASCADE\n);\n\n-- Insert test data\nINSERT INTO departments VALUES (10, 'Sales');\nINSERT INTO employees VALUES (101, 'John', 10);\nINSERT INTO employees VALUES (102, 'Jane', 10);\nINSERT INTO employee_history VALUES (1, 101, SYSDATE);\nINSERT INTO employee_history VALUES (2, 101, SYSDATE-30);\nINSERT INTO employee_history VALUES (3, 102, SYSDATE);\nCOMMIT;\n\n-- Delete department - cascades through hierarchy\nDELETE FROM departments WHERE department_id = 10;\n-- Cascade effect:\n-- 1. Deletes department (1 row)\n-- 2. Deletes employees in dept 10 (2 rows)\n-- 3. Deletes history for those employees (3 rows)\n-- Total: 6 rows deleted with single DELETE statement!\n\nCOMMIT;<\/code><\/pre>\n\n\n\n
    Monitor CASCADE DELETE:<\/strong><\/p>\n\n\n\n
    -- Enable SQL trace to see cascade operations\nALTER SESSION SET SQL_TRACE = TRUE;\n\nDELETE FROM departments WHERE department_id = 10;\n\n-- Check trace file for cascade delete operations\n-- You'll see multiple DELETE statements executed automatically<\/code><\/pre>\n\n\n\n
    Performance Warning:<\/strong><\/p>\n\n\n\n
    -- Cascade can delete thousands of rows unexpectedly!\nDELETE FROM parent_table WHERE id = 1;\n-- If deeply nested relationships exist, this could delete:\n-- \u2022 1 parent row\n-- \u2022 100 child rows\n-- \u2022 1,000 grandchild rows\n-- \u2022 10,000 great-grandchild rows\n-- Total: 11,101 rows deleted!\n\n-- Always check dependencies first\nSELECT table_name, constraint_name, delete_rule\nFROM dba_constraints\nWHERE r_constraint_name IN (\n    SELECT constraint_name \n    FROM dba_constraints \n    WHERE table_name = 'PARENT_TABLE'\n)\nAND constraint_type = 'R';<\/code><\/pre>\n\n\n\n
    Step 12: Fire Triggers<\/h3>\n\n\n\n
    DELETE triggers can access old values but not new values (row doesn’t exist after delete).<\/p>\n\n\n\n
    -- Audit DELETE operations\nCREATE OR REPLACE TRIGGER emp_delete_audit\nBEFORE DELETE ON employees\nFOR EACH ROW\nBEGIN\n    INSERT INTO audit_log (\n        table_name,\n        operation,\n        old_employee_id,\n        old_first_name,\n        old_salary,\n        deleted_by,\n        deleted_date\n    ) VALUES (\n        'EMPLOYEES',\n        'DELETE',\n        :OLD.employee_id,\n        :OLD.first_name,\n        :OLD.salary,\n        USER,\n        SYSDATE\n    );\nEND;\n\/\n\n-- Prevent accidental deletion of high-value employees\nCREATE OR REPLACE TRIGGER emp_delete_prevent\nBEFORE DELETE ON employees\nFOR EACH ROW\nBEGIN\n    IF :OLD.salary > 100000 THEN\n        RAISE_APPLICATION_ERROR(-20001, \n            'Cannot delete employee with salary > 100000. Contact HR.');\n    END IF;\nEND;\n\/\n\n-- Archive before delete\nCREATE OR REPLACE TRIGGER emp_delete_archive\nBEFORE DELETE ON employees\nFOR EACH ROW\nBEGIN\n    INSERT INTO employees_archive\n    VALUES (:OLD.employee_id, :OLD.first_name, :OLD.last_name, \n            :OLD.salary, :OLD.department_id, SYSDATE);\nEND;\n\/\n\n-- Test triggers\nDELETE FROM employees WHERE employee_id = 101;\n-- If salary > 100000: Error prevented\n-- Otherwise: Row deleted, archived, and audit logged<\/code><\/pre>\n\n\n\n
    View DELETE Triggers:<\/strong><\/p>\n\n\n\n
    SET LINESIZE 200\nCOLUMN trigger_name FORMAT A30\nCOLUMN trigger_type FORMAT A20\n\nSELECT trigger_name,\n       trigger_type,\n       triggering_event,\n       status,\n       SUBSTR(trigger_body, 1, 50) body_preview\nFROM dba_triggers\nWHERE owner = 'HR'\n  AND table_name = 'EMPLOYEES'\n  AND triggering_event LIKE '%DELETE%'\nORDER BY trigger_name;<\/code><\/pre>\n\n\n\n
    DELETE vs TRUNCATE<\/h2>\n\n\n\n
    Understanding the difference is crucial for interviews and operations.<\/p>\n\n\n\n
    Feature<\/th>DELETE<\/th>TRUNCATE<\/th><\/tr><\/thead>
    DML\/DDL<\/strong><\/td>DML<\/td>DDL<\/td><\/tr>
    WHERE Clause<\/strong><\/td>Yes (selective delete)<\/td>No (all rows)<\/td><\/tr>
    Rollback<\/strong><\/td>Yes (can rollback)<\/td>No (cannot rollback)<\/td><\/tr>
    Triggers<\/strong><\/td>Fires triggers<\/td>Does NOT fire triggers<\/td><\/tr>
    Undo Generation<\/strong><\/td>High (full row saved)<\/td>Minimal<\/td><\/tr>
    Redo Generation<\/strong><\/td>High<\/td>Minimal<\/td><\/tr>
    Locks<\/strong><\/td>Row-level locks<\/td>Table-level lock<\/td><\/tr>
    Performance<\/strong><\/td>Slow for large tables<\/td>Very fast<\/td><\/tr>
    Space Reclaim<\/strong><\/td>Delayed<\/td>Immediate (resets HWM)<\/td><\/tr>
    Indexes<\/strong><\/td>Maintained<\/td>Maintained (but faster)<\/td><\/tr>
    Foreign Keys<\/strong><\/td>Checks constraints<\/td>Cannot truncate if FK exists<\/td><\/tr>
    Commit<\/strong><\/td>Must commit explicitly<\/td>Auto-commit<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    DELETE Example:<\/strong><\/p>\n\n\n\n
    -- Can use WHERE clause\nDELETE FROM employees WHERE department_id = 10;\n-- Generates undo, can rollback\nROLLBACK;  -- Restores deleted rows\n\n-- Check result\nSELECT COUNT(*) FROM employees WHERE department_id = 10;\n-- Result: Original count (rollback successful)<\/code><\/pre>\n\n\n\n
    TRUNCATE Example:<\/strong><\/p>\n\n\n\n
    -- No WHERE clause allowed\nTRUNCATE TABLE employees;\n-- Cannot specify WHERE department_id = 10\n\n-- Cannot rollback\nROLLBACK;  -- Has no effect\n\n-- Check result\nSELECT COUNT(*) FROM employees;\n-- Result: 0 (all rows gone, cannot restore)<\/code><\/pre>\n\n\n\n
    When to Use Each:<\/strong><\/p>\n\n\n\n
    -- Use DELETE when:\n-- \u2022 Need to delete specific rows (WHERE clause)\n-- \u2022 Need rollback capability\n-- \u2022 Need triggers to fire\n-- \u2022 Deleting small number of rows\n\nDELETE FROM employees WHERE hire_date < '2020-01-01';\n\n-- Use TRUNCATE when:\n-- \u2022 Deleting ALL rows\n-- \u2022 No need for rollback\n-- \u2022 No triggers needed\n-- \u2022 Performance is critical\n-- \u2022 Want to reset high water mark\n\nTRUNCATE TABLE staging_table;<\/code><\/pre>\n\n\n\n
    TRUNCATE with Foreign Keys:<\/strong><\/p>\n\n\n\n
    -- This will fail\nTRUNCATE TABLE departments;\n-- ORA-02266: unique\/primary keys in table referenced by enabled foreign keys\n\n-- Solutions:\n\n-- 1. Disable foreign key constraints\nALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;\nTRUNCATE TABLE departments;\nALTER TABLE employees ENABLE CONSTRAINT emp_dept_fk;\n\n-- 2. Delete children first\nDELETE FROM employees;\nCOMMIT;\nTRUNCATE TABLE departments;\n\n-- 3. Use CASCADE (if available - Oracle 12c+)\nTRUNCATE TABLE departments CASCADE;\n-- Truncates parent and all children<\/code><\/pre>\n\n\n\n
    Performance Optimization for DELETE<\/h2>\n\n\n\n
    1. Use Efficient WHERE Clauses<\/h3>\n\n\n\n
    -- Bad: Full table scan\nDELETE FROM employees WHERE UPPER(last_name) = 'SMITH';\n\n-- Good: Use indexed column\nCREATE INDEX emp_last_name_idx ON employees(last_name);\nDELETE FROM employees WHERE last_name = 'SMITH';\n\n-- Better: Use primary key when possible\nDELETE FROM employees WHERE employee_id = 101;<\/code><\/pre>\n\n\n\n
    2. Batch DELETE Operations<\/h3>\n\n\n\n
    -- Bad: Large single transaction\nDELETE FROM large_table WHERE status = 'INACTIVE';  -- 1 million rows\nCOMMIT;\n-- Huge undo usage, long lock time\n\n-- Good: Batch with commits\nBEGIN\n    LOOP\n        DELETE FROM large_table \n        WHERE status = 'INACTIVE' \n        AND ROWNUM <= 10000;\n        \n        EXIT WHEN SQL%ROWCOUNT = 0;\n        COMMIT;\n    END LOOP;\nEND;\n\/\n-- Smaller undo segments, shorter lock durations<\/code><\/pre>\n\n\n\n
    3. Consider TRUNCATE for Large Deletes<\/h3>\n\n\n\n
    -- If deleting ALL or most rows, use TRUNCATE\n-- 100x faster than DELETE\n\n-- Instead of this:\nDELETE FROM staging_table;  -- Slow if millions of rows\nCOMMIT;\n\n-- Use this:\nTRUNCATE TABLE staging_table;  -- Very fast, no undo<\/code><\/pre>\n\n\n\n
    4. Drop Indexes for Bulk DELETE<\/h3>\n\n\n\n
    -- For very large delete operations (millions of rows)\n\n-- Step 1: Drop non-unique indexes\nDROP INDEX emp_dept_idx;\nDROP INDEX emp_salary_idx;\n\n-- Step 2: Perform bulk delete\nDELETE FROM employees WHERE hire_date < '2020-01-01';\nCOMMIT;\n\n-- Step 3: Recreate indexes\nCREATE INDEX emp_dept_idx ON employees(department_id);\nCREATE INDEX emp_salary_idx ON employees(salary);\n\n-- Step 4: Gather statistics\nEXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', CASCADE => TRUE);<\/code><\/pre>\n\n\n\n
    5. Use Partitioning for Large Tables<\/h3>\n\n\n\n
    -- Create partitioned table\nCREATE TABLE orders (\n    order_id NUMBER,\n    order_date DATE,\n    customer_id NUMBER,\n    amount NUMBER\n)\nPARTITION BY RANGE (order_date) (\n    PARTITION orders_2023 VALUES LESS THAN (DATE '2024-01-01'),\n    PARTITION orders_2024 VALUES LESS THAN (DATE '2025-01-01'),\n    PARTITION orders_2025 VALUES LESS THAN (DATE '2026-01-01')\n);\n\n-- Instead of slow DELETE:\nDELETE FROM orders WHERE order_date < DATE '2024-01-01';  -- Slow!\n\n-- Use fast partition drop:\nALTER TABLE orders DROP PARTITION orders_2023;  -- Instant!\n\n-- Or truncate partition:\nALTER TABLE orders TRUNCATE PARTITION orders_2023;  -- Very fast!<\/code><\/pre>\n\n\n\n
    6. Disable Triggers During Bulk DELETE<\/h3>\n\n\n\n
    -- Disable triggers temporarily\nALTER TABLE employees DISABLE ALL TRIGGERS;\n\n-- Perform bulk delete\nDELETE FROM employees WHERE status = 'TERMINATED';\nCOMMIT;\n\n-- Re-enable triggers\nALTER TABLE employees ENABLE ALL TRIGGERS;<\/code><\/pre>\n\n\n\n
    Common DELETE Errors and Solutions<\/h2>\n\n\n\n
    Error: ORA-02292 (Child Records Exist)<\/h3>\n\n\n\n
    -- Problem\nDELETE FROM departments WHERE department_id = 10;\n-- ORA-02292: integrity constraint violated - child record found\n\n-- Solution 1: Check child records\nSELECT COUNT(*) FROM employees WHERE department_id = 10;\n-- Result: 5 employees exist\n\n-- Solution 2: Delete children first\nDELETE FROM employees WHERE department_id = 10;\nDELETE FROM departments WHERE department_id = 10;\nCOMMIT;\n\n-- Solution 3: Use CASCADE DELETE (if defined)\n-- ALTER TABLE employees ADD CONSTRAINT emp_dept_fk\n-- FOREIGN KEY (department_id) REFERENCES departments(department_id)\n-- ON DELETE CASCADE;\n\nDELETE FROM departments WHERE department_id = 10;\n-- Automatically deletes child records<\/code><\/pre>\n\n\n\n
    Error: ORA-01031 (Insufficient Privileges)<\/h3>\n\n\n\n
    -- Problem\nDELETE FROM hr.employees WHERE employee_id = 101;\n-- ORA-01031: insufficient privileges\n\n-- Solution: Grant DELETE privilege\n-- As HR user:\nGRANT DELETE ON employees TO other_user;\n\n-- Or grant through role:\nGRANT DELETE ANY TABLE TO other_user;<\/code><\/pre>\n\n\n\n
    Error: ORA-01013 (User Requested Cancel)<\/h3>\n\n\n\n
    -- Problem: Long-running delete\nDELETE FROM large_table WHERE status = 'OLD';\n-- Takes too long, user cancels\n\n-- Solution: Batch the operation\nBEGIN\n    FOR i IN 1..100 LOOP\n        DELETE FROM large_table \n        WHERE status = 'OLD' \n        AND ROWNUM <= 10000;\n        \n        COMMIT;\n        \n        EXIT WHEN SQL%ROWCOUNT = 0;\n        \n        DBMS_OUTPUT.PUT_LINE('Batch ' || i || ' completed');\n    END LOOP;\nEND;\n\/<\/code><\/pre>\n\n\n\n
    Error: ORA-30036 (Unable to Extend Undo)<\/h3>\n\n\n\n
    -- Problem: Undo tablespace full\nDELETE FROM huge_table WHERE created_date < '2023-01-01';\n-- ORA-30036: unable to extend segment in undo tablespace\n\n-- Solution 1: Add undo datafile\nALTER TABLESPACE undotbs1 ADD DATAFILE \n'\/path\/to\/undotbs02.dbf' SIZE 5G;\n\n-- Solution 2: Batch with commits\nBEGIN\n    LOOP\n        DELETE FROM huge_table \n        WHERE created_date < DATE '2023-01-01'\n        AND ROWNUM <= 5000;\n        \n        EXIT WHEN SQL%ROWCOUNT = 0;\n        COMMIT;  -- Release undo\n    END LOOP;\nEND;\n\/<\/code><\/pre>\n\n\n\n
    Monitoring DELETE Performance<\/h2>\n\n\n\n
    Complete Monitoring Script<\/h3>\n\n\n\n
    SET ECHO OFF\nSET LINESIZE 200\nSET PAGESIZE 100\n\nPROMPT ========================================\nPROMPT   DELETE OPERATION MONITORING\nPROMPT ========================================\nPROMPT \n\nPROMPT 1. Active DELETE Operations\nPROMPT ----------------------------------------\nCOLUMN username FORMAT A15\nCOLUMN sql_text FORMAT A60 TRUNCATE\nCOLUMN elapsed_sec FORMAT 999.99\n\nSELECT s.username,\n       s.sid,\n       s.serial#,\n       SUBSTR(sq.sql_text, 1, 60) sql_text,\n       ROUND(s.last_call_et, 2) elapsed_sec,\n       s.blocking_session\nFROM v$session s\nLEFT JOIN v$sql sq ON s.sql_id = sq.sql_id\nWHERE UPPER(sq.sql_text) LIKE '%DELETE%'\n  AND s.username IS NOT NULL\n  AND sq.sql_text NOT LIKE '%v$sql%';\n\nPROMPT \nPROMPT 2. Undo Usage for DELETE\nPROMPT ----------------------------------------\nCOLUMN undo_mb FORMAT 999.99\n\nSELECT s.username,\n       s.sid,\n       ROUND(t.used_ublk * 8192\/1024\/1024, 2) undo_mb,\n       TO_CHAR(t.start_time, 'HH24:MI:SS') start_time\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr\nORDER BY undo_mb DESC;\n\nPROMPT \nPROMPT 3. Top DELETE Statements by Execution Time\nPROMPT ----------------------------------------\nCOLUMN rows_deleted FORMAT 999,999\nCOLUMN buffer_gets FORMAT 999,999,999\n\nSELECT sql_id,\n       SUBSTR(sql_text, 1, 60) sql_text,\n       executions,\n       rows_processed rows_deleted,\n       ROUND(elapsed_time\/1000000, 2) elapsed_sec,\n       buffer_gets\nFROM v$sql\nWHERE UPPER(sql_text) LIKE '%DELETE FROM%'\n  AND sql_text NOT LIKE '%v$sql%'\nORDER BY elapsed_time DESC\nFETCH FIRST 10 ROWS ONLY;\n\nPROMPT \nPROMPT 4. Long-Running DELETE Progress\nPROMPT ----------------------------------------\nCOLUMN pct_complete FORMAT 990.99\n\nSELECT sid,\n       serial#,\n       opname,\n       sofar,\n       totalwork,\n       ROUND(sofar\/NULLIF(totalwork,0)*100, 2) pct_complete,\n       time_remaining,\n       elapsed_seconds\nFROM v$session_longops\nWHERE opname LIKE '%DELETE%'\n  AND sofar <> totalwork;<\/code><\/pre>\n\n\n\n
    Best Practices Summary<\/h2>\n\n\n\n
    1. Always Use WHERE Clause<\/h3>\n\n\n\n
    -- Dangerous: Deletes ALL rows\nDELETE FROM employees;\n\n-- Safe: Deletes specific rows\nDELETE FROM employees WHERE department_id = 10;\n\n-- Safest: Verify count first\nSELECT COUNT(*) FROM employees WHERE department_id = 10;\n-- Check count, then delete\nDELETE FROM employees WHERE department_id = 10;<\/code><\/pre>\n\n\n\n
    2. Check Foreign Key Dependencies<\/h3>\n\n\n\n
    -- Before deleting parent records\nSELECT table_name, constraint_name\nFROM dba_constraints\nWHERE r_constraint_name = 'DEPT_PK'\n  AND constraint_type = 'R';<\/code><\/pre>\n\n\n\n
    3. Use Bind Variables<\/h3>\n\n\n\n
    -- Bad\nDELETE FROM employees WHERE employee_id = 101;\nDELETE FROM employees WHERE employee_id = 102;\n\n-- Good\nDELETE FROM employees WHERE employee_id = :emp_id;<\/code><\/pre>\n\n\n\n
    4. Consider TRUNCATE for Full Table DELETE<\/h3>\n\n\n\n
    -- Slow\nDELETE FROM staging_table;\n\n-- Fast\nTRUNCATE TABLE staging_table;<\/code><\/pre>\n\n\n\n
    5. Archive Before DELETE<\/h3>\n\n\n\n
    -- Create archive first\nINSERT INTO employees_archive\nSELECT * FROM employees WHERE status = 'TERMINATED';\nCOMMIT;\n\n-- Then delete\nDELETE FROM employees WHERE status = 'TERMINATED';\nCOMMIT;<\/code><\/pre>\n\n\n\n
    6. Monitor Space Usage<\/h3>\n\n\n\n
    -- After large DELETE, space is not immediately reclaimed\n-- Consider:\n\n-- Option 1: Rebuild table\nALTER TABLE employees MOVE;\nALTER INDEX emp_pk REBUILD;\n\n-- Option 2: Shrink table (if possible)\nALTER TABLE employees ENABLE ROW MOVEMENT;\nALTER TABLE employees SHRINK SPACE CASCADE;<\/code><\/pre>\n\n\n\n
    Quick Reference: DELETE Syntax<\/h2>\n\n\n\n
    -- Basic DELETE\nDELETE FROM table_name WHERE condition;\n\n-- DELETE with subquery\nDELETE FROM employees\nWHERE department_id IN (\n    SELECT department_id FROM departments WHERE location = 'BOSTON'\n);\n\n-- DELETE with EXISTS\nDELETE FROM employees e\nWHERE NOT EXISTS (\n    SELECT 1 FROM departments d \n    WHERE d.department_id = e.department_id\n);\n\n-- DELETE with RETURNING clause\nDELETE FROM employees \nWHERE employee_id = 101\nRETURNING first_name, last_name INTO :fname, :lname;\n\n-- DELETE all rows (be careful!)\nDELETE FROM table_name;  -- Better to use TRUNCATE\n\n-- DELETE with join (using EXISTS)\nDELETE FROM employees e\nWHERE EXISTS (\n    SELECT 1 FROM terminations t\n    WHERE t.employee_id = e.employee_id\n    AND t.termination_date < SYSDATE - 365\n);<\/code><\/pre>\n\n\n\n
    Conclusion<\/h2>\n\n\n\n
    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.<\/p>\n\n\n\n
    Key Takeaways:<\/strong><\/p>\n\n\n\n
      \n
    1. DELETE generates most undo<\/strong> – Complete row stored for rollback<\/li>\n\n\n\n
    2. All indexes must be updated<\/strong> – Unlike UPDATE (only changed columns)<\/li>\n\n\n\n
    3. Foreign keys must be checked<\/strong> – Child records prevent deletion<\/li>\n\n\n\n
    4. Space not immediately reclaimed<\/strong> – Rows marked deleted, cleanup delayed<\/li>\n\n\n\n
    5. CASCADE can delete many rows<\/strong> – Understand FK relationships<\/li>\n\n\n\n
    6. TRUNCATE is faster<\/strong> – For full table delete operations<\/li>\n\n\n\n
    7. Batch large deletes<\/strong> – Prevent undo exhaustion and long locks<\/li>\n\n\n\n
    8. Always use WHERE clause<\/strong> – Except when using TRUNCATE<\/li>\n<\/ol>\n\n\n\n
      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!<\/p>\n\n\n\n
      Remember:<\/strong> DELETE is permanent after COMMIT – always verify your WHERE clause and consider archiving data first!<\/p>\n","protected":false},"excerpt":{"rendered":"
      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 […]<\/p>\n","protected":false},"author":1,"featured_media":5439,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5437","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5437","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5437"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5437\/revisions"}],"predecessor-version":[{"id":5438,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5437\/revisions\/5438"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5439"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5437"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5437"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5437"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}