View Materialization

1
View Materialization

• Hyoung-Gon Lee
• MAI Lab. Seminar
• 2005.2.2

2
Table of Contents

• 1. Concept of View View Materialization
• - Fundamentals of Database System Third
  Edition, Elmasri, Chapter 8.5
• 2. Materialization Strategy
• Optimization of Materialization Strategies for
  Derived Data Elements, David Botzer and Opher
  Etizion, IEEE Transactions on Knowledge and Data
  Engineering, Vol. 8, No. 2, April 1996. pp.
  260272
• 3. Research Idea (KERP-DB)

3
1. Concept of View View Materialization

• 1.1 Concept of a View in SQL
• 1.2 Specification of Views in SQL
• 1.3 View Implementation (and View Update
  excepted)
• Query Modification
• View Materialization

• Ramez A.Elmasri Shamkant Navathe,
• Dept. of CS Eng at the Univ. of Texas at
  Arlington, USA
• Database research group in the College of
  Computing at the GIT, USA
• Fundamentals of Database Systems (3rd E),
  Chapter 8.5 - Views in SQL, pp. 278 282

4
1.1 Concept of a View in SQL
1. Concept of View View Materialization

• Concept of a View
• A single table that is derived from other tables.
• A view does not necessarily exist in physical
  form it is considered a virtual table.
• We can think of a view as a way of specifying a
  table that we need to reference frequently.
• ex) COMPANY DB

EMPLOYEE
FNAME MINT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO
DEPARTMENT
DEPT_LOCATIONS
DNAME DNUMBER MGRSSN MGRSTARTDATE
DNUMBER DLOCATION
PROJECT
WORKS_ON
PNAME PNUMBER PLOCATION DNUM
ESSN PNO HOURS
DEPENDENT
ESSN DEPENDENT_NAME SEX BDATE RELATIONSHIP
5
1.2 Specification of Views in SQL
1. Concept of View View Materialization

• Create View

V1 CREATE VIEW AS SELECT FROM WHERE WORKS_ON1 FNAME, LNAME, PNAME, HOURS EMPLOYEE, PROJECT, WORKS_ON SSNESSN AND PNOPNUMBER
V2 CREATE VIEW AS SELECT FROM WHERE GROUP BY DEPT_INFO(DEPT_NAME, NO_OF_ EMPS, TOTAL_SAL) DNAME, COUNT(), SUM(SALARY) DEPARTMENT, EMPLOYEE DNUMBERDNO DNAME
WORKS_ON1
DEPT_INFO
FNAME LNAME PNAME HOURS
DEPT_NAME NO_OF_EMPS TOTAL_SAL
6
1.3 View Implementation
1. Concept of View View Materialization

• The problem of efficiently implementing a view
  for querying is complex. Two main approaches have
  been suggested.
• 1) Query modification
• - Modifying the view query into a query on
  the underlying base tables.
• - drawback complex queries, time
  consuming.
• 2) View materialization
• - Physically creating a temporary view table
  when the view is first queried and keeping that
  table on the assumption that other queries on the
  view will follow.
• - An efficient strategy for automatically
  updating the view table when the base tables are
  updated must be developed in order to keep the
  view up to date.

7
2. Materialization Strategy- Optimization of
Materialization Strategies for Derived Data
Elements
2.1 Introduction and Motivation 2.2 The
Optimization Algorithm 2.3 The Optimization
Model 2.4 Some Experimental Results 2.5 Conclusion

• David Botzer and Opher Etzion
• Dep. of Information Systems Engineering,
  Industrial Engineering and Management,
  Technion-Israel Institute of Technology, Israel
• IEEE Transactions on Knowledge and Data
  Engineering, Vol. 8, No. 2, pp. 260-272, 1996

8
2.1 Introduction and Motivation
2. Materialization Strategy

• Research issues in materialization of derived
  data elements
• IF issue whether to physically store derived
  data elements
• HOW issue defining derivation rules
• WHEN issue choosing a point when to derive
• lt it has been neglected in database
  research
• An example of an attribute value derivation
• Salary Base-Salary Bonus
  Professional-Increment
• Decision problems handled in this paper
• 1) Should an update operation to a PDI instance
  be triggered by modifications of any of its
  derivers? Example Should an update of the
  Professional-Increment for a given profession
  trigger the re-calculation of the Salary for each
  employee that belongs to this profession?
• 2) If the answer to the first decision is
  positive then
• a) Should the PDI be updated synchronously
  with respect to its derivers?
• b) Should the consistency of a PDI with
  respect to its derivers be guaranteed by the
  DBMS?

9
2.1 Introduction and Motivation
2. Materialization Strategy

• The first decision problem yields three possible
  materialization modes
• Active mode The values of PDI instances should
  be updated by operations that are triggered as a
  part of any update transaction of a derivers
  instance.
• Passive mode Each PDI instance is virtual. It
  is recalculated any time that it is required.
• Semiactive mode To execute the actual update
  when the first retrieval request for this
  PDI-instance occurs.
• When the materialization mode of a PDI is active,
  then there is a second decision that should be
  made.
• Fully Consistent Mode If a PDI is fully
  consistent, its consistency with respect to its
  derivers is guaranteed by the DBMS at all times.
