Ch23b_Views-95 - 1 Database Management Systems, 2 nd...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 1 Data Warehousing and Decision Support Chapter 23, Part B Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 2 Views and Decision Support ¡ OLAP queries are typically aggregate queries. ¢ Precomputation is essential for interactive response times. ¢ The CUBE is in fact a collection of aggregate queries, and precomputation is especially important: lots of work on what is best to precompute given a limited amount of space to store precomputed results. ¡ Warehouses can be thought of as a collection of asynchronously replicated tables and periodically maintained views. ¢ Has renewed interest in view maintenance! Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 3 View Modification (Evaluate On Demand) CREATE VIEW RegionalSales (category,sales,state) AS SELECT P.category, S.sales, L.state FROM Products P, Sales S, Locations L WHERE P.pid=S.pid AND S.locid=L.locid SELECT R.category, R.state, SUM(R.sales) FROM RegionalSales AS R GROUP BY R.category, R.state SELECT R.category, R.state, SUM(R.sales) FROM ( SELECT P.category, S.sales, L.state FROM Products P, Sales S, Locations L WHERE P.pid=S.pid AND S.locid=L.locid ) AS R GROUP BY R.category, R.state View Query Modified Query 2 Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 4 View Materialization (Precomputation) ¡ Suppose we precompute RegionalSales and store it with a clustered B+ tree index on [category,state,sales]. ¢ Then, previous query can be answered by an index- only scan. SELECT R.state, SUM(R.sales) FROM RegionalSales R WHERE R.category=“Laptop” GROUP BY R.state SELECT R.state, SUM(R.sales) FROM RegionalSales R WHERE R. state=“Wisconsin” GROUP BY R.category Index on precomputed view is great! Index is less useful (must scan entire leaf level). Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 5 Materialized Views ¡ A view whose tuples are stored in the database is said to be materialized . ¢ Provides fast access, like a (very high-level) cache....
View Full Document

Page1 / 6

Ch23b_Views-95 - 1 Database Management Systems, 2 nd...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online