Answering Queries Using Views

1
Answering Queries Using Views
Advanced DB Class Presented by David Fuhry March
9, 2006
2
Presentation Outline

• Introduction to views
• Where views are used
• How a database processes views
• Query equivalence and containment
• Using views to solve queries
• Means of optimizing the above
• System-R, Transformational

3
What is a View?

• A named query Hal0?
• Virtual or logical table composed of the result
  set of a query Wik06
• Any relation that is not a part of the logical
  model, but is made visible to a user as a visual
  relation SKS02

4
An Example View

• CREATE VIEW CHEAP_HOTELS AS
• SELECT Hotel_name, Distance FROM HOTELS WHERE
  Price lt 250

5
Presentation Outline

• Introduction to views
• Where views are used
• How a database processes views
• Query equivalence and containment
• Using views to solve queries
• Means of optimizing the above
• System-R, Transformational

6
Where are views used?

• Query Optimization DB Design
• Significant performance gain (if materialized)
• Logical perspective of physical data
• Data Integration
• Provide common query interface to non-uniform
  data sources
• Query -gt Mediated Schema -gt Source Descriptor -gt
  Source Data

7
When might I use a view?

• Organize the data to be presented by a screen or
  page of an application
• Secure a protected global table by making only
  parts of it visible to users
• Reduce size of query statement
• As do stored procedures and prepared statements
• Views integrate into SQL expressions more easily
  though

8
When else might I use a view?

• Result set is too large to exist on disk
• Frequent itemsets when the number of items is
  realistically large
• I can only access chunks of the data at a time
• Web screen scraping of detail pages

9
Presentation Outline

• Introduction to views
• Where views are used
• How a database processes views
• Query equivalence and containment
• Using views to solve queries
• Means of optimizing the above
• System-R, Transformational

10
How does the database process views?

• SELECT FROM CHEAP_HOTELS

SELECT Hotel_name, Distance FROM HOTELS WHERE
Price lt 250
SELECT Hotel_name from CHEAP_HOTELS WHERE
Distance gt 0.3
SELECT Hotel_name FROM HOTELS WHERE Distance gt
0.3 AND Price lt 250
11
Data Integration

• Searching a website
• SELECT page_title, url FROM site_index WHERE
  MATCH (title, body) AGAINST ('hotels -small')
• But no need to use a view in the above
  materialize it into a table and update it on a
  regular interval

12
Data Integration

• SELECT page_title, url FROM internet WHERE MATCH
  (title, body) AGAINST ('hotels -small') LIMIT 10
• Pretty much impossible to materialize 'internet'
• Not too difficult if you employ a search engine's
  API and make 'internet' a view of their cache

13
Answering queries with views

• Physical data independence
• Normal RDBMS views

• Data integration
• Ex search tools that parse multiple formats

14
Presentation Outline

• Introduction to views
• Where views are used
• How a database processes views
• Query equivalence and containment
• Using views to solve queries
• Means of optimizing the above
• System-R, Transformational

15
Query Containment

• Q1 Q2 if the tuples (rows) returned by Q1 are a
  subset of those returned by Q2
• Q1 is contained in Q2

Q1
Q2
SELECT Hotel_name, Price, Distance FROM hotels
WHERE Price lt 240
SELECT Hotel_name, Price, Distance FROM hotels
WHERE Price lt 400
In the above case Q1 Q2
16
Query Equivalence

• Q1 and Q2 are equivalent if Q1 Q2 and Q2 Q1

Q1
Q2
SELECT Hotel_name, Price, Distance FROM hotels
WHERE Distance gt 0.3
SELECT Hotel_name, Price, Distance FROM hotels
WHERE Distance BETWEEN(0.3, MAX_FLOAT)
17
Presentation Outline

• Introduction to views
• Where views are used
• How a database processes views
• Query equivalence and containment
• Using views to solve queries
• Means of optimizing the above
• System-R, Transformational

18
When can a view be useful for solving part of a
query?

• If it has relation(s) in common with the query
  and selects some attributes selected by the query

US_Hotels
Hawaii_Buildings
Norwegian_Beagles
Jordanian_Hotels
19
Grouping and aggregation

• How useful can views with grouping or aggregation
  be in solving the query?
• If the view uses weaker predicates than the
  query, very useful
• If the view uses stronger predicates, then
  perhaps as a subset of the results

20
Grouping and aggregation
Rooms
Price
Distance
Adapted from Essbase Database Administrator's
Guide Understanding Multidimensional Databases
21
Presentation Outline

