The DELETE That Vanished When DDL Committed It
Orion Distribution's SALES table has 42 million rows, is partitioned by SALE_MONTH, and has a global index on SALE_DATE. At 02:10 a batch developer runs DELETE FROM sales WHERE sale_date < DATE '2024-01-01'; it affects 8.2 million rows and is not committed. Undo usage reaches 91%, and a purge job in another session fails with ORA-00054 when it tries TRUNCATE TABLE sales. At 02:40 the same developer, still in the delete transaction, runs ALTER TABLE sales ADD (region_code VARCHAR2(10) DEFAULT 'NA' NOT NULL); it completes in 40 seconds. At 03:00 the developer issues ROLLBACK and is told rollback complete, but the 8.2 million rows do not return. Queries that filter on SALE_DATE have also slowed since the load.
1. Explain why ROLLBACK did not restore the 8.2 million rows and why the other session's TRUNCATE failed. What transactional rule should the team enforce for batch purges?
Read the worked answer
The DELETE was transactional and uncommitted, so it held row locks and generated undo. The purge job's TRUNCATE needed an exclusive lock on SALES; the uncommitted deletes blocked it, producing ORA-00054. That part is expected concurrency behavior, not a rollback failure.
The rollback surprise comes from DDL. The developer's ALTER TABLE ... ADD ... is a DDL statement. Oracle issues an implicit commit before and after DDL, so the ALTER committed the pending DELETE. By 03:00 there was no delete transaction left to roll back. ROLLBACK reported success because it had nothing to undo, but the 8.2 million rows were already committed as deleted.
The rule should be: never mix a large uncommitted DML transaction with DDL in the same session, and never rely on a later ROLLBACK to undo DDL. Batch purges should commit explicitly at controlled points, or use partition maintenance. Also note that TRUNCATE is itself DDL: even if it had obtained the lock, it would have committed and could not be rolled back, and it would have removed all rows rather than only pre-2024 rows.
2. Compare DELETE, TRUNCATE, and partition drop for removing pre-2024 sales. Recommend a strategy for 8.2 million rows out of 42 million and explain the index implications.
Read the worked answer
DELETE is selective and transactional but generates large undo and redo, holds locks, and can be slow for 8.2 million rows. TRUNCATE TABLE is fast and releases space, but it removes every row and is not selective, so it is wrong here. TRUNCATE PARTITION or DROP PARTITION is selective by partition and is usually the best choice when SALE_MONTH partitions align with the retention boundary.
The recommendation is partition maintenance, not row-by-row DELETE:
ALTER TABLE sales DROP PARTITION p2023_12 UPDATE GLOBAL INDEXES;
Use UPDATE GLOBAL INDEXES when a global index must remain usable; without it, a global index can become unusable after partition maintenance. Better still, design large tables with local indexes so dropping or truncating a partition maintains only that partition's index entries. If the table cannot be reorganized immediately, purge in committed batches, monitor undo, and gather statistics afterward. Also check whether the global index on SALE_DATE is skewed by the mass delete: rebuild or coalesce it and refresh statistics before blaming the query plan.
3. Evaluate the ALTER TABLE that added REGION_CODE with a NOT NULL default to a 42 million row table. Was the 40-second duration reasonable, and what should be done about the slow SALE_DATE queries?
Read the worked answer
On Oracle 11g and later, adding a column with a constant default can be metadata-only: the default is stored in the data dictionary and existing rows return it without a physical update to all 42 million rows. Because the column is also NOT NULL and the default is non-null, existing rows can satisfy the constraint from the dictionary. A 40-second duration is therefore plausible, dominated by the exclusive DDL lock and dictionary work rather than 42 million row updates.
Caveats matter. The statement still needs an exclusive lock and can block or be blocked; it is not free. A non-constant default such as SYSDATE forces row updates. For future inserts, DEFAULT ON NULL is useful when incoming NULLs should become the default. Verify the column definition and test on a copy before repeating in production.
The slow SALE_DATE queries are more likely caused by the mass delete and stale statistics than by the new column. The delete left a sparse, skewed global index and stale optimizer statistics. Gather statistics on SALES and the index, inspect the execution plan, and consider rebuilding or coalescing the index if the deleted range was large. If retention will continue, move to local indexes and partition drop so future purges do not leave the global index fragmented.