1 views, indexes virtual and materialized views speeding accesses to data
TRANSCRIPT
![Page 1: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/1.jpg)
1
Views, Indexes
Virtual and Materialized ViewsSpeeding Accesses to Data
![Page 2: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/2.jpg)
2
Views
A view is a relation defined in terms of stored tables (called base tables ) and other views.
Two kinds:1. Virtual = not stored in the
database; just a query for constructing the relation.
2. Materialized = actually constructed and stored.
![Page 3: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/3.jpg)
3
Declaring Views
Declare by:CREATE [MATERIALIZED] VIEW <name> AS <query>;
Default is virtual.
![Page 4: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/4.jpg)
4
Example: View Definition
CanDrink(drinker, beer) is a view “containing” the drinker-beer pairs such that the drinker frequents at least one bar that serves the beer:
CREATE VIEW CanDrink AS
SELECT drinker, beer
FROM Frequents, Sells
WHERE Frequents.bar = Sells.bar;
![Page 5: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/5.jpg)
5
Example: Accessing a View
Query a view as if it were a base table. Also: a limited ability to modify views
if it makes sense as a modification of one underlying base table.
Example query:SELECT beer FROM CanDrink
WHERE drinker = ’Sally’;
![Page 6: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/6.jpg)
6
Triggers on Views
Generally, it is impossible to modify a virtual view, because it doesn’t exist.
But an INSTEAD OF trigger lets us interpret view modifications in a way that makes sense.
Example: View Synergy has (drinker, beer, bar) triples such that the bar serves the beer, the drinker frequents the bar and likes the beer.
![Page 7: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/7.jpg)
7
Example: The View
CREATE VIEW Synergy ASSELECT Likes.drinker, Likes.beer, Sells.barFROM Likes, Sells, FrequentsWHERE Likes.drinker = Frequents.drinker
AND Likes.beer = Sells.beerAND Sells.bar = Frequents.bar;Natural join of Likes,
Sells, and Frequents
Pick one copy ofeach attribute
![Page 8: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/8.jpg)
8
Interpreting a View Insertion
We cannot insert into Synergy --- it is a virtual view.
But we can use an INSTEAD OF trigger to turn a (drinker, beer, bar) triple into three insertions of projected pairs, one for each of Likes, Sells, and Frequents. Sells.price will have to be NULL.
![Page 9: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/9.jpg)
9
The Trigger
CREATE TRIGGER ViewTrigINSTEAD OF INSERT ON SynergyREFERENCING NEW ROW AS nFOR EACH ROWBEGIN
INSERT INTO LIKES VALUES(n.drinker, n.beer);INSERT INTO SELLS(bar, beer) VALUES(n.bar, n.beer);INSERT INTO FREQUENTS VALUES(n.drinker, n.bar);
END;
![Page 10: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/10.jpg)
10
Materialized Views
Problem: each time a base table changes, the materialized view may change. Cannot afford to recompute the view
with each change. Solution: Periodic reconstruction of
the materialized view, which is otherwise “out of date.”
![Page 11: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/11.jpg)
11
Example: A Data Warehouse
Wal-Mart stores every sale at every store in a database.
Overnight, the sales for the day are used to update a data warehouse = materialized views of the sales.
The warehouse is used by analysts to predict trends and move goods to where they are selling best.
![Page 12: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/12.jpg)
12
Indexes
Index = data structure used to speed access to tuples of a relation, given values of one or more attributes.
Could be a hash table, but in a DBMS it is always a balanced search tree with giant nodes (a full disk page) called a B-tree.
![Page 13: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/13.jpg)
13
Declaring Indexes
No standard! Typical syntax:CREATE INDEX BeerInd ON Beers(manf);
CREATE INDEX SellInd ON Sells(bar, beer);
![Page 14: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/14.jpg)
14
Using Indexes
Given a value v, the index takes us to only those tuples that have v in the attribute(s) of the index.
Example: use BeerInd and SellInd to find the prices of beers manufactured by Pete’s and sold by Joe. (next slide)
![Page 15: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/15.jpg)
15
Using Indexes --- (2)
SELECT price FROM Beers, Sells
WHERE manf = ’Pete’’s’ AND
Beers.name = Sells.beer AND
bar = ’Joe’’s Bar’;
1. Use BeerInd to get all the beers made by Pete’s.
2. Then use SellInd to get prices of those beers, with bar = ’Joe’’s Bar’
![Page 16: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/16.jpg)
16
Database Tuning
A major problem in making a database run fast is deciding which indexes to create.
Pro: An index speeds up queries that can use it.
Con: An index slows down all modifications on its relation because the index must be modified too.
![Page 17: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/17.jpg)
17
Example: Tuning
Suppose the only things we did with our beers database was:
1. Insert new facts into a relation (10%).2. Find the price of a given beer at a
given bar (90%). Then SellInd on Sells(bar, beer)
would be wonderful, but BeerInd on Beers(manf) would be harmful.
![Page 18: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/18.jpg)
18
Tuning Advisors
A major research thrust. Because hand tuning is so hard.
An advisor gets a query load, e.g.:1. Choose random queries from the
history of queries run on the database, or
2. Designer provides a sample workload.
![Page 19: 1 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data](https://reader036.vdocuments.us/reader036/viewer/2022082422/5697bfea1a28abf838cb71ca/html5/thumbnails/19.jpg)
19
Tuning Advisors --- (2)
The advisor generates candidate indexes and evaluates each on the workload. Feed each sample query to the query
optimizer, which assumes only this one index is available.
Measure the improvement/degradation in the average running time of the queries.