Distributed Query Processing Advanced Topics in Database Management - PDF document

Distributed Query Processing Advanced Topics in Database Management (INFSCI 2711) Some materials are from Database System Concepts, Siberschatz, Korth and Sudarshan Vladimir Zadorozhny, DINS, University of Pittsburgh 1 1 Banking Example branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower (customer_name, loan_number) 2 2 1

Basic Query Processing Architecture Find the names of customers having accounts in Brooklyn select customer-name from branch, account, depositor where branch-name = “ Brooklyn ” and branch.branch-ID = account.branch-ID and account.customer-ID = depositor.customer-ID)  customer-name ( (  branch-city = “ Brooklyn ” internal query parser representation ( branch ( account depositor))) Catalog (metadata repository) optimizer Statist stics execution output evaluator about ut data plan data branch(branch-ID, branch-name) account(acc-number,branch-ID,customer-ID) 3 depositor(customer-ID,customer-name,customer-addr) 3 Relational Algebra   − 4 4 2

Sailors Database 5 5 6 6 3

7 7 Basic Steps in Query Processing Parsing and translation translate the query into its internal form. This is then translated into relational algebra. Parser checks syntax, verifies relations Evaluation The query-execution engine takes a query evaluation plan , executes that plan, and returns the answers to the query. 8 8 4

Evaluation Plan An evaluation plan defines exactly what algorithm is used for each operation, and how the execution of the operations is coordinated. 9 9 Measures of Query Cost Cost is generally measured as total elapsed time for answering query Many factors contribute to time cost  disk accesses, CPU , or network communication Typically disk access is the predominant cost in centralized system, and is also relatively easy to estimate. Measured by taking into account Number of seeks * average-seek-cost Number of blocks read * average-block-read-cost Number of blocks written * average-block-write-cost  Cost to write a block is greater than cost to read a block – data is read back after being written to ensure that the write was successful For simplicity we just use the number of block transfers from disk 10 10 5

Selection Operation File scan – search algorithms that locate and retrieve records that fulfill a selection condition. Algorithm A1 ( linear search ). Scan each file block and test all records to see whether they satisfy the selection condition. Cost estimate = b r block transfers  b r denotes number of blocks containing records from relation r If selection is on a key attribute, can stop on finding record  cost = ( b r /2) block transfers Linear search can be applied regardless of  selection condition or  ordering of records in the file, or  availability of indices 11 11 Selection Operation (Cont.) A2 (binary search). Applicable if selection is an equality comparison on the attribute on which file is ordered. Assume that the blocks of a relation are stored contiguously Cost estimate (number of disk blocks to be scanned):  cost of locating the first tuple by a binary search on the blocks –  log 2 ( b r )   If there are multiple records satisfying selection – Add transfer cost of the number of blocks containing records that satisfy selection condition Index scan – search algorithms that use an index selection condition must be on search-key of index. A3 ( primary index on candidate key, equality ). Retrieve a single record that satisfies the corresponding equality condition Cost = ( h i + 1) 12 12 6

Join Operation Several different algorithms to implement joins Nested-loop join Merge-join … Choice based on cost estimate Examples use the following information Number of records of customer : 10,000 depositor : 5000 Number of blocks of customer : 400 depositor : 100 13 13 Nested-Loop Join (Cont.) In the worst case, if there is enough memory only to hold one block of each relation, the estimated cost is n r  b s + b r block transfers If the smaller relation fits entirely in memory, use that as the inner relation. Reduces cost to b r + b s block transfers Assuming worst case memory availability cost estimate is with depositor as outer relation:  5000  400 + 100 = 2,000,100 block transfers, with customer as the outer relation  10000  100 + 400 = 1,000,400 block transfers If smaller relation ( depositor) fits entirely in memory, the cost estimate will be 500 block transfers. 14 14 7

Merge-Join 1. Sort both relations on their join attribute (if not already sorted on the join attributes). Merge the sorted relations to join them 2. Join step is similar to the merge stage of the sort-merge algorithm. 1. Main difference is handling of duplicate values in join attribute — every 2. pair with same value on join attribute must be matched 15 15 Merge-Join (Cont.) Can be used only for equi-joins and natural joins Each block needs to be read only once (assuming all tuples for any given value of the join attributes fit in memory Thus the cost of merge join is: b r + b s block transfers + the cost of sorting if relations are unsorted. 16 16 8

Query Optimization A relational algebra expression may have many equivalent expressions E.g.,  balance  2500 (  balance ( account)) is equivalent to  balance (  balance  2500 ( account)) Each relational algebra operation can be evaluated using one of several different algorithms Correspondingly, a relational-algebra expression can be evaluated in many ways. Annotated expression specifying detailed evaluation strategy is called an evaluation- plan . E.g., can use an index on balance to find accounts with balance < 2500, or can perform complete relation scan and discard accounts with balance  2500 Query Optimization : Amongst all equivalent evaluation plans choose the one with lowest cost. Cost is estimated using statistical information from the database catalog  e.g. number of tuples in each relation, size of tuples, etc. 17 17 Pictorial Depiction of Equivalence Rules 18 18 9

Multiple Transformations (Cont.) 19 19 Join Ordering Example For all relations r 1, r 2, and r 3 , ( r 1 r 2 ) r 3 = r 1 ( r 2 r 3 ) (Join Associativity) If r 2 r 3 is quite large and r 1 r 2 is small, we choose ( r 1 r 2 ) r 3 so that we compute and store a smaller temporary relation. 20 20 10

Join Ordering Example (Cont.) Consider the expression  customer_name ((  branch_city = “ Brooklyn ” ( branch)) ( account depositor)) Could compute account depositor first, and join result with  branch_city = “ Brooklyn ” ( branch) but account depositor is likely to be a large relation. Only a small fraction of the bank ’ s customers are likely to have accounts in branches located in Brooklyn it is better to compute  branch_city = “ Brooklyn ” ( branch) account first. 21 21 Cost Estimation Cost of each operator computed using statistics of input relations  E.g. number of tuples, sizes of tuples Inputs can be results of sub-expressions Need to estimate statistics of expression results To do so, we require additional statistics  E.g. number of distinct values for an attribute 22 22 11

Statistical Information for Cost Estimation n r : number of tuples in a relation r. b r : number of blocks containing tuples of r. l r : size of a tuple of r. f r : blocking factor of r — i.e., the number of tuples of r that fit into one block. V(A, r): number of distinct values that appear in r for attribute A; same as the size of  A ( r ). If tuples of r are stored together physically in a file, then: é n ù r = b ê ú r ê f ú r ê ú ê ú 23 23 Histograms Histogram on attribute age of relation person 24 24 12

Evaluation of Expressions So far: we have seen algorithms for individual operations Alternatives for evaluating an entire expression tree Materialization : generate results of an expression whose inputs are relations or are already computed, materialize (store) it on disk. Repeat. Pipelining : pass on tuples to parent operations even as an operation is being executed 25 25 Materialization Materialized evaluation: evaluate one operation at a time, starting at the lowest-level. Use intermediate results materialized into temporary relations to evaluate next-level operations. E.g., in figure below, compute and store  2500 account ( ) balance  then compute and store its join with customer, and finally compute the projections on customer-name. 26 26 13