1. Query Processing for SQL Updates Galindo-Legaria, Stefani and Waas (Microsoft Corp) Talk for CS 632 by S. Sudarshan

2. Issues in Efficient Processing of Updates • Checking constraints • primary/unique key • check constraints • referential integrity, esp. with cascading actions • Updating primary and secondary indices • Bulk updates vs. individual updates • Not in this paper • Write optimized index structures • Materialized view maintenance

3. Modeling Updates • Delta stream • StreamUpdate operator • issues updates to the storage engine • multiple of these can be cascaded or run serially for a single update

4. General form of update Plan • Read-query provides • for insert/update: values to be inserted/new values for update • for delete/update: locators of tuples to be deleted updated

5. Index Maintenance • Primary access path • B-tree or heap • locator: uniquely identify row, and allow efficient retrieval • For B-tree: index attributes + uniquifier • For heap: physical locator: RID • Secondary indices (non-clustered) • Store locator of record corresp to primary access path • Optionally: included columns at leaf level • (a.k.a. covering indices) • Also: • computed columns • unique declaration

6. Index Maintenance • Primary access path updated first • locator needed for secondary indices • old values needed for secondary index update, obtained from primary access path • copied out into delta table (pre-copy) • Per-row vs Per-Index update plans • Per-row: update all indices corresp. to a row, before processing next row • Per-index: use all rows to update an index, then move to next index • Allows sorting of delta table, to reduce access costs • but requires spooling of delta table

7. Per-Index Maintenance • A single update affects two areas of an index • messes up access order even if delta table is sorted • idea: create two records, one for delete action, one for insert action • called split delta • sort split delta stream by (locator, action) • delete sorts first to avoid spurious uniqueness violations

8. Split Delta

9. Choosing an Update Plan • Per-index plan can be more efficient for a large batch • Sorted order, piggy back other steps such as referential integrity checks • But may be less efficient in other cases due to • overhead of more operators for per-index plan • spooling of delta • Wide vs stacked plans • i.e. separate deltas for each secondary index vs. shared delta pipelined through many operators • wide seems better since it can prune unnecessary columns • Cache effects: when you have a choice of index for selection, may be better to use one that is going to be updated

10. Checking Single Table Constraints • Only those that may be affected by update are checked • exception/rollback on failure

11. Referential Integrity Constraints • Updates/deletes on referenced side • Inserts/updates on referencing side • Use outerjoin of delta rows with other table • ensure match/lack of match depending on updated side being referencing/referenced • Checks performed after updating the table • But for updates to referenced table, old values must be used • May require index on attributes of referencing table • index on referenced table already present normally

12. Checking Referential Integrity

13. Referential Integrity (Cont.) • When checking self-referential integrity constraints, perform all updates done by a DML statement, only then check • Why? • If there is an index on f.k. attrs, integrity check can be combined with index maintenance, since both need same sort order • place ref integrity check operator just above index maintenance • Another optimization: eliminate duplicates

14. Cascading Updates • E.g. S has f.k. referencing R, on update cascade • Update to p.k. of tuple in R cascades to S • Similar to f.k. checking, but instead of validating, update referencing relation(s) • to do so, create delta table, and use update plan to process delta table

15. Cascading Updates

16. Issues not addressed by paper • All optimizations are for a single transaction • What if there is a sequence of small transactions? • Insert/update/delete to large B-tree may require 1 I/O per update to leaf page • even assuming internal pages are in memory • Solution: write-optimized B-trees • collect deltas and apply as a batch • but ensure queries are correctly answered meanwhile, even if more slowly • Several schemes including work at IITB in 1997, and a nice survey paper by Graefe with some neat implementation ideas