Query Result Caching Prasan Roy Krithi Ramamritham S

Query Result Caching Prasan Roy, Krithi Ramamritham, S. Seshadri, S. Sudarshan Pradeep Shenoy, Jinesh Vora

Model z. Predictive Caching - use history z. Query results/ intermediate z. Single user stream - very similar queries z. Global sequence of queries - long term patterns z. Leverages off MQO (P. Roy, et. al. )

Issues z. Matching and reuse of cached items z. Choice of items to cache

Matching z Integrated into optimization z Hash-based storage of DAGs and plans z New plans unified with old identical plans z. Cache items chosen in cost based manner

z. Basic idea y. Sharable nodes considered for caching Review ofof. MQO y. Benefit all subsets computed, choose best set z. Greedy heuristic: take highest benefit node at each step z. Several optimizations included

Adaptation z. Characterizing the query workload z. Weighted set of queries - frequency based z. Candidates for caching is varied

new execution plan z. Make greedy choice on this set Local Commonality y. Re-check if old nodes are relevant (cleanup) y. Any nodes in current plan worth caching? (scavenge) z. Metric: benefit to representative set.

z. DAG is small - no long term patterns Disadvantage z. Candidate set is small - only local minimum y similar to "quick-and-dirty" method Volcano-RU

z. Large DAG, full-scale MQO z. Candidate set includes all sharable Global Commonality nodes z. Extended-predictive: no immediate caching ycompute and materialize during slack time y cache on first use

Status y. In Progress!