view materialization l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
View Materialization PowerPoint Presentation
Download Presentation
View Materialization

Loading in 2 Seconds...

play fullscreen
1 / 23

View Materialization - PowerPoint PPT Presentation


  • 317 Views
  • Uploaded on

View Materialization. Hyoung-Gon Lee MAI Lab. Seminar 2005.2.2. Table of Contents. 1. Concept of View & View Materialization - “Fundamentals of Database System” Third Edition, Elmasri, Chapter 8.5 2. Materialization Strategy

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'View Materialization' - Antony


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
view materialization

View Materialization

Hyoung-Gon Lee

MAI Lab. Seminar

2005.2.2

table of contents
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

1 concept of view view materialization

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

1 1 concept of a view in sql

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

1 2 specification of views in sql

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

1 3 view implementation

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

2 materialization strategy optimization of materialization strategies for derived data elements

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

2 1 introduction and motivation

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

2 1 introduction and motivation9

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

2 1 introduction and motivation10

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

2 1 introduction and motivation11

2. Materialization Strategy

2.1 Introduction and Motivation

Fig. The derivation graph

Fig. Topological order of dependencies

MAI Lab. Seminar at 2005 Winter

2 2 the optimization algorithm

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

2 2 the optimization algorithm13

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

2 2 the optimization algorithm14

2. Materialization Strategy

2.2 The Optimization Algorithm
  • The Algorithm’s Formalization

MAI Lab. Seminar at 2005 Winter

2 3 the optimization model

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

2 3 the optimization model16

2. Materialization Strategy

2.3 The Optimization Model
  • The Goal Function

MAI Lab. Seminar at 2005 Winter

2 3 the optimization model17

2. Materialization Strategy

2.3 The Optimization Model
  • The Goal Function

MAI Lab. Seminar at 2005 Winter

2 4 some experimental results

2. Materialization Strategy

2.4 Some Experimental Results
  • Update Frequency Analysis
  • Computation Cost Analysis

MAI Lab. Seminar at 2005 Winter

2 5 conclusion

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

kerp db

KERP-DB

연구배경

대상품목 선정

3.DB Performance 향상방안

slide21

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

slide22

3. Research Idea

대상 품목 선정
  • Camcorder phone

Display Module

Antenna Module

Keypad & Main board Module

Battery Module

Camera Module

MAI Lab. Seminar at 2005 Winter

database performance

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