Advanced Database Management Systems Distributed DBMS:Introduction - - PowerPoint PPT Presentation

Advanced Database Management Systems

Distributed DBMS:Introduction and Architectures Alvaro A A Fernandes

School of Computer Science, University of Manchester

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 1 / 121

Outline

Introduction to Distributed DBMSs Distributed DBMS Architectures

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 2 / 121

Introduction to Distributed DBMSs

Distributed Computing

Definition

◮ A number of ◮ distinct processing elements ◮ possibly administratively autonomous ◮ possibly heterogeneous ◮ interconnected by a computer network ◮ cooperating in the performance of assigned tasks. ◮ Several aspects of DBMS can be distributed, e.g.:

◮ Control (e.g., over updates, allocation of resources, etc.) ◮ Processing logic (e.g., algebraic operators, data movements, etc.) ◮ Services (e.g., optimization, access control, etc.) ◮ Data (e.g., tuples, columns, relations, etc.) AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 3 / 121

Introduction to Distributed DBMSs

What is a Distributed Database Management System?

Definition

◮ A distributed database (DDB) is a collection of multiple, distinct, but

logically interrelated databases, placed in different physical locations and linked by a computer network.

◮ A distributed database management system (DDBMS) is the software

that manages the DDB and provides mechanisms that make this distribution transparent to applications and end users.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 4 / 121

Introduction to Distributed DBMSs

DDBMS Environment (1)

◮ Note that a (centralized) DBMS

may be networked without being a DDBMS.

◮ This happens when there is no

logical view over the data and resources that could be accessed and shared over a network.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 5 / 121

Introduction to Distributed DBMSs

DDBMS Environment (2)

◮ When there is a logical view

• ver data and resources, they

can then be accessed and shared

• ver a network and co-operation

takes place.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 6 / 121

Introduction to Distributed DBMSs

DDBMS Environment (3)

Implicit Assumptions

◮ Data may be stored at a number of sites. ◮ Each site logically has a distinct (assumed single) processor. ◮ Processors at different sites are interconnected by a computer

network, therefore no multiprocessors, no specialist interconnect, no specialist parallel hardware.

◮ The DDB is a DB, not a collection of data files, therefore the data is

logically related (e.g., as manifested in the access patterns that are characteristic of the relational data model).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 7 / 121

Introduction to Distributed DBMSs

DDBMS Environment (4)

Applications/Promises

◮ Any organization which has a decentralized structure is a good a

priori candidate for using DDBMSs.

◮ A DDBMS promises:

◮ Transparent management of distributed, fragmented, and replicated

data

◮ Improved reliability/availability through distributed processes ◮ Improved performance by exploiting locality and parallelism ◮ Easier and more economical system expansion through scale out (i.e.,

more of the same “boxes”) rather than scale up (i.e., ever bigger, more expensive “boxes”).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 8 / 121

Introduction to Distributed DBMSs

DDBMS Environment (5)

Transparency

◮ Transparency stems from abstraction, i.e., the separation of the

higher-level semantics of a system from the lower-level implementation concerns.

◮ In a DDBMS environment, a fundamental issue is to provide data

independence through several kinds of transparency:

◮ Network (or distribution) transparency ◮ Replication transparency ◮ Fragmentation transparency ◮ horizontal, through selection ◮ vertical, through projection ◮ hybrid, combining both AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 9 / 121

Introduction to Distributed DBMSs

DDBMS Environment (6)

Example Relations

Example

EMP =

ENO ENAME TITLE E1

• J. Doe
• Elect. Eng.

E2

• M. Smith
• Syst. Anal.

E3

• A. Lee
• Mech. Eng.

E4

• J. Miller

Programmer E5

• B. Casey
• Syst. Anal.

E6

• L. Chu
• Elect. Eng. .

E7

• R. Davis
• Mech. Eng.

E8

• J. Jones
• Syst. Anal

PROJ =

PNO PNAME BUDGET LOC P1 Instrumentation 150000 Tokyo P2 Database Develop. 135000 Oslo P3 CAD/CAM 250000 Oslo P4 Maintenance 310000 Paris P5 CAD/CAM 500000 Paris

ASG =

ENO PNO RESP DUR E1 P1 Manager 12 E2 P1 Analyst 24 E2 P2 Analyst 6 E3 P3 Consultant 10 E3 P4 Engineer 48 E4 P2 Programmer 18 E5 P2 Manager 24 E6 P4 Manager 48 E7 P3 Engineer 36 E7 P5 Engineer 23 E8 P3 Manager 40

PAY =

TITLE SAL

• Elect. Eng.

40000

• Syst. Anal.

34000

• Mech. Eng.

27000 Programmer 24000 AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 10 / 121

Introduction to Distributed DBMSs

DDBMS Environment (7)

Transparent Access

Example

Find the name and salary of employees on assignments not lasting 12 months.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 11 / 121

Introduction to Distributed DBMSs

DDBMS Environment (8)

Potentially Improved Performance

Locality : Data can be kept close to its points of use by means of data distribution strategies whilst still benefitting all by means of data integration strategies. Parallelism : Distribution allows for both inter-query (i.e., when whole query evaluation plans (QEPs) run in distinct sites) and intra-query parallelism (i.e., when QEP fragments of the same query run in distinct sites).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 12 / 121

Introduction to Distributed DBMSs

DDBMS Environment (9)

Scale-Out System Expansion

◮ Scaling-out (i.e., deriving a positive response in performance to more

• f the same processing elements) is generally considered to be easier

than scaling-up (i.e., deriving a positive response in performance by the same number of larger processing elements).

◮ With the widespread availability of high-performance commodity

hardware, scale-out is all the more appealing now than in the past.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 13 / 121

Distributed DBMS Architectures

DBMS Abstraction Through Schema Levels

The ANSI/SPARC Architecture

◮ A DBMS supports abstractions

by means of schemas that define different views at different levels.

◮ A DDBMS must provide

transparency without breaking, and indeed supporting, such expectations.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 14 / 121

Distributed DBMS Architectures

DBMS Implementation Alternatives (1)

◮ The DBMS implementation dimensions that matter the most in

DDBMSs are: distribution :

• f various kinds (e.g., processing, data)

heterogeneity :

• f various kinds (e.g., syntactic, semantic)

autonomy :

• f various kinds (e.g., at instance level, at schema level)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 15 / 121

Distributed DBMS Architectures

DBMS Implementation Alternatives (2)

Dimensions of the Problem

◮ Distribution refers to whether the data and processing components of

the system are located on the same machine or not.

◮ There many sources of heterogeneity, e.g.,

◮ infrastructural (e.g., different hardware, communications, OSs, etc.) ◮ syntactic (e.g., different data model, database languages, etc.) ◮ semantic (e.g., different names for the same concepts, different

concepts with the same name)

◮ Autonomy is the least understood, the most troublesome to contend

with, and takes various forms:

◮ Design autonomy, i.e., the degree to which the design of a component

DBMS can change without explicit co-ordination and control

◮ Communication autonomy, i.e., the degree to which a component

DBMS can decide whether and how to communicate with others.

◮ Execution autonomy, i.e., the degree to which a component DBMS can

decide whether and how to execute operations locally

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 16 / 121

Distributed DBMS Architectures

DBMS Implementation Alternatives (3)

◮ There are some interesting

triples in the space defined by the implementation dimensions in the figure: centralized DBMSs :

(D=none, H=none, A=none) client-server DBMSs : (D=some, H=some, A=none) federated DBMSs : (D=any, H=some, A=some) multi-DBMSs : (D=any, H=any, A=any)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 17 / 121

Distributed DBMS Architectures

Distributed DBMS Architectures (1)

Federated DBMS from Component Schemas

◮ In the figure, federated is being used

