PMIT-6102 Advanced Database Systems

1. PMIT-6102Advanced Database Systems By- JesminAkhter Assistant Professor, IIT, Jahangirnagar University

2. Lecture 07Overview of Query Processing Slide 2

3. Outline • Overview of Query Processing • Objective of Query Processing • Characterization of Query Processors • Layers of Query Processing Slide 3

4. query processor low level data manipulation Commands(relational algebra) Query Processing high level user query Slide 4

5. Selecting Alternatives Example: 7.2 This example illustrates the importance of site selection and communication for a chosen relational algebra query against a fragmented database. SELECT ENAME  Project FROM EMP,ASG  Select WHERE EMP.ENO = ASG.ENO  Join AND RESP = ”Manager” Strategy 1 ENAME(RESP = ”Manager” EMP.ENO=ASG.ENO(EMP  ASG)) Strategy 2 ENAME(EMPENO( RESP = ”Manager” (ASG))) Strategy 2 avoids Cartesian product, so is “better” Slide 5

6. What is the Problem? We assume that relations EMP and ASG are horizontally fragmented. Fragments ASG1, ASG2, EMP1, and EMP2 are stored at sites 1, 2, 3, and 4,respectively, and the result is expected at site 5. Site 1 Site 2 Site 3 Site 4 Site 5 ASG1=ENO≤“E3”(ASG) EMP1=ENO≤“E3”(EMP) EMP2=ENO>“E3”(EMP) Result ASG2=ENO>“E3”(ASG) Site 5 Site 5 result = EMP1’EMP2’ result2=(EMP1EMP2)ENO RESP = ”Manager” (ASG1ASG1) EMP1’ EMP2’ ASG1 ASG2 EMP1 EMP2 Site 3 Site 4 EMP2’=EMP2 ENOASG2’ EMP1’=EMP1 ENOASG1’ Site 1 Site 2 Site 3 Site 4 (b) Strategy B ASG1’ ASG2’ Site 1 Site 2 ASG1’= RESP = ”Manager” (ASG1) ASG2’= RESP = ”Manager” (ASG2) (a) Strategy A Slide 6

7. Cost of Alternatives To evaluate the resource consumption of the two strategies, we use a simple cost model. • Assume: • size(EMP) = 400, size(ASG) = 1000 • tuple access cost = 1 unit; tuple transfer cost = 10 units • Strategy A • produce ASG': (10+10)tuple access cost 20 • transfer ASG' to the sites of EMP: (10+10)tuple transfer cost 200 • produce EMP': (10+10) tuple access cost2 40 • transfer EMP' to result site: (10+10) tuple transfer cost 200 Total cost 460 • Strategy B • transfer EMP to site 5:400tuple transfer cost 4,000 • transfer ASG to site 5 :1000tuple transfer cost 10,000 • produce ASG':1000tuple access cost 1,000 • join EMP and ASG':40020tuple access cost 8,000 Total cost 23,000 Slide 7 Strategy A is better by a factor of 50, which is quite significant.

8. Objectives of Query Processing • The objective of query processing in a distributed context is • To transform a high-level query on a distributed database into an efficient low-level language on local databases. • The different layers are involved in the query transformation. • An important aspect of query processing is • query optimization. • Because many execution strategies are correct transformations of the same high-level query, the one that optimizes (minimizes) resource consumption should be retained. • A good measure of resource consumption is the total cost that will be incurred in processing the query • Another good measure is the response time of the query. Slide 8

9. Characterization of Query Processors • The first four characteristics hold for both centralized and distributed query processors while the next four characteristics are particular to distributed query processors in tightly-integrated distributed DBMSs. • Languages • Types of Optimization • Optimization Timing • Statistics • Decision Sites • Exploitation of the Network Topology • Exploitation of Replicated Fragments • Use of Semijoins Slide 9

10. Characterization of Query Processors • Types of Optimization • Exhaustive search • query optimization aims at choosing the “best” point in the solutionspace of all possible execution strategies. • search the solution space to predict the cost of each strategy • select the strategy with minimum cost. • Although this method is effective in selecting the best strategy, it may incur a significant processing cost for the optimization itself. • The problem is that the solution space can be large that is, there may be many equivalent strategies, even with a small number of relations.. Slide 10

11. Characterization of Query Processors • Types of Optimization • Heuristics • popular way of reducing the cost of exhaustive search • restrict the solution space so that only a few strategies are considered • regroup common sub-expressions • perform selection, projection first • replace a join by a series of semijoins • reorder operations to reduce intermediate relation size • optimize individual operations to minimize data communication. Slide 11

12. Characterization of Query Processors • Types of Optimization • Randomized strategies • Find a very good solution, not necessarily the best one, but avoid the high cost of optimization, in terms of memory and time consumption Slide 12

13. Characterization of Query Processors • Optimization Timing • Optimization can be done statically before executing the query or dynamically as the query is executed. • Static • Static query optimization is done at query compilation time. • Thus the cost of optimization may be amortized over multiple query executions. • this timing is appropriate for use with the exhaustive search method. • Since the sizes of the intermediate relations of a strategy are not known until run time, they must be estimated using database statistics. Slide 13