• Introduction to views
• Where views are used
• How a database processes views
• Query equivalence and containment
• Using views to solve queries
• Means of optimizing the above
• System-R, Transformational

22
Query Optimization

• For optimization purposes, views tables to
  abstract logic
• Normally we want an equivalent rewriting
• For data integration, we may only want a
  contained rewriting
• Although millions match, just get top 10 search
  results

23
Problem Statement

• How can we use more efficiently answer queries
  using a predefined set of materialized views?

24
Efficiently answering a query

• Suppose a query like the following is being run
  very often
• SELECT attr1, attr2, ..., attrN FROM t1INNER
  JOIN t2 ON t1.some_attr t2.id...OUTER JOIN tM
  ON t1.other_attr tM.id
• Lots of JOINs.
• M tables must be joined. The operation will be
  expensive.
• Can we do better? (Hint yes)

25
Efficiently answering a query
M Source Tables
Result Set
How can database systems determine which (if any)
materialized views to use to solve the query?
26
Query Optimization Techinques

• Here are a few techniques
• Bottom-up (System-R style)
• Transformational
• Other

27
Presentation Outline

• Introduction to views
• Where views are used
• How a database processes views
• Query equivalence and containment
• Using views to solve queries
• Means of optimizing the above
• System-R, Transformational

28
System-R style optimization

• Identify potentially useful views
• Termination testing
• Pruning of plans
• Combining partial plans

29
System-R style optimization

• Identify potentially useful views
• Here is where we use the concepts of query
  containment and equivalence discussed earlier
• But to recap A view can be useful for a query
  if the set of relations it mentions overlaps with
  that of the query, and it selects some of the
  attributes selected by the query

30
System-R style optimization

• Termination testing
• Differentiate partial query plans from complete
  query plans
• Enumerate possible join orders and explore all
  partial paths

Source Tables
Result Set
Materialized Views
31
System-R style optimization

• Pruning of plans
• A plan is pruned if a cheaper plan exists which
  contains it

Plan 1 Cost 25
Plan 0Cost 30
Plan 2 Cost 18
32
System-R style optimization

• Combining partial plans
• Consider different possible ways of joining the
  views
• Use dynamic programming
• To solve optimal plan for Join(A, B, C, D),
  find optimal (cheapest) plan among
• Join(A, B, C, D)
• Join(A, B, D, C)
• Join(A, C, D, B)
• Join(B, C, D, A)
• Use recursion to solve
• Discard the other three

33
System-R style optimization
Source An overview of Query Optimization in
Relational Systems Chaudhuri, Surajit
34
Presentation Outline

• Introduction to views
• Where views are used
• How a database processes views
• Query equivalence and containment
• Using views to solve queries
• Means of optimizing the above
• System-R, Transformational

35
Transformational query rewriting

• Top-down approach
• Cache materialized view metadata
• Relations the view is composed of
• Columns the view covers
• Groupings the view applies
• etc.
• Build a multiway search tree out of all views'
  metadata
• It partitions the views by the above attributes
• Idea is to reject irrelevant views quickly

36
A Filter Tree
Source table condition Hub condition Output
column condition Grouping columns Range
constrained columns Residual predicate
condition Output expression condition Grouping
expression condition
...
Leaf nodes point to sets of relevant views
V1,V3
V7,V9,V10
37
Other types of view rewriting

• Query Graph Model (QGM)
• Split query into multiple boxes, and try to match
  the view's boxes with the query's

38
References

• Hal0? A.Y. Halevy. Answering Queries Using
  Views A Survey. VLDB Journal, 10(4).
• Ull97 Jeffrey D. Ullman. Information
  Integration Using Logical Views. ICDT 1997.
• Wik06 Wikipedia contributors (2006). View
  (database). Wikipedia, The Free Encyclopedia
• SKS02 Silbershatz, Korth, Sudarshan. Database
  System Concepts, 4th Ed. 2002. (100)
• JL01 Jonathan Goldstein and Per-Ake Larson.
  Optimizing queries using materialized views a
  practical, scalable solution. In Proc. Of SIGMOD,
  pages 331-342, 2001.
• Ess06 IBM Corp. Essbase Analytic Services
  Database Administrator's Guide. Understanding
  Multidimensional Databases

39
Recap

• Introduction to views
• Where views are used
• How a database processes views
• Query equivalence and containment
• Using views to solve queries
• Means of optimizing the above
• System-R, Transformational

40
Appendix (Misc. Slides)
41
Are tables views?

• Maybe yes

• Maybe no
• Views aren't supposed to contain concrete data or
  take up space.

Physical Representation
Logical Representation