{"id":5434,"date":"2026-02-15T15:50:43","date_gmt":"2026-02-15T10:20:43","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5434"},"modified":"2026-02-15T15:50:45","modified_gmt":"2026-02-15T10:20:45","slug":"oracle-update-statement-behind-the-scenes","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-update-statement-behind-the-scenes\/","title":{"rendered":"Oracle UPDATE Statement: Behind the Scenes"},"content":{"rendered":"\n

When you execute UPDATE employees SET salary = 60000 WHERE employee_id = 101;<\/code>, Oracle performs a sophisticated series of operations involving undo segments, redo logs, locks, and constraints. Understanding this process is essential for database administrators, especially for performance tuning and troubleshooting.<\/p>\n\n\n\n

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

The Complete UPDATE 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 UPDATE STATEMENT                   \u2502\n\u2502   UPDATE employees SET salary=60000 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 UPDATE syntax                                       \u2502\n\u2502  \u2022 Check SET clause format                                      |\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 Do columns exist?                                             \u2502\n\u2502  \u2022 Does user have UPDATE privilege?                              \u2502\n\u2502  \u2022 Are data types compatible?                                    \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-00904\/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                    [Plan Found?]\n                           \u2502\n            \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n            \u2502                             \u2502\n           Yes                           No\n       (Soft Parse)                 (Hard Parse)\n            \u2502                             \u2502\n            \u2514\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\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 update                             \u2502\n\u2502  \u2022 May use 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 updated\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: ACQUIRE LOCKS                          \u2502\n\u2502  \u2022 Get TX (Transaction) lock on each row                         \u2502\n\u2502  \u2022 Lock table in Row Exclusive (RX) mode                         \u2502\n\u2502  \u2022 Wait if rows locked by another session                        \u2502\n\u2502  \u2022 Potential deadlock situation                                  \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 6: CREATE UNDO RECORDS                    \u2502\n\u2502  \u2022 Save \"before image\" of each row                               \u2502\n\u2502  \u2022 Store in UNDO tablespace                                      \u2502\n\u2502  \u2022 Record original values for ROLLBACK                           \u2502\n\u2502  \u2022 Critical for read consistency                                 \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: CHECK CONSTRAINTS                      \u2502\n\u2502  \u2022 Validate NOT NULL constraints                                \u2502\n\u2502  \u2022 Check CHECK constraints on new values                         \u2502\n\u2502  \u2022 Verify UNIQUE constraints                                     \u2502\n\u2502  \u2022 Validate FOREIGN KEY constraints                              \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                    [Constraints Valid?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 Error, Rollback changes\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 change vectors                                    \u2502\n\u2502  \u2022 Log both old and new values                                   \u2502\n\u2502  \u2022 Write to Redo Log Buffer                                      \u2502\n\u2502  \u2022 Ensure recoverability                                         \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: UPDATE IN BUFFER CACHE                 \u2502\n\u2502  \u2022 Read data blocks into Buffer Cache (if not already)           \u2502\n\u2502  \u2022 Modify column values in memory                                \u2502\n\u2502  \u2022 Mark blocks as \"dirty\"                                        \u2502\n\u2502  \u2022 Update block header with new SCN                              \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 INDEXES                        \u2502\n\u2502  \u2022 Update affected index entries                                 \u2502\n\u2502  \u2022 Only indexes on modified columns                              \u2502\n\u2502  \u2022 Delete old entry, insert new entry                            \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: FIRE TRIGGERS (if any)                \u2502\n\u2502  \u2022 Execute BEFORE UPDATE triggers                                \u2502\n\u2502  \u2022 Can modify :NEW values                                        \u2502\n\u2502  \u2022 Execute AFTER UPDATE 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 12: RETURN SUCCESS                        \u2502\n\u2502  \u2022 Return \"N rows updated\" message                               \u2502\n\u2502  \u2022 Changes in memory, NOT on disk yet                            \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 Old Values   \u2502\n    \u2502 Release Locks    \u2502  \u2502 Release Locks        \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: UPDATE vs INSERT<\/h2>\n\n\n\n
Aspect<\/th>INSERT<\/th>UPDATE<\/th><\/tr><\/thead>
Row Location<\/strong><\/td>Must find free space<\/td>Must locate existing rows<\/td><\/tr>
Undo Data<\/strong><\/td>Minimal (no before image)<\/td>Full before image stored<\/td><\/tr>
Index Impact<\/strong><\/td>Add entries to ALL indexes<\/td>Only indexes on changed columns<\/td><\/tr>
Lock Acquisition<\/strong><\/td>Lock new row location<\/td>Must lock existing rows (wait if locked)<\/td><\/tr>
Performance<\/strong><\/td>Generally faster<\/td>Slower due to undo\/row location<\/td><\/tr>
WHERE Clause<\/strong><\/td>Not applicable<\/td>Critical for performance<\/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 (Same as INSERT)<\/h3>\n\n\n\n
The initial parsing steps are identical to INSERT operations.<\/p>\n\n\n\n
-- Syntax check\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;  -- Valid\n\nUPDATE employees salary = 60000 WHERE employee_id = 101;  -- Invalid\n-- ORA-00971: missing SET keyword<\/code><\/pre>\n\n\n\n
Step 4: Locate Rows to Update (Critical Step)<\/h3>\n\n\n\n
Oracle must first find which rows match the WHERE clause. <\/p>\n\n\n\n
Access Path Selection:<\/p>\n\n\n\n
WHERE Clause Analysis:\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\u2510\n\u2502  Is there an index on WHERE column? \u2502\n\u2514\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\u2518\n               \u2502\n        \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n        \u2502             \u2502\n       Yes           No\n        \u2502             \u2502\n        \u25bc             \u25bc\n \u250c\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\u2510\n \u2502 INDEX SCAN  \u2502  \u2502 FULL TABLE   \u2502\n \u2502  (Fast)     \u2502  \u2502 SCAN (Slow)  \u2502\n \u2514\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\u2518<\/code><\/pre>\n\n\n\n
Example – Index Scan (Fast):<\/strong><\/p>\n\n\n\n
-- Assuming index on employee_id (primary key)\nUPDATE employees \nSET salary = 60000 \nWHERE employee_id = 101;\n\n-- Execution Plan:\n-- UPDATE STATEMENT\n--   UPDATE EMPLOYEES\n--     TABLE ACCESS BY INDEX ROWID (EMPLOYEES)\n--       INDEX UNIQUE SCAN (EMP_PK)\n-- Fast: Directly locates the row<\/code><\/pre>\n\n\n\n
Example – Full Table Scan (Slow):<\/strong><\/p>\n\n\n\n
-- No index on last_name\nUPDATE employees \nSET salary = 60000 \nWHERE last_name = 'Smith';\n\n-- Execution Plan:\n-- UPDATE STATEMENT\n--   UPDATE EMPLOYEES\n--     TABLE ACCESS FULL (EMPLOYEES)\n-- Slow: Scans entire table to find matching rows<\/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\nUPDATE employees \nSET salary = 60000 \nWHERE employee_id = 101;\n\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);<\/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_processed FORMAT 999,999\nCOLUMN buffer_gets FORMAT 999,999,999\n\nSELECT sql_id,\n       sql_text,\n       executions,\n       rows_processed,\n       buffer_gets,\n       ROUND(buffer_gets\/NULLIF(rows_processed,0)) gets_per_row\nFROM v$sql\nWHERE UPPER(sql_text) LIKE '%UPDATE 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: Acquire Locks (More Complex than INSERT)<\/h3>\n\n\n\n
UPDATE must lock existing rows, which may already be locked by other sessions.<\/p>\n\n\n\n
Lock Wait Scenario:<\/strong><\/p>\n\n\n\n
-- Session 1\nUPDATE employees \nSET salary = 60000 \nWHERE employee_id = 101;\n-- Lock acquired, transaction open\n\n-- Session 2 (tries to update same row)\nUPDATE employees \nSET department_id = 20 \nWHERE employee_id = 101;\n-- WAITS for Session 1 to COMMIT or ROLLBACK\n-- May timeout if wait is too long<\/code><\/pre>\n\n\n\n
Check Locked Rows:<\/strong><\/p>\n\n\n\n
SET LINESIZE 200\nCOLUMN username FORMAT A15\nCOLUMN object_name FORMAT A30\nCOLUMN locked_mode FORMAT A15\n\nSELECT s.username,\n       s.sid,\n       s.serial#,\n       o.object_name,\n       DECODE(l.lmode,\n              0, 'None',\n              1, 'Null',\n              2, 'Row Share',\n              3, 'Row Exclusive',\n              4, 'Share',\n              5, 'Share Row Excl',\n              6, 'Exclusive') locked_mode,\n       s.blocking_session\nFROM v$lock l\nJOIN v$session s ON l.sid = s.sid\nJOIN dba_objects o ON l.id1 = o.object_id\nWHERE s.username IS NOT NULL\n  AND o.object_name = 'EMPLOYEES';<\/code><\/pre>\n\n\n\n
Find Blocking Sessions:<\/strong><\/p>\n\n\n\n
SET LINESIZE 180\nCOLUMN blocker_sid FORMAT 9999\nCOLUMN waiter_sid FORMAT 9999\nCOLUMN wait_time_min FORMAT 999.99\n\nSELECT blocking_session blocker_sid,\n       sid waiter_sid,\n       username,\n       ROUND(wait_time\/60, 2) wait_time_min,\n       sql_id\nFROM v$session\nWHERE blocking_session IS NOT NULL\n  AND username IS NOT NULL;<\/code><\/pre>\n\n\n\n
Step 6: Create Undo Records (Most Critical for UPDATE)<\/h3>\n\n\n\n
Unlike INSERT, UPDATE requires storing the complete “before image” of each row. <\/p>\n\n\n\n
Undo Record Structure:<\/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\u2500\u2500\u2510\n\u2502              UNDO RECORD FOR UPDATE                  \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\u2500\u2500\u2524\n\u2502                                                      \u2502\n\u2502  Before Update:                                      \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510     \u2502\n\u2502  \u2502 employee_id: 101                            \u2502     \u2502\n\u2502  \u2502 first_name: 'John'                          \u2502     \u2502\n\u2502  \u2502 salary: 50000  \u2190 OLD VALUE                  \u2502     \u2502\n\u2502  \u2502 department_id: 10                           \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518     \u2502\n\u2502                                                      \u2502\n\u2502  After Update:                                       \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510     \u2502\n\u2502  \u2502 employee_id: 101                            \u2502     \u2502\n\u2502  \u2502 first_name: 'John'                          \u2502     \u2502\n\u2502  \u2502 salary: 60000  \u2190 NEW VALUE                  \u2502     \u2502\n\u2502  \u2502 department_id: 10                           \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518     \u2502\n\u2502                                                      \u2502\n\u2502  Undo Record Stores:                                 \u2502\n\u2502  \u2022 Transaction ID                                    \u2502\n\u2502  \u2022 Table and Row identifier                          \u2502\n\u2502  \u2022 OLD salary value: 50000                           \u2502\n\u2502  \u2022 Used for ROLLBACK and Read Consistency            \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\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Why UPDATE Generates More Undo:<\/strong><\/p>\n\n\n\n
-- INSERT: Minimal undo (just delete the row if rollback)\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000, 10);\n-- Undo size: ~50 bytes\n\n-- UPDATE: Full before image stored\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\n-- Undo size: ~200 bytes (entire row)\n\n-- UPDATE multiple columns: Same undo size\nUPDATE employees \nSET salary = 60000, department_id = 20, email = 'new@email.com'\nWHERE employee_id = 101;\n-- Undo size: ~200 bytes (entire row, not per column)<\/code><\/pre>\n\n\n\n
Monitor Undo Usage:<\/strong><\/p>\n\n\n\n
SET LINESIZE 180\nCOLUMN username FORMAT A15\nCOLUMN undo_mb FORMAT 999.99\nCOLUMN start_time FORMAT A20\n\nSELECT s.username,\n       s.sid,\n       s.serial#,\n       ROUND(t.used_ublk * 8192\/1024\/1024, 2) undo_mb,\n       TO_CHAR(t.start_time, 'DD-MON HH24:MI:SS') start_time,\n       s.sql_id\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr\nORDER BY t.used_ublk DESC;<\/code><\/pre>\n\n\n\n
Check Undo Tablespace:<\/strong><\/p>\n\n\n\n
SET LINESIZE 150\nCOLUMN tablespace_name FORMAT A20\nCOLUMN status FORMAT A10\nCOLUMN size_mb FORMAT 999,999.99\n\nSELECT tablespace_name,\n       status,\n       ROUND(SUM(bytes)\/1024\/1024, 2) size_mb\nFROM dba_data_files\nWHERE tablespace_name LIKE '%UNDO%'\nGROUP BY tablespace_name, status;<\/code><\/pre>\n\n\n\n
Step 7: Constraint Validation<\/h3>\n\n\n\n
Oracle validates constraints on the NEW values.<\/p>\n\n\n\n
-- Create table with constraints\nCREATE TABLE employees (\n    employee_id NUMBER PRIMARY KEY,\n    first_name VARCHAR2(20) NOT NULL,\n    salary NUMBER CHECK (salary > 0),\n    email VARCHAR2(50) UNIQUE,\n    department_id NUMBER,\n    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) \n        REFERENCES departments(department_id)\n);\n\n-- NOT NULL violation\nUPDATE employees \nSET first_name = NULL \nWHERE employee_id = 101;\n-- ORA-01407: cannot update (\"HR\".\"EMPLOYEES\".\"FIRST_NAME\") to NULL\n\n-- CHECK constraint violation\nUPDATE employees \nSET salary = -5000 \nWHERE employee_id = 101;\n-- ORA-02290: check constraint (HR.SYS_C007123) violated\n\n-- UNIQUE constraint violation\nUPDATE employees \nSET email = 'existing@email.com' \nWHERE employee_id = 101;\n-- ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated\n\n-- FOREIGN KEY violation\nUPDATE employees \nSET department_id = 999 \nWHERE employee_id = 101;\n-- ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated\n\n-- Correct UPDATE\nUPDATE employees \nSET salary = 60000, department_id = 20 \nWHERE employee_id = 101;\n-- 1 row updated<\/code><\/pre>\n\n\n\n
Step 8-9: Redo Generation and Buffer Cache Update<\/h3>\n\n\n\n
Similar to INSERT, but UPDATE logs both old and new values.<\/p>\n\n\n\n
Redo Record Content:<\/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\u2510\n\u2502       REDO RECORD FOR UPDATE           \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\u2524\n\u2502                                        \u2502\n\u2502  \u2022 Operation: UPDATE                   \u2502\n\u2502  \u2022 Table: EMPLOYEES                    \u2502\n\u2502  \u2022 ROWID: AAAHYtAAEAAAACXAAA           \u2502\n\u2502  \u2022 SCN: 1234568                        \u2502\n\u2502  \u2022 Old Value: salary = 50000           \u2502\n\u2502  \u2022 New Value: salary = 60000           \u2502\n\u2502  \u2022 Undo segment pointer                \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\u2518<\/code><\/pre>\n\n\n\n
Buffer Cache Modification:<\/strong><\/p>\n\n\n\n
BEFORE UPDATE (in Buffer Cache):\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\u2510\n\u2502 Block 1234                       \u2502\n\u2502 Row 1: EmpID=101, Salary=50000   \u2502\n\u2502 SCN: 1234567                     \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\u2518\n\nAFTER UPDATE (in Buffer Cache):\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\u2510\n\u2502 Block 1234 (DIRTY)               \u2502\n\u2502 Row 1: EmpID=101, Salary=60000   \u2502\n\u2502 SCN: 1234568 (updated)           \u2502\n\u2502 Status: Modified, not yet on disk\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\u2518<\/code><\/pre>\n\n\n\n
Step 10: Update Indexes (Only Affected Indexes)<\/h3>\n\n\n\n
Key Difference from INSERT:<\/strong> Only indexes on modified columns need updating.<\/p>\n\n\n\n
-- Table structure\nCREATE TABLE employees (\n    employee_id NUMBER PRIMARY KEY,        -- Index: EMP_PK\n    first_name VARCHAR2(20),\n    last_name VARCHAR2(25),\n    salary NUMBER,                         -- Index: EMP_SALARY_IDX\n    department_id NUMBER                   -- Index: EMP_DEPT_IDX\n);\n\n-- UPDATE only salary\nUPDATE employees \nSET salary = 60000 \nWHERE employee_id = 101;\n\n-- Index updates required:\n-- \u2713 EMP_SALARY_IDX (salary changed)\n-- \u2717 EMP_PK (employee_id not changed)\n-- \u2717 EMP_DEPT_IDX (department_id not changed)\n-- Result: Only 1 index updated\n\n-- UPDATE multiple columns\nUPDATE employees \nSET salary = 60000, department_id = 20 \nWHERE employee_id = 101;\n\n-- Index updates required:\n-- \u2713 EMP_SALARY_IDX (salary changed)\n-- \u2713 EMP_DEPT_IDX (department_id changed)\n-- \u2717 EMP_PK (employee_id not changed)\n-- Result: 2 indexes updated<\/code><\/pre>\n\n\n\n
View Index Updates:<\/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.status\nFROM dba_indexes i\nJOIN dba_ind_columns ic ON i.index_name = ic.index_name \n                       AND i.owner = ic.index_owner\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: Fire Triggers<\/h3>\n\n\n\n
UPDATE triggers can access both old and new values.<\/p>\n\n\n\n
-- Audit trigger for salary changes\nCREATE OR REPLACE TRIGGER emp_salary_audit\nAFTER UPDATE OF salary ON employees\nFOR EACH ROW\nBEGIN\n    INSERT INTO salary_audit (\n        employee_id,\n        old_salary,\n        new_salary,\n        changed_by,\n        changed_date\n    ) VALUES (\n        :OLD.employee_id,\n        :OLD.salary,\n        :NEW.salary,\n        USER,\n        SYSDATE\n    );\nEND;\n\/\n\n-- Prevent salary decrease\nCREATE OR REPLACE TRIGGER emp_salary_check\nBEFORE UPDATE OF salary ON employees\nFOR EACH ROW\nBEGIN\n    IF :NEW.salary < :OLD.salary THEN\n        RAISE_APPLICATION_ERROR(-20001, \n            'Salary cannot be decreased');\n    END IF;\nEND;\n\/\n\n-- Test triggers\nUPDATE employees \nSET salary = 55000 \nWHERE employee_id = 101;\n-- Error: Salary cannot be decreased (if old salary was 60000)\n\nUPDATE employees \nSET salary = 65000 \nWHERE employee_id = 101;\n-- Success: 1 row updated, audit record created<\/code><\/pre>\n\n\n\n
View Triggers:<\/strong><\/p>\n\n\n\n
SET LINESIZE 200\nCOLUMN trigger_name FORMAT A30\nCOLUMN triggering_event FORMAT A20\n\nSELECT trigger_name,\n       trigger_type,\n       triggering_event,\n       status\nFROM dba_triggers\nWHERE owner = 'HR'\n  AND table_name = 'EMPLOYEES'\n  AND TRIGGERING_EVENT LIKE '%UPDATE%'\nORDER BY trigger_name;<\/code><\/pre>\n\n\n\n
Performance Optimization for UPDATE<\/h2>\n\n\n\n
1. Use Efficient WHERE Clauses<\/h3>\n\n\n\n
-- Bad: Full table scan\nUPDATE employees \nSET salary = salary * 1.1 \nWHERE UPPER(last_name) = 'SMITH';\n-- Scans entire table, function on column prevents index use\n\n-- Good: Index scan\nCREATE INDEX emp_last_name_idx ON employees(last_name);\n\nUPDATE employees \nSET salary = salary * 1.1 \nWHERE last_name = 'SMITH';\n-- Uses index, much faster<\/code><\/pre>\n\n\n\n
2. Minimize Undo Generation<\/h3>\n\n\n\n
-- Bad: Updates all columns\nUPDATE employees \nSET first_name = first_name,\n    last_name = last_name,\n    salary = 60000,\n    email = email\nWHERE employee_id = 101;\n-- Generates unnecessary undo\/redo\n\n-- Good: Update only changed columns\nUPDATE employees \nSET salary = 60000 \nWHERE employee_id = 101;\n-- Minimal undo\/redo<\/code><\/pre>\n\n\n\n
3. Batch Updates with Commits<\/h3>\n\n\n\n
-- Bad: Single large transaction\nUPDATE employees SET salary = salary * 1.1;  -- Updates 100,000 rows\nCOMMIT;\n-- Huge undo segment usage, long lock time\n\n-- Good: Batch with commits\nBEGIN\n    FOR emp IN (SELECT employee_id FROM employees) LOOP\n        UPDATE employees \n        SET salary = salary * 1.1 \n        WHERE employee_id = emp.employee_id;\n        \n        IF MOD(SQL%ROWCOUNT, 1000) = 0 THEN\n            COMMIT;  -- Commit every 1000 rows\n        END IF;\n    END LOOP;\n    COMMIT;\nEND;\n\/<\/code><\/pre>\n\n\n\n
4. Use Bulk Operations<\/h3>\n\n\n\n
-- Slow: Row-by-row in loop\nBEGIN\n    FOR emp IN (SELECT employee_id FROM employees WHERE department_id = 10) LOOP\n        UPDATE employees \n        SET salary = salary * 1.1 \n        WHERE employee_id = emp.employee_id;\n    END LOOP;\n    COMMIT;\nEND;\n\/\n\n-- Fast: Single UPDATE statement\nUPDATE employees \nSET salary = salary * 1.1 \nWHERE department_id = 10;\nCOMMIT;\n\n-- Fastest: Bulk collect with FORALL\nDECLARE\n    TYPE t_emp_ids IS TABLE OF NUMBER;\n    l_emp_ids t_emp_ids;\nBEGIN\n    SELECT employee_id \n    BULK COLLECT INTO l_emp_ids\n    FROM employees \n    WHERE department_id = 10;\n    \n    FORALL i IN 1..l_emp_ids.COUNT\n        UPDATE employees \n        SET salary = salary * 1.1 \n        WHERE employee_id = l_emp_ids(i);\n    \n    COMMIT;\nEND;\n\/<\/code><\/pre>\n\n\n\n
5. Avoid Unnecessary Index Updates<\/h3>\n\n\n\n
-- Consider dropping unused indexes before bulk update\nDROP INDEX emp_unused_idx;\n\n-- Perform bulk update\nUPDATE employees SET salary = salary * 1.1;\nCOMMIT;\n\n-- Recreate index if needed\nCREATE INDEX emp_unused_idx ON employees(some_column);<\/code><\/pre>\n\n\n\n
Common UPDATE Errors and Solutions<\/h2>\n\n\n\n
Error: ORA-01407 (Cannot Update to NULL)<\/h3>\n\n\n\n
-- Problem\nUPDATE employees \nSET first_name = NULL \nWHERE employee_id = 101;\n-- ORA-01407: cannot update to NULL\n\n-- Solution: Provide a value\nUPDATE employees \nSET first_name = 'John' \nWHERE employee_id = 101;<\/code><\/pre>\n\n\n\n
Error: ORA-01654 (Unable to Extend Undo Segment)<\/h3>\n\n\n\n
-- Problem: Large update, undo tablespace full\nUPDATE large_table SET column1 = 'value';\n-- ORA-01654: unable to extend segment in undo tablespace\n\n-- Solution 1: Add datafile\nALTER TABLESPACE undotbs1 ADD DATAFILE \n'\/path\/to\/undotbs02.dbf' SIZE 2G;\n\n-- Solution 2: Batch the update\nUPDATE large_table SET column1 = 'value' WHERE ROWNUM <= 10000;\nCOMMIT;\n-- Repeat in batches<\/code><\/pre>\n\n\n\n
Error: ORA-00060 (Deadlock Detected)<\/h3>\n\n\n\n
-- Session 1\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\nUPDATE departments SET budget = 100000 WHERE department_id = 10;\n\n-- Session 2 (simultaneously)\nUPDATE departments SET budget = 100000 WHERE department_id = 10;\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\n-- ORA-00060: deadlock detected while waiting for resource\n\n-- Solution: Always update tables in same order\n-- Both sessions should update employees first, then departments<\/code><\/pre>\n\n\n\n
Monitoring UPDATE 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   UPDATE OPERATION MONITORING\nPROMPT ========================================\nPROMPT \n\nPROMPT 1. Top UPDATE Statements by Elapsed Time\nPROMPT ----------------------------------------\nCOLUMN sql_text FORMAT A60 TRUNCATE\nCOLUMN elapsed_sec FORMAT 999.99\nCOLUMN rows_updated FORMAT 999,999\n\nSELECT sql_id,\n       SUBSTR(sql_text, 1, 60) sql_text,\n       executions,\n       rows_processed rows_updated,\n       ROUND(elapsed_time\/1000000, 2) elapsed_sec,\n       buffer_gets\nFROM v$sql\nWHERE UPPER(sql_text) LIKE '%UPDATE%'\n  AND sql_text NOT LIKE '%v$sql%'\n  AND executions > 0\nORDER BY elapsed_time DESC\nFETCH FIRST 10 ROWS ONLY;\n\nPROMPT \nPROMPT 2. Undo Usage by Session\nPROMPT ----------------------------------------\nCOLUMN username FORMAT A15\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\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr\nORDER BY undo_mb DESC;\n\nPROMPT \nPROMPT 3. Locked Objects\nPROMPT ----------------------------------------\nCOLUMN object_name FORMAT A30\n\nSELECT o.object_name,\n       s.username,\n       s.sid,\n       s.blocking_session\nFROM v$locked_object lo\nJOIN dba_objects o ON lo.object_id = o.object_id\nJOIN v$session s ON lo.session_id = s.sid;\n\nPROMPT \nPROMPT 4. Buffer Cache Hit Ratio\nPROMPT ----------------------------------------\n\nSELECT ROUND((1 - (phy.value \/ (bg.value + cg.value))) * 100, 2) || '%' \n       AS hit_ratio\nFROM v$sysstat phy, v$sysstat bg, v$sysstat cg\nWHERE phy.name = 'physical reads'\n  AND bg.name = 'db block gets'\n  AND cg.name = 'consistent gets';<\/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: Updates ALL rows\nUPDATE employees SET salary = 50000;\n\n-- Safe: Updates specific rows\nUPDATE employees SET salary = 50000 WHERE department_id = 10;\n\n-- Safest: Verify before update\nSELECT COUNT(*) FROM employees WHERE department_id = 10;\n-- Check count, then update<\/code><\/pre>\n\n\n\n
2. Use Bind Variables<\/h3>\n\n\n\n
-- Bad\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\nUPDATE employees SET salary = 65000 WHERE employee_id = 102;\n\n-- Good\nUPDATE employees SET salary = :sal WHERE employee_id = :emp_id;<\/code><\/pre>\n\n\n\n
3. Index WHERE Clause Columns<\/h3>\n\n\n\n
-- Ensure indexes exist on frequently used WHERE columns\nCREATE INDEX emp_dept_idx ON employees(department_id);\nCREATE INDEX emp_status_idx ON employees(status);<\/code><\/pre>\n\n\n\n
4. Commit Appropriately<\/h3>\n\n\n\n
-- OLTP: Commit after each business transaction\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\nCOMMIT;\n\n-- Batch: Commit in intervals\n-- Every 1000-10000 rows depending on undo size<\/code><\/pre>\n\n\n\n
5. Monitor Long-Running Updates<\/h3>\n\n\n\n
-- Check progress of long-running UPDATE\nSELECT sid, \n       serial#,\n       sofar,\n       totalwork,\n       ROUND(sofar\/totalwork*100, 2) pct_complete\nFROM v$session_longops\nWHERE opname LIKE '%UPDATE%'\n  AND sofar <> totalwork;<\/code><\/pre>\n\n\n\n
Quick Reference: UPDATE Syntax<\/h2>\n\n\n\n
-- Basic UPDATE\nUPDATE table_name SET column = value WHERE condition;\n\n-- Update multiple columns\nUPDATE employees \nSET salary = 60000, department_id = 20 \nWHERE employee_id = 101;\n\n-- Update with subquery\nUPDATE employees \nSET salary = (SELECT AVG(salary) FROM employees WHERE department_id = 10)\nWHERE employee_id = 101;\n\n-- Update with CASE\nUPDATE employees\nSET salary = CASE \n    WHEN department_id = 10 THEN salary * 1.1\n    WHEN department_id = 20 THEN salary * 1.15\n    ELSE salary * 1.05\nEND\nWHERE hire_date < '2020-01-01';\n\n-- Update from another table\nUPDATE employees e\nSET salary = (\n    SELECT s.new_salary \n    FROM salary_updates s \n    WHERE s.employee_id = e.employee_id\n)\nWHERE EXISTS (\n    SELECT 1 FROM salary_updates s \n    WHERE s.employee_id = e.employee_id\n);\n\n-- Update with RETURNING clause\nUPDATE employees \nSET salary = salary * 1.1 \nWHERE employee_id = 101\nRETURNING salary INTO :new_salary;<\/code><\/pre>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Understanding UPDATE statement execution is crucial for Oracle database performance tuning. Key differences from INSERT include the need to locate existing rows, generate more extensive undo records, and potentially wait for locks held by other sessions.<\/p>\n\n\n\n
Key Takeaways:<\/strong><\/p>\n\n\n\n
    \n
  1. WHERE clause is critical<\/strong> – Use indexes for efficient row location<\/li>\n\n\n\n
  2. UPDATE generates more undo than INSERT<\/strong> – Full before image stored<\/li>\n\n\n\n
  3. Lock contention is common<\/strong> – Multiple sessions may wait for same rows<\/li>\n\n\n\n
  4. Only modified columns affect index updates<\/strong> – Unlike INSERT<\/li>\n\n\n\n
  5. Undo space management is crucial<\/strong> – Monitor for long transactions<\/li>\n\n\n\n
  6. Batch updates with commits<\/strong> – Prevent huge transactions<\/li>\n\n\n\n
  7. Use bind variables<\/strong> – Improve parse efficiency<\/li>\n\n\n\n
  8. Monitor blocking sessions<\/strong> – Prevent performance degradation<\/li>\n<\/ol>\n\n\n\n
    By mastering these concepts and using the monitoring scripts provided, you’ll be well-prepared for DBA interviews and equipped to optimize UPDATE performance in production environments!<\/p>\n","protected":false},"excerpt":{"rendered":"
    When you execute UPDATE employees SET salary = 60000 WHERE employee_id = 101;, Oracle performs a sophisticated series of operations involving undo segments, redo logs, locks, and constraints. Understanding this process is essential for database administrators, especially for performance tuning and troubleshooting. In this guide, we’ll explore the complete execution flow of an UPDATE statement […]<\/p>\n","protected":false},"author":1,"featured_media":5436,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5434","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\/5434","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=5434"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5434\/revisions"}],"predecessor-version":[{"id":5435,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5434\/revisions\/5435"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5436"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}