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. 260~272 3. Research Idea (KERP-DB) MAI Lab. Seminar at 2005 Winter

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. Concept of View & View Materialization 1.1 Concept of a View in SQL • 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 DEPARTMENT DEPT_LOCATIONS PROJECT WORKS_ON DEPENDENT MAI Lab. Seminar at 2005 Winter

5. 1. Concept of View & View Materialization 1.2 Specification of Views in SQL • Create View WORKS_ON1 DEPT_INFO MAI Lab. Seminar at 2005 Winter

6. 1. Concept of View & View Materialization 1.3 View Implementation • 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. MAI Lab. Seminar at 2005 Winter

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. PDI(Persistent Derived Information) deriver 2. Materialization Strategy 2.1 Introduction and Motivation • 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 <= 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? MAI Lab. Seminar at 2005 Winter

9. 2. Materialization Strategy 2.1 Introduction and Motivation • 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 deriver’s 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 PDI’s 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 MAI Lab. Seminar at 2005 Winter

10. 2. Materialization Strategy 2.1 Introduction and Motivation • Motivation • To provide database administrators with a tool to assist in getting tuning decisions that can be based upon an application’s constraints • A Case Study : Project cost planning application Activity – Cost := Activity-Estimated-Cost*1.5 Resources-Cost := Resources-Estimated-Cost*1.2 Project-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 MAI Lab. Seminar at 2005 Winter

11. 2. Materialization Strategy 2.1 Introduction and Motivation Fig. The derivation graph Fig. Topological order of dependencies MAI Lab. Seminar at 2005 Winter

12. 2. Materialization Strategy 2.2 The Optimization Algorithm • Definitions required for algorithm assumptions 1) The transitive relation weaker, denoted as <v orders the materialization in the following total order : <passive <v semiactive <v active;lossely consistent <v active;quasiconsistent <v active;fully consistent> 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 MAI Lab. Seminar at 2005 Winter

13. 2. Materialization Strategy 2.2 The Optimization Algorithm • Two major assumptions 1) AssumptionAS1 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) AssumptionAS2 Fig. possible scenario MAI Lab. Seminar at 2005 Winter

14. 2. Materialization Strategy 2.2 The Optimization Algorithm • The Algorithm’s Formalization MAI Lab. Seminar at 2005 Winter

15. 2. Materialization Strategy 2.3 The Optimization Model • 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 MAI Lab. Seminar at 2005 Winter

16. 2. Materialization Strategy 2.3 The Optimization Model • The Goal Function MAI Lab. Seminar at 2005 Winter

17. 2. Materialization Strategy 2.3 The Optimization Model • The Goal Function MAI Lab. Seminar at 2005 Winter

18. 2. Materialization Strategy 2.4 Some Experimental Results • Update Frequency Analysis • Computation Cost Analysis MAI Lab. Seminar at 2005 Winter

19. 2. Materialization Strategy 2.5 Conclusion • 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. MAI Lab. Seminar at 2005 Winter

20. KERP-DB 연구배경 대상품목 선정 3.DB Performance 향상방안

21. 3. Research Idea 연구배경 vs. 연구목표. • IBM제품의 경우 부품이 만개 정도일 경우 part explosion 하는데 2일 정도 소요. => part explosion(MRP), resource allocation(CRP), Inventory Record, Cost등을 확장시켜 고려하고자 함. • 효율적인 DB 스키마 구현을 통해 MRP계획 기간 단축 => Generative BOM, Modular BOM등 방대한 양의 BOM정보를 효율적으로 관리하기 위한 다양한 연구 접목. • ORDB vs. OODB => 복잡한 데이터의 속성을 지원하기 위한 OODB, relational DB의 장점을 계승한 ORDB등의 DB structure가 등장했으나, 제조정보를 이에 적용시키는 연구가 희박한 실정. 다양한 실험을 통해서 Real Time Enterprise를 가능하게 하는 Best Solution 도출. MAI Lab. Seminar at 2005 Winter

22. 3. Research Idea 대상 품목 선정 • Camcorder phone Display Module Antenna Module Keypad & Main board Module Battery Module Camera Module MAI Lab. Seminar at 2005 Winter

23. 3. Research Idea Database performance 향상방안 • 효율적인 Database structure 구성 • 여러 개의 하위 항목을 가지는 부품에 대한 part explosion을 효과적으로 수행할 수 있는 database structure를 조사한다. • BOM 전개를 위하여 자주 발생하는 query를 분석하여 pattern을 파악하고 여러 종류의 database structure에서 작업 수행에 걸리는 시간을 측정한다. • 가장 좋은 성과를 보이는 database structure를 전체 DB의 기본 골격으로 구성한다. • Materialized view의 활용 • BOM의 부분적인 생성 및 수정에 있어서의 시간을 단축시키기 위한 방안으로 미리 설계된 database structure만으로 해결할 수 없는 부분을 materialized view를 통하여 해결하도록 한다. • 특히 다양하게 변화하는 query에 대한 대응 방법으로 materialized view를 사용하도록 한다. MAI Lab. Seminar at 2005 Winter