Introduction To Data Management CSE 344

Introduction to Data ManagementCSE 344Lecture 19: ViewsCSE 344 - Fall 20141

Announcements Midterm will be graded over the weekend Next web quiz and homework covering designtheory, FDs, normalization, etc. posted now. Duenext Tuesday and Thursday. Looking ahead: final exam is Monday, Dec. 8.Do we want a review session the previous day?If so, when? Today: Views

Views A view in SQL – A table computed from other tables, s.t., wheneverthe base tables are updated, the view is updatedtoo More generally:– A view is derived data that keeps track of changesin the original data Compare:– A function computes a value from other values,but does not keep track of changes to the inputs

Purchase(customer, product, store)Product(pname, price)StorePrice(store, price)A Simple ViewCreate a view that returns for each storethe prices of products purchased at that storeCREATE VIEW StorePrice ASSELECT DISTINCT x.store, y.priceFROM Purchase x, Product yWHERE x.product y.pnameThis is like a new tableStorePrice(store,price)CSE 344 - Fall 201412

Purchase(customer, product, store)Product(pname, price)StorePrice(store, price)We Use a View Like Any Table A "high end" store is a store that sell some productsover 1000. For each customer, return all the high end stores thatthey visit.SELECT DISTINCT u.customer, u.storeFROM Purchase u, StorePrice vWHERE u.store v.storeAND v.price 1000CSE 344 - Fall 201413

Types of Views Virtual views– Used in databases– Computed only on-demand – slow at runtime– Always up to date Materialized views––––Used in data warehousesPre-computed offline – fast at runtimeMay have stale data (must recompute or update)Indexes are materialized views A key component of physical tuning of databases isthe selection of materialized views and indexes14

Purchase(customer, product, store)Product(pname, price)StorePrice(store, price)Query ModificationFor each customer, find all the high end stores that they visit.CREATE VIEW StorePrice ASSELECT DISTINCT x.store, y.priceFROM Purchase x, Product yWHERE x.product y.pnameSELECT DISTINCT u.customer, u.storeFROM Purchase u, StorePrice vWHERE u.store v.storeAND v.price 1000CSE 344 - Fall 201415

Purchase(customer, product, store)Product(pname, price)StorePrice(store, price)Query ModificationFor each customer, find all the high end stores that they visit.CREATE VIEW StorePrice ASSELECT DISTINCT x.store, y.priceFROM Purchase x, Product yWHERE x.product y.pnameModified query:SELECT DISTINCT u.customer, u.storeFROM Purchase u, StorePrice vSELECT DISTINCT u.customer, u.storeWHERE u.store v.storeFROM Purchase u,AND v.price 1000(SELECT DISTINCT x.store, y.priceFROM Purchase x, Product yWHERE x.product y.pname) vWHERE u.store v.storeAND v.price 1000CSE 344 - Fall 201416

Purchase(customer, product, store)Product(pname, price)StorePrice(store, price)Query ModificationFor each customer, find all the high end stores that they visit.Notice thatSELECT DISTINCT u.customer, u.storePurchaseFROM Purchase u, Purchase x, Product yoccurs twice.WHERE u.store x.storeWhy?AND y.price 1000AND x.product y.pnameModifiedquery:Modified and unnested query: SELECT DISTINCT u.customer, u.storeFROM Purchase u,(SELECT DISTINCT x.store, y.priceFROM Purchase x, Product yWHERE x.product y.pname) vWHERE u.store v.storeAND v.price 1000CSE 344 - Fall 201417

Purchase(customer, product, store)Product(pname, price)StorePrice(store, price)Further Virtual View OptimizationRetrieve all stores whose name contains ACMECREATE VIEW StorePrice ASSELECT DISTINCT x.store, y.priceFROM Purchase x, Product yWHERE x.product y.pnameSELECT DISTINCT v.storeFROM StorePrice vWHERE v.store like ‘%ACME%’CSE 344 - Fall 201418

Purchase(customer, product, store)Product(pname, price)StorePrice(store, price)Further Virtual View OptimizationRetrieve all stores whose name contains ACMECREATE VIEW StorePrice ASSELECT DISTINCT x.store, y.priceFROM Purchase x, Product yWHERE x.product y.pnameSELECT DISTINCT v.storeFROM StorePrice vWHERE v.store like ‘%ACME%’Modified query:SELECT DISTINCT v.storeFROM(SELECT DISTINCT x.store, y.priceFROM Purchase x, Product yWHERE x.product y.pname) vWHERE v.store like ‘%ACME%’CSE 344 - Fall 201419

Purchase(customer, product, store)Product(pname, price)StorePrice(store, price)Further Virtual View OptimizationRetrieve all stores whose name contains ACMESELECT DISTINCT x.storeFROM Purchase x, Product yWHERE x.product y.pnameAND x.store like ‘%ACME%’We can further optimize! How?Modified query:Modified and unnested query: SELECT DISTINCT v.storeFROM(SELECT DISTINCT x.store, y.priceFROM Purchase x, Product yWHERE x.product y.pname) vWHERE v.store like ‘%ACME%’CSE 344 - Fall 201420