to connote the notion that the component DBMSs co-operate by neither exercising high degrees of autonomy nor inflicting high levels of heterogeneity on others.

◮ In this case, a global conceptual

schema (GCS) arises from local conceptual schemas (LCS) and local internal schemas (LIS) by a negotiation process (or by imposition from the centre, if within organization boundaries).

◮ The external schemas (ES) can be

more easily derived from the GCS.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 18 / 121

Distributed DBMS Architectures

Distributed DBMS Architectures (2)

Multi-DBMS from Component Schemas

◮ In the figure, ’multi-’ is being used to

connote the notion that the component DBMSs have no coercion

• n their autonomy nor on how

heterogeneous they make themselves.

◮ In this case, a GCS does not normally

arise by negotiation (e.g., there may be more than one GCS if the component DBMSs have public interfaces).

◮ The component DBMSs may still have

local external schemas (LES) imposed upon them.

◮ The GCS too can have global external

schemas (GES) imposed upon it.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 19 / 121

Distributed DBMS Architectures

Distributed DBMS Architectures (3)

Multi-DBMS without a Global Schema

◮ The absence of a global schema means

that only partial views arise, i.e., there is no attempt at a unified description

• f all the component DBMSs.

◮ This is more likely in the case of

ad-hoc, single-use scenarios, where there is no motivation to invest on creating a global view over the resources.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 20 / 121

Distributed DBMS Architectures

Distributed DBMS Architectures (4)

Multi-DBMS Execution Model

◮ In a multi-DBMS there is a need to

map a global request into local sub-requests and local sub-results into a global result.

◮ The component DBMSs still cater for

local requests with local results.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 21 / 121

Distributed DBMS Architectures

Distributed DBMS Architectures (5)

Time-Shared Access to a Centralized Database

◮ In mere time-sharing of a centralized

DBMS, all data and all applications run remotely from the point of access.

◮ Requests are for batch tasks, a

response (and not necessarily results) is sent back.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 22 / 121

Distributed DBMS Architectures

Distributed DBMS Architectures (6)

Multiple Clients/Single Server

◮ In client-server approaches, clients are

applications that interface through client-side services and communications with a server.

◮ The server runs server-side services in

response to client requests.

◮ Because of the client-side services that

support the application, high-level, fine-grained, interactive requests can be sent that cause results (i.e., filtered answers only) to flow back.

◮ In general, the client-side services offer

query language interfaces (perhaps language-embedded, or form-based, or both).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 23 / 121

Distributed DBMS Architectures

Distributed DBMS Architectures (7)

Pros/Cons of Client-Server Architectures

Pros

◮ More efficient division of labor ◮ Client-side scale-up and

scale-out

◮ Better price/performance on

client machines

◮ Ability to use familiar tools on

client machines

◮ Client access to remote data ◮ Full DBMS functionality

provided to many

◮ Overall better system