14. Characterization of Query Processors • Optimization Timing • Dynamic • run time optimization • database statistics are not needed to estimate the size of intermediate results • The main advantage over static query optimization is that the actual sizes of intermediate relations are available to the query processor, thereby minimizing the probability of a bad choice. • The main shortcoming is that query optimization, an expensive task, must be repeated for each execution of the query. Therefore, this approach is best for ad-hoc queries. Slide 14

15. Characterization of Query Processors • Optimization Timing • Hybrid • provide the advantages of static query optimization • The approach is basically static, but dynamic query optimization may take place at run time when a high difference between predicted sizes and actual size of intermediate relations is detected. • if the error in estimate sizes > threshold, reoptimize at run time Slide 15

16. Characterization of Query Processors • Statistics • The effectiveness of query optimization relies on statistics on the database. • Dynamic query optimization requires statistics in order to choose which operators should be done first. • Static query optimization is even more demanding since the size of intermediate relations must also be estimated based on statistical information. • statistics for query optimization typically bear on fragments, and include fragment cardinality and size as well as the size and number of distinct values of each attribute. • To minimize the probability of error, more detailed statistics such as histograms of attribute values are sometimes used. • The accuracy of statistics is achieved by periodic updating. • With static optimization, significant changes in statistics used to optimize a query might result in query reoptimization. Slide 16

17. Characterization of Query Processors • Decision Sites • Centralized decision approach • single site generates the strategy that is determines the “best” schedule • Simpler • need knowledge about the entire distributed database • Distributed decision approach • cooperation among various sites to determine the schedule (elaboration of the best strategy) • need only local information • Hybrid decision approach • one site makes the major decisions that is determines the global schedule • Other sites make local decisions that is optimizes the local sub-queries Slide 17

18. Characterization of Query Processors • Network Topology • distributed query optimization be divided into two separate problems: • selection of the global execution strategy, based on inter-site communication, and selection of each local execution strategy, based on a centralized query processing algorithm. • Wide area networks (WAN) – point-to-point • communication cost will dominate; ignore all other cost factors • global schedule to minimize communication cost • local schedules according to centralized query optimization Slide 18

19. Characterization of Query Processors • Network Topology • Local area networks (LAN) • communication costs are comparable to I/O costs. • increase parallel execution at the expense of communication cost. • The broadcasting capability of some local area networks can be exploited successfully to optimize the processing of join operators • special algorithms exist for star networks Slide 19

20. Layers of Query Processing • Four main layers are involved in distributed query processing. • each layer solves a well-defined subproblem. • The input is a query on global data . • This query is posed on global (distributed) relations • The first three layers map the input query into an optimized distributed query execution plan. • They perform the functions of query decomposition, data localization, and global query optimization. Slide 20

21. Layers of Query Processing • Query decomposition and data localization correspond to query rewriting. • The firstthree layers are performed by a centralcontrol site and use schemainformationstoredin the global directory. • The fourth layer performs distributed query execution by executing the plan and returns the answer to the query. • It is done by the local sites and the control site. Slide 21

22. Layers of Query Processing Calculus Query on Distributed Relations GLOBAL SCHEMA Query Decomposition Algebraic Query on Distributed Relations CONTROL SITE FRAGMENT SCHEMA Data Localization Fragment Query STATS ON FRAGMENTS Global Optimization Optimized Fragment Query with Communication Operations LOCAL SCHEMAS LOCAL SITES Local Optimization Optimized Local Queries Slide 22

23. Query Decomposition • Query decomposition can be viewed as four successive steps. • First, the calculus query is rewritten in a normalized form that is suitable for subsequent manipulation. • Second, the normalized query is analyzed semantically so that incorrect queries are detected and rejected as early as possible. • Third, the correct query is simplified. One way to simplify a query is to eliminate redundant predicates • Fourth, the calculus query is restructured as an algebraic query. Slide 23

24. Data Localization • The input to the second layer is an algebraic query on global relations. • The main role of the second layer is to localize the query’s data using data distribution information in the fragment schema. • This layer determines which fragments are involved in the query and transforms the distributed query into a query on fragments • Fragmentation is defined by fragmentation predicates that can be expressed through relational operators. Slide 24

25. Data Localization • A global relation can be reconstructed by applying the fragmentation rules, and then deriving a program, called a localization program of relational algebra operators which then act on fragments. • Generating a fragment query is done in two steps. • First, the query is mapped into a fragment query by substituting each relation by its reconstruction program (also called materialization program), • Second, the fragment query is simplified and restructured to produce another “good” query. Slide 25

26. Global Query Optimization • The input to the third layer is an algebraic query on fragments. • The goal of query optimization is to find an execution strategy for the query which is close to optimal. • An execution strategy for a distributed query can be described with relational algebra operators and communication primitives (send/receive operators) for transferring data between sites. • Query optimization consists of finding the “best” ordering of operators in the query, including communication operators that minimize a cost function. Slide 26

27. Distributed Query Execution • The last layer is performed by all the sites having fragments involved in the query. • Each subquery executing at one site, called a local query, is then optimized using the local schema of the site and executed. • the algorithms to perform the relational operators may be chosen Slide 27

28. Thank You Slide 28