• The Quasiconsistent Mode The idea of
  quasiconsistency stems from the relaxation of the
  transaction atomicity in order to enable
  asynchronous execution of subtransactions.
• The Loosely Consistent Mode The loosely
  consistent mode applies in cases, where
  maintaining the PDIs consistency is desirable,
  but no action is taken if it is violated.
• Five combinations are possible for each PDI
• passive, semiactive, active-fully
  consistent, active-quasiconsistent,
  active-loosely consistent

10
2.1 Introduction and Motivation
2. Materialization Strategy

• Motivation
• To provide database administrators with a tool to
  assist in getting tuning decisions that can be
  based upon an applications constraints
• A Case Study Project cost planning application

Activity Cost Activity-Estimated-Cost1.5 Res
ources-Cost Resources-Estimated-Cost1.2 Projec
t-Labor-Cost sum(Activity-Cost) Branch
-Total-Cost Branch-Labor-Cost Branch-

Resource-Value
Class Branch Branch-Name
Branch-Address
Class Department Department-Name
Branch-Affiliation
Class Project Project-Name
Department-Affiliation
Class Activity Activity-Name
Project-Affiliation
Fig. Derivations definitions
Fig. The schema
11
2.1 Introduction and Motivation
2. Materialization Strategy
Fig. The derivation graph
Fig. Topological order of dependencies
12
2.2 The Optimization Algorithm
2. Materialization Strategy

• Definitions required for algorithm assumptions
• 1) The transitive relation weaker, denoted as ltv
  orders the materialization in the following total
  order
• ltpassive ltv semiactive ltv activelossely
  consistent ltv activequasiconsistent ltv
  activefully consistentgt
• 2) msa, msb, msab are feasible materialization
  strategies, in which each element is a
  materialization of a single PDI in the database
  (a member of ?).
• 3) m1, m2, , mn are materializations of all the
  PDIs in ?.
• 4) msa differs from msb in exactly two
  materialization values. msab differs from both
  msa, msb in a single materialization value. All
  other materialization values are equal.
• 5) Za, Zb, Zab are values of the goal function
  defined for msa, msb, msab.

Fig. possible scenario
13
2.2 The Optimization Algorithm
2. Materialization Strategy

• Two major assumptions
• 1) Assumption AS1
• Let a data element d be a deriver of a PDI p,
  the materialization of d cannot be weaker than
  the materialization of p.
• 2) Assumption AS2

Fig. possible scenario
14
2.2 The Optimization Algorithm
2. Materialization Strategy

• The Algorithms Formalization

15
2.3 The Optimization Model
2. Materialization Strategy

• Optimizer
• A utility program in the DBMS package.
• It accepts a goal function and its associated
  parameters and uses the optimization algorithm
  discussed above to propose a materialization
  strategy.
• Life-cycle of materialization strategies

16
2.3 The Optimization Model
2. Materialization Strategy

• The Goal Function

17
2.3 The Optimization Model
2. Materialization Strategy

• The Goal Function

18
2.4 Some Experimental Results
2. Materialization Strategy

• Update Frequency Analysis

• Computation Cost Analysis

19
2.5 Conclusion
2. Materialization Strategy

• We have failed to produce a good predictor for
  the optimal materialization strategy.
• Consequently, without the optimization model it
  is difficult to predict the optimal
  materialization strategy even if we leave all the
  parameters but one as constants and trying to
  find such predictor as a function of any single
  parameter.
• It is desirable to obtain the optimal solution,
  due to the fact that the optimization model
  substantially improves(reduces) the goal function
  value in most cases relative to the two universal
  strategies.

20
KERP-DB

• ????
• ???? ??
• 3.DB Performance ????

21
???? vs. ????.
3. Research Idea

• IBM??? ?? ??? ?? ??? ?? part explosion
• ??? 2? ?? ??.
• gt part explosion(MRP), resource
  allocation(CRP), Inventory Record, Cost?? ????
  ????? ?.
• ???? DB ??? ??? ?? MRP?? ?? ??
• gt Generative BOM, Modular BOM? ??? ??
  BOM??? ????? ???? ?? ??? ?? ??.
• ORDB vs. OODB
• gt ??? ???? ??? ???? ?? OODB, relational
  DB? ??? ??? ORDB?? DB structure? ?????, ????? ??
  ????? ??? ??? ??. ??? ??? ??? Real Time
  Enterprise? ???? ?? Best Solution ??.

22
?? ?? ??
3. Research Idea

• Camcorder phone

Display Module
Keypad Main board Module
Battery Module
Camera Module
Antenna Module
23
Database performance ????
3. Research Idea

• ???? Database structure ??
• ?? ?? ?? ??? ??? ??? ?? part explosion? ????? ???
  ? ?? database structure? ????.
• BOM ??? ??? ?? ???? query? ???? pattern? ???? ??
  ??? database structure?? ?? ??? ??? ??? ????.
• ?? ?? ??? ??? database structure? ?? DB? ?? ????
  ????.
• Materialized view? ??
• BOM? ???? ?? ? ??? ???? ??? ????? ?? ???? ?? ???
  database structure??? ??? ? ?? ??? materialized
  view? ??? ????? ??.
• ?? ???? ???? query? ?? ?? ???? materialized view?
  ????? ??.