price/performance Cons (vis-` a-vis other distribution strategies)

◮ Possible bottleneck and single

point of failure in the server

◮ Server-side scale-up and

scale-out less easy

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 24 / 121

Distributed DBMS Architectures

Distributed DBMS Architectures (8)

Multiple Clients/Multiple Servers

◮ Distributing server-side load is

possible.

◮ Mechanisms become more complex at

the lower levels.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 25 / 121

Distributed DBMS Architectures

Distributed DBMS Architectures (9)

Co-Operating Servers Once servers start co-operating, one is coming close to a truly distributed DDBMS. The newest classes of DDBMSs have arisen in the last five year as a result of pressure to maintain

◮ extremely large repositories of either

structured or unstructured data supporting

◮ workloads consisting of

◮ either relatively few

computationally intensive analyses

◮ or an extremely large amount of

relatively simple retrieval or update requests.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 26 / 121

Distributed DBMS Architectures

Summary

Distributed DBMS Architectures

◮ DDBMSs have risen in importance due to structural changes in the

computing landscape that saw the networking of high-quality PCs become the norm.

◮ Even so, they still retain their original role of emulating the

• perational decentralization of organizations.

◮ DDBMS architectures capitalize on localization and parallelization to

• ffer a potential for performance gains.

◮ Nonetheless, autonomy and heterogeneity levels can create significant

hurdles for full distribution.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 27 / 121

Distributed DBMS Architectures

Advanced Database Management Systems

Data Distribution Strategies Alvaro A A Fernandes

School of Computer Science, University of Manchester

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 28 / 121

Outline

Distributed DBMSs: The Design Problem Data Distribution Strategies Fragmentation and Allocation Fragmentation, in More Detail

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 29 / 121

Distributed DBMSs: The Design Problem

Distribution Strategies (1)

The Design Problem

◮ In the general setting, we need to decide:

◮ the placement of data and programs ◮ across the sites of a computer network ◮ as well as possibly designing the network itself

◮ In DDBMS, the placement of applications entails:

◮ placement of the distributed DBMS software ◮ placement of the applications that run on the database AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 30 / 121

Distributed DBMSs: The Design Problem

Distribution Strategies (2)

Dimensions of the Problem

◮ Whether only data is partitioned across sites (and programs are

replicated everywhere) or whether programs are partitioned too

◮ Whether the access patterns are stable or not ◮ Whether knowledge of such access patterns is complete or not

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 31 / 121

Distributed DBMSs: The Design Problem

Distribution Strategies (3)

Design Approaches

Top-Down : only possible, in practice, when the system is being designed from scratch, and only lasts if heterogeneity and autonomy are tightly controlled Bottom-Up : only practical solution when the component databases already exist at a number of sites, and more likely to last when heterogeneity and autonomy cannot be controlled

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 32 / 121

Data Distribution Strategies

Data Distribution Strategies (4)

Some Design Issues

◮ Why fragment at all? ◮ How to fragment? ◮ How much to fragment? ◮ How to ensure correctness of fragmentation? ◮ How to allocate fragments? ◮ What information is required?

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 33 / 121

Data Distribution Strategies

Data Distribution Strategies (5)

Fragmentation (1)

◮ Why can’t we just distribute relations?

◮ Because most relations are designed to be suitable for a great many

applications, and different applications may be subject to different locality aspects and offer different parallelization opportunities.

◮ What is a reasonable unit of distribution?

◮ Roughly, that view on a relation that is needed by one or more

applications in one place

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 34 / 121

Fragmentation and Allocation

Data Distribution Strategies (6)

Fragmentation (2)

Consider the case of entire relations as the unit of distribution:

◮ Most relations have subsets whose semantics characterize special

affinity (e.g., of location, of timing, etc.).

◮ For example, in a relation Employees, the attribute Department may

characterize location affinity if different departments occupy different locations.

◮ If so, then unnecessary communication may be incurred if we

distribute entire relations.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 35 / 121

Fragmentation and Allocation

Data Distribution Strategies (7)

Fragmentation (3)

Consider the case of sub-relations as the unit of distribution:

◮ A sub-relation, referred to as a fragment in the DDBMS context, is

what is specified by a view (typically by selection or projection or both).

◮ Fragmentation can be derived in knowledge of applications and their

affinities and allows parallel/distributed execution.

◮ For example, if Employee is horizontally fragmented by the attribute

Department, and different fragments are held where the corresponding department is located, computing the average salary in each department can be done in parallel.

◮ If, after fragmentation, a particular query/view cannot be defined

• ver a single fragment, then extra processing will be needed.

◮ Also, semantic checks may be more difficult (e.g., enforcing

referential integrity).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 36 / 121

Fragmentation and Allocation

Data Distribution Strategies (8)

Fragmentation Alternatives: Horizontal (1)

◮ Broadly speaking, defined by a selection. ◮ Reconstruction is by union.

Example

PROJ1 ← σbudget<200000(PROJ) PROJ2 ← σbudget≥200000(PROJ) PROJ ← PROJ1 ∪ PROJ2

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 37 / 121

Fragmentation and Allocation

Data Distribution Strategies (9)

Fragmentation Alternatives: Horizontal (2)

Example

PROJ1 = PNO PNAME BUDGET LOC P1 Instrumentation 150000 Tokyo P2 Database Develop. 135000 Oslo PROJ2 = PNO PNAME BUDGET LOC P3 CAD/CAM 250000 Oslo P4 Maintenance 310000 Paris P5 CAD/CAM 500000 Paris PROJ = PNO PNAME BUDGET LOC P1 Instrumentation 150000 Tokyo P2 Database Develop. 135000 Oslo P3 CAD/CAM 250000 Oslo P4 Maintenance 310000 Paris P5 CAD/CAM 500000 Paris

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 38 / 121

Fragmentation and Allocation

Data Distribution Strategies (10)

Fragmentation Alternatives: Vertical (1)

◮ Broadly speaking, defined by a projection. ◮ Reconstruction is by a natural join on the replicated key.

Example

PROJ1 ← πPNO,BUDGET(PROJ) PROJ2 ← πPNO,PNAME,LOC(PROJ) PROJ ← PROJ1 ⊲ ⊳PNO PROJ2

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 39 / 121

Fragmentation and Allocation

Data Distribution Strategies (11)

Fragmentation Alternatives: Vertical (2)

Example

PROJ1 = PNO BUDGET P1 150000 P2 135000 P3 250000 P4 310000 P5 500000 PROJ2 = PNO PNAME LOC P1 Instrumentation Tokyo P2 Database Develop. Oslo P3 CAD/CAM Oslo P4 Maintenance Paris P5 CAD/CAM Paris PROJ = PNO PNAME BUDGET LOC P1 Instrumentation 150000 Tokyo P2 Database Develop. 135000 Oslo P3 CAD/CAM 250000 Oslo P4 Maintenance 310000 Paris P5 CAD/CAM 500000 Paris AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 40 / 121

Fragmentation and Allocation

Data Distribution Strategies (12)

Correctness of Fragmentation

Completeness The decomposition of a relation R into fragments R1, R2, ..., Rn is complete if and only if each data item in R can also be found in some Ri. Reconstructibility If a relation R is decomposed into fragments R1, R2, ..., Rn, then there should exist some relational operator ∇ such that R = ∇n

i=1Ri.

Disjointness If a relation R is decomposed into fragments R1, R2, ..., Rn, and data item di is in Rj, then di should not be in any other fragment Rk (k = j).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 41 / 121

Fragmentation and Allocation

Data Distribution Strategies (13)

Allocation Alternatives

Non-replicated : the fragments form a proper partition, and each fragment resides at only one site. Replicated : the fragments overlap, either fully (i.e., each fragment exists at every site) or partially (i.e., each fragment exists at some sites only).

◮ An often used rule-of thumb is that if the number of proper (i.e.,

read-only) queries is larger than the number of updating queries, then replication tends to be advantageous in proportion, otherwise the

• pposite is the case.

◮ Especially in the client/server case, caching is also part of the design

considerations.

◮ Web giants (e.g., Facebook, Amazon) use replication extensively.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 42 / 121

Fragmentation and Allocation

Data Distribution Strategies (14)

Replication v. Caching: Some Contrasts

Replication Caching target server client or middle-tier granularity coarse fine storage device typically disk typically main memory impact on catalog yes no update protocol propagation invalidation remove copy explicit implicit mechanism separate fetch fault in and keep copy after use

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 43 / 121

Fragmentation, in More Detail

Data Distribution Strategies (15)

Information Requirements

◮ The are four kinds of information required:

◮ about the database ◮ about the applications (i.e., the queries, by and large) ◮ about the communication network ◮ about the computer system AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 44 / 121

Fragmentation, in More Detail

Fragmentation

Kinds

◮ Horizontal Fragmentation (HF)

◮ Primary Horizontal Fragmentation (PHF) ◮ Derived Horizontal Fragmentation (DHF)

◮ Vertical Fragmentation (VF) ◮ Hybrid Fragmentation (HF)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 45 / 121

Fragmentation, in More Detail

Primary Horizontal Fragmentation (1)

Information Requirements: Database

◮ We draw a link from a relation

R to a relation S if we there is an equijoin on the key of R and the corresponding foreign key in S.

◮ We call R the owner, and S the

member.

◮ We need the cardinalities of

relations and the (average) length of their tuples.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 46 / 121

Fragmentation, in More Detail

Primary Horizontal Fragmentation (2)

Information Requirements: Application (1)

◮ Given R with schema [A1, . . . , An], a simple predicate pj has the

form Aiθc where θ ∈ {=, =, <, >, ≤, ≥}, c ∈ Domain(Ai).

◮ For a relation R, we define Pr = {p1, . . . , pm}. ◮ Given R and Pr, we define the set of minterm predicates

M = {m1, . . . , mr} as M = { mk|mk =

pj∈Pr p∗ j },

1 ≤ j ≤ m, 1 ≤ k ≤ r, where p∗

j = pj or else p∗ j = ¬p∗ j .

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 47 / 121

Fragmentation, in More Detail

Primary Horizontal Fragmentation (3)

Information Requirements: Application (2)

Example

Some (but not all) simple predicates on PROJ are: p1 : LOC =

′Tokyo′

p2 : LOC =

′Oslo′

p3 : LOC =

′Paris′

p4 : BUDGET ≤ 200000 Some (but not all) minterm predicates on PROJ are: m1 : LOC = ′Tokyo′ ∧ BUDGET ≤ 200000 m2 : ¬(LOC = ′Tokyo′) ∧ BUDGET ≤ 200000 m3 : LOC = ′Tokyo′ ∧ ¬(BUDGET ≤ 200000) m4 : ¬(LOC = ′Tokyo′) ∧ ¬(BUDGET ≤ 200000)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 48 / 121

Fragmentation, in More Detail

Primary Horizontal Fragmentation (4)

Information Requirements: Application (3)

◮ We also need quantitative information about the application:

◮ The selectivity of a minterm mi, denoted by sel(mi) is the number of

tuples in the corresponding relation R that would be produced by σmi(R).

◮ The access frequency of an application qi, denoted by acc(qi) is the

number of times in which qi accesses data in a given period.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 49 / 121

Fragmentation, in More Detail

Primary Horizontal Fragmentation (5)

Definition

◮ A (primary) horizontal fragment Rj of a relation R is defined as

Rj ← σmi(R) where mi is a minterm predicate on R.

◮ Given a set of minterm predicates M = {m1, . . . , mr} over R, one can

define r horizontal fragments in R.

◮ [¨

Oszu and Valduriez, 1999] give an algorithm that, given a relation R and a set of simple predicates on R, produces a correct set of fragments from R.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 50 / 121

Fragmentation, in More Detail

Primary Horizontal Fragmentation (6)

Example (1)

Example (Information Required)

◮ Let the relations PAY and PROJ be candidates for PHF. ◮ Let the following be the applications involved:

◮ A1: Find the name and budget of projects given their project number. ◮ A2: Find projects according to their budget. ◮ Let A1 be issued at three sites. ◮ Let one site access A2 for budgets below 200000, and the other two

access A2 for those above.

◮ Let the following be the simple predicates:

p1 : LOC =

′Tokyo′

p2 : LOC =

′Oslo′

p3 : LOC =

′Paris′

p4 : BUDGET ≤ 200000 p5 : BUDGET > 200000

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 51 / 121

Fragmentation, in More Detail

Primary Horizontal Fragmentation (7)

Example (2)

Example (Output)

◮ Applying the algorithm alluded to, the following minterm predicates

result: m1 : LOC = ′Tokyo′ ∧ BUDGET ≤ 200000 m2 : LOC = ′Tokyo′ ∧ BUDGET > 200000 m3 : LOC = ′Oslo′ ∧ BUDGET ≤ 200000 m4 : LOC = ′Oslo′ ∧ BUDGET > 200000 m5 : LOC = ′Paris′ ∧ BUDGET ≤ 200000 m6 : LOC = ′Paris′ ∧ BUDGET > 200000

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 52 / 121

Fragmentation, in More Detail

Primary Horizontal Fragmentation (8)

Example (3)

Example (Fragments Obtained)

PROJ1 = PNO PNAME BUDGET LOC P1 Instrumentation 150000 Tokyo PROJ3 = PNO PNAME BUDGET LOC P2 Database Develop. 135000 Oslo PROJ4 = PNO PNAME BUDGET LOC P3 CAD/CAM 250000 Oslo PROJ6 = PNO PNAME BUDGET LOC P4 Maintenance 310000 Paris P5 CAD/CAM 500000 Paris

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 53 / 121

Fragmentation, in More Detail

Derived Horizontal Fragmentation (1)

Definition

◮ A derived horizontal fragment is defined on a member relation

according to a selection operation on its owner.

◮ Recall that a link from owner to member is defined in terms of an

equijoin.

◮ A semijoin between R and S is defined as follows:

R ⋉ S ≡ πA(R ⊲ ⊳ S), where A is the list of attributes in the schema

• f R.

◮ Given a link L, where owner(L) = S and member(L) = R, the derived

horizontal fragments of R are defined as Ri = R ⋉ Si, 1 ≤ i ≤ w, where w is the maximum number of fragments to be generated and Si = σmi(S) is the primary horizontal fragment defined by the minterm predicate mi.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 54 / 121

Fragmentation, in More Detail

Derived Horizontal Fragmentation (2)

Example (1)

Example (Information Required, Fragments Defined)

◮ Let there be a link L1 with owner(L1) = PAY and

member(L1) = EMP.

◮ Let PAY1 ← σSAL≤30000(PAY ) and PAY2 ← σSAL>30000(PAY ). ◮ Then two DHFs are defined:

◮ EMP1 ← EMP ⋉ PAY1 ◮ EMP2 ← EMP ⋉ PAY2 AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 55 / 121

Fragmentation, in More Detail

Derived Horizontal Fragmentation (3)

Example (2)

Example (Fragments Obtained)

EMP1 = ENO ENAME TITLE E3

• A. Lee
• Mech. Eng.

E4

• J. Miller

Programmer E7

• R. Davis
• Mech. Eng.

EMP2 = ENO ENAME TITLE E1

• J. Doe
• Elect. Eng.

E2

• M. Smith
• Syst. Anal.

E5

• B. Casey
• Syst. Anal.

E6

• L. Chu
• Elect. Eng. .

E8

• J. Jones
• Syst. Anal

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 56 / 121

Fragmentation, in More Detail

Vertical Fragmentation (1)

◮ Vertical fragmentation has also been studied in the centralized

context since it is important for:

◮ normalization of designs ◮ physical clustering

◮ In terms of physical clustering, there is excitement in the DBMS

industry (at the time of writing) about an extreme form of vertical partitioning in which single columns are stored separately.

◮ Certain access patterns are made easier by this and compression levels

an order of magnitude larger can be obtained, which is important when dealing with the massive volumes of data that are typical of analytics workloads.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 57 / 121

Fragmentation, in More Detail

Vertical Fragmentation (2)

◮ Vertical fragmentation is more difficult than horizontal fragmentation,

because more alternatives exist.

◮ Heuristic approaches that can be used are:

grouping : one adds attributes to fragments one by one. splitting : one breaks down a relation into fragments based on access patterns.

◮ See [¨

Oszu and Valduriez, 1999] for an example (or else recall, from your earlier database studies the theory of normal forms and how it is justifiably disobeyed).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 58 / 121

Fragmentation, in More Detail

Summary

Data Distribution Strategies

◮ Fragmentation, allocation, replication and caching are all mechanisms

that DDBMSs make use of to respond to the affinity of locality that data exhibits, particularly in decentralized organizations.

◮ The design decisions required are well-studied and well-founded

solutions are available but require a great deal of information.

◮ The benefits can be significant particularly for response time because

• f the greater degree of natural parallelism that becomes possible.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 59 / 121

Fragmentation, in More Detail

Advanced Database Management Systems

Distributed Query Processing Alvaro A A Fernandes

School of Computer Science, University of Manchester

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 60 / 121

Outline

The Distributed Query Processing Problem Two-Phase Distributed Query Optimization Localization and Reduction Cost-Related Issues Join Ordering in DQP

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 61 / 121

The Distributed Query Processing Problem

Distributed Query Processing (1)

What is the Problem? (1)

◮ Assume the fragments EMPi

and ASGj to be stored in the sites shown in the figure.

◮ Assume the double-shafted

arrows to denote the transfer of data between sites.

◮ Strategy 1 can be said to aim to

do processing locally in order to reduce the amount of data that needs to be shipped to the result site, i.e., Site 5.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 62 / 121

The Distributed Query Processing Problem

Distributed Query Processing (2)

What is the Problem? (2)

◮ Strategy 2 can be said to aim to

ship all the data to, and do all the processing at, the site where results need to be delivered

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 63 / 121

The Distributed Query Processing Problem

Distributed Query Processing (3)

Cost of Alternatives (1)

Example (Assumptions)

◮ ta (tuple access cost) = 1 unit ◮ tt (tuple transfer cost) = 10 units ◮ |ASG| = 100, length(ASG) = 10, |EMP| = 80, length(EMP) = 5 ◮ |ASG1| = |σENO≤′E3′(ASG)| = 50 ◮ |EMP1| = |σENO≤′E3′(EMP)| = 40 ◮ V (ASG, RESP) = 5 ◮ length(ENO) = 2

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 64 / 121

The Distributed Query Processing Problem

Distributed Query Processing (4)

Cost of Alternatives (2)

Example (Consequences)

◮ size(ASG) = 100 × 10 = 1, 000, size(EMP) = 80 × 5 = 400 ◮ |ASG2| = |ASG| − |ASG1| = 100 − 50 = 50 ◮ size(ASG1) = size(ASG2) = |ASG1| × 10 = 50 × 10 = 500 ◮ |EMP2| = |EMP| − |EMP1| = 80 − 40 = 40 ◮ size(EMP1) = size(EMP2) = |EMP1| × 5 = 40 × 5 = 200 ◮ |ASG ′

1| = |ASG ′ 2| = |σRESP=′manager′(ASG1)| = |ASGi | V (ASG,RESP) = 50 5 = 10

◮ |ASG ′| = |ASG ′

1| + |ASG ′ 2| = 10 + 10 = 20

◮ length(EMPi ⊲

⊳ENO ASG ′

i ) = length(EMP) + length(ASG) − length(ENO) =

10 + 5 − 2 = 13

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 65 / 121

The Distributed Query Processing Problem

Distributed Query Processing (5)

Cost of Alternatives (3)

Example (Comparison (1))

Action Cost Formula Cost produce ASG ′

i

= 2 × |ASGi| × ta = 2 × 50 × 1 100 transfer ASG ′

i to sites 3, 4

= 2 × size(ASG ′

i ) × tt

= 2 × 10 × 10 × 10 2,000 produce EMP′

i

= 2 × |EMPi| × |ASG ′

i | × ta

= 2 × 40 × 10 × 1 800 transfer EMP′

i to site 5

= 2 × size(EMPi ⊲ ⊳ENO ASG ′

i ) × tt

= 2 × 40×10

40

× 13 × 10 2,600 Total Cost of Strategy 1 5,500

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 66 / 121

The Distributed Query Processing Problem

Distributed Query Processing (6)

Cost of Alternatives (4)

Example (Comparison (2))

Action Cost Formula Cost transfer EMP to site 5 = size(EMP) × tt = 400 × 10 4,000 transfer ASG to site 5 = size(ASG) × tt = 1000 × 10 10,000 produce ASG’ = |ASG| × ta = 100 × 1 100 join EMP and ASG’ = |EMP| × |ASG ′| × ta = 80 × 20 × 1 1,600 Total Cost of Strategy 2 15,700

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 67 / 121

The Distributed Query Processing Problem

Distributed Query Processing (7)

Query Optimization Objectives

◮ Minimize a cost function such as total time or response time. ◮ All components may have different weights in different distributed

environments.

◮ One could have different goals, e.g., maximize throughput.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 68 / 121

The Distributed Query Processing Problem

Distributed Query Processing (8)

Where Can Decisions Be Made?

Centralized

◮ A single site determines the schedule. ◮ This is simpler, but requires knowledge about the entire

distributed database. Distributed

◮ There is co-operation among sites to determine the

schedule.

◮ This only requires sharing local information, but

co-operation has a cost. Hybrid

◮ One site determines the global schedule. ◮ Each site optimizes the local subqueries.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 69 / 121

The Distributed Query Processing Problem

Distributed Query Processing (9)

Issues Regarding the Network

Wide-Area Network (WAN)

◮ WANs have comparatively low

bandwidth, low speed and high protocol overhead

◮ As a result, communication cost will dominate, to the

extent that it may be possible to ignore all other costs.

◮ Thus, the global schedule will aim to minimize

communication cost.

◮ Local schedules are decided according to centralized

query optimization decisions. Local-Area Network (LAN)

◮ Communication cost is not as

dominant as in WANs.

◮ Thus, all components in the total cost function must be

considered.

◮ Broadcasting is an option.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 70 / 121

Two-Phase Distributed Query Optimization

Distributed Query Optimization (1)

Two-Phase Approach

◮ One way to implement distributed

query optimization as a continuum with the centralized case is to structure the decision-making stages in such a way that the optimizer breaks the overall task into two phases.

◮ In the first phase, a single-node QEP

is produced (that would run if the DBMS were not a distributed DBMS); in the second phase, this single-node QEP is transformed into a multi-node

• ne.

◮ The second phase partitions a QEP

into fragments linked by exchange

• perators, then schedules each

fragment to execute in different component nodes.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 71 / 121

Localization and Reduction

Distributed Query Optimization (2)

Localizing a Global Query

◮ Given an algebraic query on global relations:

◮ determine which are distributed; ◮ for those, determine which fragments are involved; ◮ replace references to global relations with the reconstruction expression

(which is referred to as a localization program).

◮ The leaves of distributed relations are replaced by its localization

program over its fragments.

◮ The result is sometimes referred to as a generic query and is likely to

benefit from optimization by reduction.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 72 / 121

Localization and Reduction

Distributed Query Optimization (3)

Some Examples (1)

◮ Assume EMP is horizontally fragmented into EMP1, EMP2 and

EMP3 as follows:

• 1. EMP1 ← σENO≤′E3′(EMP)
• 2. EMP2 ← σ′E3′<ENO≤′E6′(EMP)
• 3. EMP3 ← σENO>′E6′(EMP)

◮ Assume ASG is horizontally fragmented into ASG1 and ASG2 as

follows:

• 1. ASG1 ← σENO≤′E3′(ASG)
• 2. ASG2 ← σENO>′E3′(ASG)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 73 / 121

Localization and Reduction

Distributed Query Optimization (4)

Some Examples (2)

◮ Assume the following query:

SELECT E.ENAME FROM EMP E WHERE E.ENO = ’E5’

◮ The figure shows the corresponding

generic query with the leaf replaced by its localization program.

◮ Then, the figure shows the query

after optimization by reduction, in this case because it follows from the predicates that defined the fragments that only EMP2 can contribute to the specified results.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 74 / 121

Localization and Reduction

Distributed Query Optimization (5)

Some Examples (3)

◮ Assume the following query:

SELECT E.ENAME FROM EMP E, ASG A WHERE E.ENO = A.ENO

◮ The figure shows the corresponding

generic query with the leaf replaced by its localization program.

◮ We next show the query after

reduction.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 75 / 121

Localization and Reduction

Distributed Query Optimization (6)

Some Examples (4)

◮ The figure shows the reduced join

query.

◮ Note that the optimizer has used

the commutativity between join and union to push the joins upstream and reduce the amount of work.

◮ This also helps in scheduling the

joins to execute in parallel.

◮ Note, finally, that the optimizer has

made use of the fact that EMP3 and ASG1 do not share tuples (because their predicates lead to a contradiction, and hence would return an empty set) and eliminated the need to join them.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 76 / 121

Localization and Reduction

Distributed Query Optimization (7)

Some Examples (5)

◮ Assume EMP is vertically

fragmented into EMP1 and EMP2 as follows:

• 1. EMP1 ← πENO,ENAME(EMP)
• 2. EMP2 ← πENO,TITLE(EMP)

◮ Assume the following query:

SELECT E.ENAME FROM EMP E

◮ The figure shows the corresponding

generic query with the leaf replaced by its localization program.

◮ Then, the figure shows the query

after optimization by reduction, in this case because it follows from the projection lists that defined the fragments that only EMP1 can contribute to the specified results.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 77 / 121

Localization and Reduction

Distributed Query Optimization (8)

A Detailed Example Derivation (1)

◮ Assume PROJ is horizontally fragmented into PROJ1, PROJ2 and

PROJ3 as follows:

• 1. PROJ1 ← σLOC=′Tokyo′(PROJ)
• 2. PROJ2 ← σLOC=′Oslo′(PROJ)
• 3. PROJ3 ← σLOC=′Paris′(PROJ)

◮ Assume the following query:

SELECT AVG(P.BUDGET) FROM PROJ P WHERE P.LOC = ’OSLO’

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 78 / 121

Localization and Reduction

Distributed Query Optimization (9)

A Detailed Example Derivation (2) (translate) γAVG(BUDGET)(σLOC=′Oslo′(PROJ)) (localize) γAVG(BUDGET)(σLOC=′Oslo′(PROJ1 ∪ (PROJ2 ∪ PROJ3))) (expand) γAVG(BUDGET)(σLOC=′Oslo′ (σLOC=′′Tokyo′(PROJ) ∪ (σLOC=′Oslo′(PROJ) ∪ σLOC=′Paris′(PROJ)))) (combine) γAVG(BUDGET)(σLOC=′Oslo′∧LOC=′Tokyo′(PROJ) ∪(σLOC=′Oslo′∧LOC=′Oslo′(PROJ) ∪(σLOC=′Oslo′∧LOC=′Paris′(PROJ)))) (simplify) γAVG(BUDGET)(σ⊥(PROJ) ∪ (σLOC=′Oslo′(PROJ) ∪ (σ⊥(PROJ)))) (simplify) γAVG(BUDGET)(∅ ∪ (σLOC=′Oslo′(PROJ) ∪ ∅)) (simplify) γAVG(BUDGET)(σLOC=′Oslo′(PROJ)) (simplify) γAVG(BUDGET)(PROJ2)

This derivation shows that the query can be executed only over the Oslo horizontal fragment PROJ2 and wherever it is stored.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 79 / 121

Cost-Related Issues

Distributed Query Optimization (10)

Scheduling Query Fragments

◮ Given a fragment query, find the best global schedule by minimizing a

cost function.

◮ Join processing in centralized DBMSs tends to prefer linear (e.g.,

left-deep) trees because the size of the search space is reduced by the linearity constraint).

◮ However, in distributed DBMSs, join processing over bushy trees

reveals opportunities for parallelism.

◮ Other decisions include:

◮ Which relation to ship where? ◮ Whether to ship the whole or to ship as needed? ◮ Whether to use semijoins? (Semijoins save on communication at the

expense of more local processing.)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 80 / 121

Cost-Related Issues

Distributed Query Optimization (11)

Cost Functions

Total Time (also referred to as Total Cost): The overall strategy in this case is to

◮ Reduce the cost (i.e., time) in each component

individually

◮ Do as little of each cost component as possible

This optimizes the utilization of the resources and tends to increases system throughput. Response Time The overall strategy in this case is to do as many things as possible in parallel. However, this may increase the total time because of overall increased activity.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 81 / 121

Cost-Related Issues

Distributed Query Optimization (12)

Total Cost

◮ The total cost is the summation of all cost factors:

• 1. Total cost = CPU cost + I/O cost + communication cost
• 2. CPU cost = unit instruction cost × no.of instructions
• 3. I/O cost = unit disk I/O cost × no. of disk I/Os
• 4. communication cost = (unit message initiation cost × no. of

messages)+ (unit transmission cost × no. of bytes)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 82 / 121

Cost-Related Issues

Distributed Query Optimization (13)

Response Time

◮ The response time is the elapsed time between the initiation and the

completion of a query.

◮ Processing and communication costs that are incurred in sequence in

a component count at most once.

◮ If several sequential tasks are executed in parallel, the cost that is

counted is the maximum cost of all those tasks.

◮

• 1. Response time = CPU time + I/O time + communication time
• 2. CPU time = unit instruction time × no. of sequential instructions
• 3. I/O time = unit I/O time × no. of sequential I/Os
• 4. communication time = (unit message initiation time × no. of

sequential messages) + (unit transmission time × no. of sequential bytes

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 83 / 121

Cost-Related Issues

Distributed Query Optimization (14)

Some Cost Factors

wide-area networks

◮ Message initiation and transmission costs are

relatively high.

◮ Local processing cost is comparatively low (fast

mainframes or minicomputers)

◮ Ratio of communication to I/O costs is high (2-digits to

1-digit?). local-area networks

◮ Communication and local processing costs are

comparable.

◮ Ratio of communication to I/O costs is not high (close

to 1:1?).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 84 / 121

Cost-Related Issues

Distributed Query Optimization (15)

Example: Total Cost v. Response Time

◮ Assume that:

◮ only the communication cost is considered ◮ one message conveys one unit of work

(e.g., a tuple)

◮ Let UM denote the unit message initialization time

and UT the unit transmission time. Let Tsend(r, s, t) denote the time to send r from s to t.

◮ Total time = (n + m)UM + (np + mq)UT ◮ Response time = max{Tsend(n, 1, 3), Tsend(m, 2, 3)} ◮ Tsend(n, 1, 3) = nUM + npUT ◮ Tsend(m, 2, 3) = mUM + mqUT ◮ If n = 900, m = 1, 000, p = 90, and q = 100, then

◮ Total time = 1, 900UM + 181, 000UT ◮ Response time = 1, 000UM + 100, 000UT AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 85 / 121

Join Ordering in DQP

Distributed Query Optimization (16)

Join Ordering in Fragment Queries

◮ Given an n-ary relation R with attributes A1, . . . , An, let |R| denote the

cardinality of R, and let length(Ai) denote the (possibly average) length in bytes of a value from the domain of Ai, in which case the (possibly average) length of a tuple in R is length(R) = n

i=1 length(Ai).

◮ Let size(R) = |R| × length(R). ◮ Given two relations R and S that are not co-located, we ship R to the site of

S if size(R) ≤ size(S) and we ship S to the site of R if size(S) < size(R).

◮ For many relations, there may be too many alternatives. ◮ Also, computing the cost of all alternatives and selecting the best one

depends on computing the size of intermediate relations, which is difficult.

◮ In practice, heuristics are needed.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 86 / 121

Join Ordering in DQP

Distributed Query Optimization (17)

Join Ordering: An Example (1)

◮ Consider the 2-way join

PROJ ⊲ ⊳PNO (ASG ⊲ ⊳ENO EMP)

◮ The join graph shows the sites where each

relation is, and there is an edge between two relations if an equijoin on the edge label is required.

◮ The many different execution alternatives

are shown next, with a double-shafted arrow denoting the shipment of the relation in the left to the site in the right, and the ’@’ sign denoting that the left-hand side expression is evaluated at the site in the right-hand side.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 87 / 121

Join Ordering in DQP

Distributed Query Optimization (18)

Join Ordering: An Example (2)

1.

1.1 EMP ⇒ 2 1.2 EMP’ ← EMP ⊲ ⊳ ASG @ 2 1.3 EMP’ ⇒ 3 1.4 EMP’ ⊲ ⊳ PROJ @ 3

2.

2.1 ASG ⇒ 1 2.2 EMP’ ← EMP ⊲ ⊳ ASG @ 1 2.3 EMP’ ⇒ 3 2.4 EMP’ ⊲ ⊳ PROJ @ 3

3.

3.1 ASG ⇒ 3 3.2 ASG’ ← ASG ⊲ ⊳ PROJ @ 3 3.3 ASG’ ⇒ 1 3.4 ASG’ ⊲ ⊳ EMP @ 1

4.

4.1 PROJ ⇒ 2 4.2 PROJ’ ← PROJ ⊲ ⊳ ASG @ 2 4.3 PROJ’ ⇒ 1 4.4 PROJ’ ⊲ ⊳ EMP @ 1

5.

5.1 EMP ⇒ 2 5.2 PROJ ⇒ 2 5.3 PROJ ⊲ ⊳ (ASG ⊲ ⊳ EMP)@ 2

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 88 / 121

Join Ordering in DQP

Distributed Query Optimization (19)

Join Ordering: An Example (3)

• 1. An alternative to enumerating all possibilities is to use the heuristic of

considering only the sizes of the operands and assuming that the cardinality of the join is the product of the input cardinalities.

• 2. In this case, relations are ordered by increasing sizes and the order of

execution is given by this ordering and the join.

• 3. For example, the order (EMP, ASG, PROJ) could use Strategy 1, and

the order (PROJ, ASG, EMP) could use Strategy 4.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 89 / 121

Join Ordering in DQP

Distributed Query Optimization (20)

Approaches Based on Semijoins (1)

◮ Consider the join of two relations R[A] (located at site 1) and S[A]

(located at site 2).

◮ One could evaluate R ⊲

⊳A S.

◮ Alternatively,one could evaluate one of the equivalent semijoins:

R ⊲ ⊳A S ⇔ (R ⋉A S) ⊲ ⊳A S ⇔ R ⊲ ⊳A (S ⋉A R) ⇔ (R ⋉A S) ⊲ ⊳A (S ⋉A R)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 90 / 121

Join Ordering in DQP

Distributed Query Optimization (21)

Approaches Based on Semijoins (2)

• 1. Using a join:

1.1 R ⇒ 2 1.2 R ⊲ ⊳A S@ 2

• 2. Using a semijoin:

2.1 S’ ← πA(S) 2.2 S’ ⇒ 1 2.3 R’ ← R ⋉A S’ @ 1 2.4 R’ ⇒ 2 2.5 R’ ⊲ ⊳A S @ 2

Semijoin is better if size(πA(S)) + size(R ⋉A S)) < size(R)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 91 / 121

Join Ordering in DQP

Summary

Distributed Query Processing

◮ There is an evolutionary continuum from centralized to distributed

query optimization.

◮ Localization and reduction are the main techniques by which a

heuristically-efficient distributed QEP can be arrived at.

◮ In wide-area distributed query processing (DQP), communication

costs tend to dominate, although in local-area networks this is not the case.

◮ The join ordering problem remains, here too, an important one.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 92 / 121

Join Ordering in DQP

Advanced Database Management Systems

Data Integration Strategies Alvaro A A Fernandes

School of Computer Science, University of Manchester

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 93 / 121

Outline

Data Integration: Problem Definition Process Alternatives View-Based Data Integration Schema Matching, Mapping and Integration Dataspaces

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 94 / 121

Data Integration: Problem Definition

Data Integration (1)

Problem Definition

◮ Data(base) integration is the process as a result of which a set of

component DBMSs are conceptually integrated to form a multi-DBMS, i.e., a DDBMS that offers a single, logically coherent schema to users and applications.

◮ Equivalently, given existing databases with their Local Conceptual

Schemas (LCSs), data integration is the process by which they are integrated into a Global Conceptual Schema (GCS).

◮ A GCS is also called a mediated schema, or, more simply, a global

schema.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 95 / 121

Data Integration: Problem Definition

Data Integration (2)

Some Assumptions, Some Issues

◮ In general, the problem only arises if the component DBMSs already

exist, so data integration is typically a bottom-up process.

◮ In some respects, it can be conceived of as the reverse of the data

distribution (i.e., fragmentation and allocation) problem.

◮ One of the most important concerns in data integration is the level of

heterogeneity of the component DBMSs.

◮ This, in turn, is strongly linked to the degree of autonomy that each

component DBMSs enjoys and exercises.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 96 / 121

Process Alternatives

Data Integration (3)

Some Alternatives

Physical Integration : in this case, the source databases are integrated and the outcome is materialized. It is the more common practice in data warehousing. Logical Integration in this case, the global schema that emerges from integrating the sources remains virtual. It is the more common practice when the component DBMSs enjoy autonomy (e.g., in scientific contexts, where different research groups maintain different data resources but still allow them to be part of a multi-DBMS of interest to them and to others).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 97 / 121

Process Alternatives

Data Integration (4)

A Bottom-Up Process

◮ The most widely-used approach

involves:

◮ translation ◮ Each LCS abstracts over a

data source.

◮ A translator maps across to

and from concepts in the LCS and concepts in an intermediate schema (IS).

◮ integration ◮ The ISs are cast in an

interlingua, a canonical formalism in which the LCSs of the participating sources can be cast.

◮ The integrator uses the ISs

to project out the GCS to users and applications.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 98 / 121

Process Alternatives

Data Integration (5)

Dealing with Autonomy and Heterogeneity

◮ In contexts where heterogeneity is the norm (e.g., when the

multi-DBMS is formed from public resources) the translators are

• ften referred to as wrappers and the integrator is referred to as the

mediator.

◮ Wrappers can reconcile different kinds of heterogeneity, e.g.:

infrastructural including those stemming from the system software

• r network level

syntactic including those relating to data model and query languages (e.g., generating a relational view of a spreadsheet) semantic which are the hardest to capture and maintain in sync

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 99 / 121

View-Based Data Integration

Data Integration (6)

Schemas as Views

◮ There are two major possibilities to relate a GCS and its LCSs by

means of views: Global-As-View (GAV) : in this case, the LCSs are the extents over which one writes a set of views that, together, comprise the GCS against which global queries are formulated. Local-As-View (LAV) : in this case, the GCS is assumed to exist and each LCS is treated as if it were a view over this postulated GCS.

◮ We will focus on GAV, and a simple example of how it works is

coming soon.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 100 / 121

Schema Matching, Mapping and Integration

Database Integration Tasks (1)

Postulating Semantic Equivalences

◮ Assume three distributed,

heterogeneous, autonomous data sources, S1-S3.

◮ The first task is to postulate that

there are (1:1, 1:n, m:n) relationships between tables and columns in different sources.

◮ This is done by matching at

schema-level, i.e., using schema names and structures, and at instance-level, i.e., using attribute values and structures.

◮ The dashed lines show some

postulated equivalences, explicitly, at column/attribute level (and, for simplicity, only implicitly, at table/relation level).

◮ Lighter attributes with dark borders denote

primary keys.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 101 / 121

Schema Matching, Mapping and Integration

Database Integration Tasks (2)

Postulating Mappings

◮ From postulated equivalences, the

next task is to write view expressions that define constructs in one schema in terms of one or more other schemas.

◮ For example, one might define S3 in

terms of S1 and S2 with the following mappings: R ← πx→a,m→b,n→c(X ⊲ ⊳x=y Y ) S ← πt→d,r+q→e(T) ∪ πv→d,w→e(U)

◮ When written as above, we often call

the left-hand side of a mapping, the head, and the right-hand side, the body.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 102 / 121

Schema Matching, Mapping and Integration

Database Integration Tasks (3)

Query Evaluation over Integrated Schemas

◮ From postulated mappings,

the next task is to issue queries against the integrated schema.

◮ Assume a query against S3:

γavg(e)(σd>5(S)) ≡ γavg(e)(σd>5(Q ∪ Q′)) i.e., we rewrite using the mappings that define S in S3 in terms of T and U in S2 and S1, resp.: S2 : Q ← πt→d,r+q→e(T) S1 : Q′ ← πv→d,w→e(U)

◮ The subqueries Q and Q′ run

remotely at S2 and S1, resp..

◮ Results are shipped to S3

where the union runs locally.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 103 / 121

Schema Matching, Mapping and Integration

Database Integration Tasks (4)

Schema Matching (1)

There are two major kinds of heterogeneity that make schema matching a hard problem: Structural (or Syntactic) Heterogeneity

◮ Type conflicts (e.g., address as string v. address as

struct)

◮ Dependency conflicts (e.g., net salary plus tax v. salary) ◮ Key conflicts (e.g., absence of foreign keys that would

be required) Schematic (or Semantic) Heterogeneity These are conflicts that arise from the fact that the designers

• f the GCS and the LCS have different underlying ontologies

in mind, i.e., they conceptualize the database domain in different terms.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 104 / 121

Schema Matching, Mapping and Integration

Database Integration Tasks (5)

Schema Matching (2)

Semantic heterogeneity takes many forms, including:

◮ Synonyms, i.e., when two words can be interchanged in a context they are

said to be synonymous relative to that context. For example, in sport, the word match can be synonymous with tie.

◮ Homonyms, i.e., when two words are spelled the same way but have

different meanings they they are said to be homonymous. For example, we may want to have an attribute spelled ’price’ in the GCS and find it in an LCS but it may have a different meaning there (e.g., it excludes VAT, which is not what the GCS expects).

◮ Hypernyms, which are words that are more generic than a given word. For

example, the GCS may expect a relation ’employees’ not to discriminate between temporary and permanent staff, whereas in some LCS may only store in ’employees’ the permanent staff (e.g., because it stores temporary staff under, say, ’temps’).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 105 / 121

Schema Matching, Mapping and Integration

Database Integration Tasks (6)

Schema Matching (3)

◮ There are other complications too:

◮ Insufficient schema and instance information: how can one find out

how a derived attribute (e.g., VAT) is calculated?

◮ Subjectivity of the matching: how can one be sure that the

correspondence (e.g., between two relations named ’employees’) is valid for all instances?

◮ Many issues also conspire to make schema matching hard:

◮ Schema-level versus instance-level matching: which do we use? Both?

If so, which weight does each have?

◮ Element-level versus structure-level matching: if we find a match for an

attribute but all other attributes in the same relation do not match, do we trust the match?

◮ Matching cardinality is hard without additional information, as it is not

normally captured in DDLs (although XMLSchema, e.g., can do).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 106 / 121

Schema Matching, Mapping and Integration

Database Integration Tasks (7)

Combined Schema Matching Approaches

◮ One way to strengthen the validity of the decision, it is possible to

use multiple matchers (i.e., different similarity-assigning algorithms).

◮ This allows for specialization, e.g., different matchers may focus on

different domains (e.g., names, or telephone numbers, or addresses, etc.)

◮ A meta-matcher integrates these into one prediction (e.g., taking the

(possibly weighted) mean of the similarity values computed by individual matchers).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 107 / 121

Schema Matching, Mapping and Integration

Database Integration Tasks (8)

Schema Integration

◮ Once correspondences are deemed

valid, we can use them to create a GCS.

◮ While matching can (and is)

essentially an automated process, selecting matches to become mappings and combining these mappings into a GCS is largely a manual process, i.e., in a rule-based approach, like the previous examples, the rules are not normally generated automatically.

◮ Approaches to data integration are

illustrated in the figure.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 108 / 121

Dataspaces

Dataspaces as a Data Integration Approach (1)

The Question of Cost

◮ What we have described so far can be called classical, mediator-based

data integration.

◮ It delivers high-quality results early but with high upfront costs due to

the need for human expertise in making up for the shortcomings of matching and mapping derivation.

◮ Dataspaces

[Franklin et al., 2005, Halevy et al., 2006, Hedeler et al., 2009] are a new approach to data integration: it automates the bootstrapping of an integrated view, accepts the lower-quality of results early on, but aggressively seeks and uses feedback to improve them over time.

◮ The idea is that users get some results quickly at near to no cost: if

this motivates them, they pay some cost in the form of feedback as their need spurs them.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 109 / 121

Dataspaces

Dataspaces as a Data Integration Approach (2)

The Question of Quality v. Cost v. Time

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 110 / 121

Dataspaces

Dataspaces as a Data Integration Approach (3)

The Broad Aim of a Dataspace Management System

◮ Given a set of data sources, a dataspace management systems

(DSpMS) aims to obtain the best mappings with minimal human intervention.

◮ This means bootstrapping the set-up stage (i.e., the postulation of

semantic equivalences and the mappings derived them) using automated means.

◮ This also means being intelligent and efficient in seeking as few and

as useful feedback instances from users as possible and, once they are

• btained, making the most of them for improving results

[Belhajjame et al., 2010].

◮ One wants to pay as little as possible as late as possible and still

• btain excellent results for the effort spent.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 111 / 121

Dataspaces

Dataspace Architecture (1)

Seen as a Stack

◮ As we saw, classical integration

involves layering a mediator over data sources, which are assumed to be fully-fledged databases.

◮ The same holds for dataspaces,

except that the mediator (i.e., the ability to translate queries against an integrated schema into queries against sources, stitching the partial results into integrated ones

• n the return journey) is powered

by automatically derived mappings from automatically derived matches.

◮ For this later task, some have used

model management techniques (essentially an algebra over schema constructs, including mappings and matches) [Bernstein and Melnik, 2007].

◮ A dataspace is then seen to be

unique in introducing improvement via feedback.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 112 / 121

Dataspaces

Dataspace Architecture (2)

Seen as a Composition of Algebras (1)

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 113 / 121

Dataspaces

Dataspace Architecture (3)

Seen as a Composition of Algebras (2)

◮ A DSpMS is a DBMS: it retains the ability to evaluate queries over

sources to produce the specified results.

◮ A DSpMS is also a data integration system: it retains the ability to

use mappings and schemas over many distributed resources and use the former to make the latter seem an integrated source.

◮ In one approach, a DSpMS is also a model management system

[Hedeler et al., 2010]: it has the ability to sample sources and match them to generate correspondences as well as to operate on schemas (e.g., merge, compose, subtract, extract schema constructs).

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 114 / 121

Dataspaces

Dataspace Architecture (4)

Seen as a Composition of Algebras (3)

◮ In this approach, a significant part of a DSpMS is an engine to

evaluate algebraic operations over schemas, matches and correspondences.

◮ These operations are like programs that a DSpMS executes to allow

users to derive many integrated views over a collection of resources, rather than a single one.

◮ We can see, therefore, that what is truly unique to a DSpMS is the

use of feedback for improving integration mappings that were generated algorithmically and are, for this reason, likely to produce poor quality results.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 115 / 121

Dataspaces

Summary

Data Integration Strategies

◮ With the explosion in the availability of networked data and

computational resources, the data integration problem has become an extremely important one.

◮ Superimposing a global conceptual schema over local ones is as

important a task as it is costly.

◮ However, view-based techniques can can be used to great effect. ◮ The greatest hurdle remains the reconciliation of schematic

heterogeneity, the upfront cost of which is often prohibitive.

◮ The notion of a dataspace has been introduced recently to

characterize a pay-as-you-go approach to data integration, i.e., avoiding having to pay high upfront costs.

◮ The idea is that conflict reconciliation happens over time,

incrementally, driven by the assimilation of user feedback.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 116 / 121

Dataspaces

Acknowledgements

The material presented mixes original material by the author as well as material adapted from

◮ [¨

Oszu and Valduriez, 1999] The author gratefully acknowledges the work of the authors cited while assuming complete responsibility any for mistake introduced in the adaptation of the material.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 117 / 121

Dataspaces

References (1)

Belhajjame, K., Paton, N. W., Embury, S. M., Fernandes, A. A. A., and Hedeler, C. (2010). Feedback-based annotation, selection and refinement of schema mappings for dataspaces.

In Manolescu, I., Spaccapietra, S., Teubner, J., Kitsuregawa, M., L´ eger, A., Naumann, F., Ailamaki, A., and ¨ Ozcan, F., editors, EDBT, volume 426 of ACM International Conference Proceeding Series, pages 573–584. ACM. http://doi.acm.org/10.1145/1739041.1739110.

Bernstein, P. A. and Melnik, S. (2007). Model management 2.0: manipulating richer mappings.

In Chan, C. Y., Ooi, B. C., and Zhou, A., editors, SIGMOD Conference, pages 1–12. ACM. http://doi.acm.org/10.1145/1247480.1247482.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 118 / 121

Dataspaces

References (2)

Franklin, M. J., Halevy, A. Y., and Maier, D. (2005). From databases to dataspaces: a new abstraction for information management. SIGMOD Record, 34(4):27–33.

http://doi.acm.org/10.1145/1107499.1107502.

Halevy, A. Y., Franklin, M. J., and Maier, D. (2006). Principles of dataspace systems.

In Vansummeren, S., editor, PODS, pages 1–9. ACM. http://doi.acm.org/10.1145/1142351.1142352.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 119 / 121

Dataspaces

References (3)

Hedeler, C., Belhajjame, K., Mao, L., Paton, N. W., Fernandes, A.

• A. A., Guo, C., and Embury, S. M. (2010).

Flexible dataspace management through model management.

In Daniel, F., Delcambre, L. M. L., Fotouhi, F., Garrig´

• s, I., Guerrini, G.,

Maz´

• n, J.-N., Mesiti, M., M¨

uller-Feuerstein, S., Trujillo, J., Truta, T. M., Volz, B., Waller, E., Xiong, L., and Zim´ anyi, E., editors, EDBT/ICDT Workshops, ACM International Conference Proceeding Series. ACM. http://doi.acm.org/10.1145/1754239.1754241.

Hedeler, C., Belhajjame, K., Paton, N. W., Campi, A., Fernandes, A.

• A. A., and Embury, S. M. (2009).

Dataspaces.

In Ceri, S. and Brambilla, M., editors, SeCO Workshop, volume 5950 of Lecture Notes in Computer Science, pages 114–134. Springer. http://dx.doi.org/10.1007/978-3-642-12310-8 7.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 120 / 121

Dataspaces

References (4)

¨ Oszu, M. T. and Valduriez, P. (1999). Principles of Distributed Database Systems. Prentice Hall International, 2nd edition.

AAAF (School of CS, Manchester) Advanced DBMSs 2011-2012 121 / 121