Purchase(customer, product, store)Product(pname, price)StorePrice(store, price)Further Virtual View OptimizationRetrieve all stores whose name contains ACMESELECT DISTINCT x.storeFROM Purchase x, Product yWHERE x.product y.pnameAND x.store like ‘%ACME%’Modified and unnested query:Assuming Product.pname is a keyand Purchase.product is a foreign keyFinal QuerySELECT DISTINCT x.storeFROM Purchase xWHERE x.store like ‘%ACME%’CSE 344 - Fall 201421

Applications of Virtual Views Increased physical data independence. E.g.– Vertical data partitioning– Horizontal data partitioning Logical data independence. E.g.– Change schemas of base relations (i.e., stored tables) Security– View reveals only what the users are allowed to knowCSE 344 - Fall 201422

Vertical PartitioningResumes AddressHustonSeattleSeattlePortlandResumeClob1 Clob2 Clob3 Clob4 T2Name AddressSSNPictureBlob1 Blob2 Blob3 Blob4 T3ResumeSSNPicture234234 MaryHuston234234 Clob1 234234Blob1 345345 SueSeattle345345 Clob2 345345Blob2 .T2.SSN is a key and a foreign key to T1.SSN. Same for T3.SSN23

esumes(ssn,name,address,resume,picture)Vertical PartitioningCREATE VIEW Resumes ASSELECT T1.ssn, T1.name, T1.address,T2.resume, T3.pictureFROM T1,T2,T3WHERE T1.ssn T2.ssn AND T1.ssn T3.ssnCSE 344 - Fall 201424

esumes(ssn,name,address,resume,picture)Vertical PartitioningCREATE VIEW Resumes ASSELECT T1.ssn, T1.name, T1.address,T2.resume, T3.pictureFROM T1,T2,T3WHERE T1.ssn T2.ssn AND T1.ssn T3.ssnSELECT addressFROM ResumesWHERE name ‘Sue’CSE 344 - Fall 201425

esumes(ssn,name,address,resume,picture)Vertical PartitioningCREATE VIEW Resumes ASSELECT T1.ssn, T1.name, T1.address,T2.resume, T3.pictureFROM T1,T2,T3WHERE T1.ssn T2.ssn AND T1.ssn T3.ssnSELECT addressFROM ResumesWHERE name ‘Sue’Modified query:SELECT T1.addressFROM T1, T2, T3WHERE T1.name ‘Sue’AND T1.SSN T2.SSNAND T1.SSN T3.SSN

esumes(ssn,name,address,resume,picture)Vertical PartitioningCREATE VIEW Resumes ASSELECT T1.ssn, T1.name, T1.address,T2.resume, T3.pictureFROM T1,T2,T3WHERE T1.ssn T2.ssn AND T1.ssn T3.ssnSELECT addressFROM ResumesWHERE name ‘Sue’Final query:SELECT T1.addressFROM T1WHERE T1.name ‘Sue’Modified query:SELECT T1.addressFROM T1, T2, T3WHERE T1.name ‘Sue’AND T1.SSN T2.SSNAND T1.SSN T3.SSN

Vertical Partitioning Applications1. Advantages– Speeds up queries that touch only a small fraction of columns– Single column can be compressed effectively, reducing disk I/O2. Disadvantages– Updates are expensive!– Need many joins to access many columns– Repeated key columns add overheadHot trend today for data analytics: e.g., Vertica startup acquired by HPThey use a highly-tuned column-oriented data store AND engine28

Horizontal 234234MaryHoustonSSNName rsInSeattle.CSE 344 - Fall 201429

l PartitioningCREATE VIEW Customers ASCustomersInHoustonUNION ALLCustomersInSeattleUNION ALL.CSE 344 - Fall 201430

l PartitioningSELECT nameFROM CustomersWHERE city ‘Seattle’Which tables are inspected by the system ?CSE 344 - Fall 201431

l PartitioningSELECT nameFROM CustomersWHERE city ‘Seattle’Which tables are inspected by the system ?All tables!The systems doesn’t know that CustomersInSeattle.city ‘Seattle’CSE 344 - Fall 201432

l PartitioningBetter: remove CustomerInHuston.city etcCREATE VIEW Customers AS(SELECT SSN, name, ‘Houston’ as cityFROM CustomersInHouston)UNION ALL(SELECT SSN, name, ‘Seattle’ as cityFROM CustomersInSeattle)UNION ALL.CSE 344 - Fall 201433

l PartitioningSELECT nameFROM CustomersWHERE city ‘Seattle’SELECT nameFROM CustomersInSeattleCSE 344 - Fall 201434

Horizontal Partitioning Applications Performance optimization– Especially for data warehousing– E.g. one partition per month– E.g. archived applications and active applications Distributed and parallel databases Data integrationCSE 344 - Fall 201435

345345 Sue Seattle 345343 Joan Seattle 234234 Ann Portland -- Frank Calgary -- Jean Montreal Customers SSN Name City 234234 Mary Houston CustomersInHouston SSN Name City 345345 Sue Seattle 345343 Joan Seattle