510 likes | 629 Views
Temple University – CIS Dept. CIS616– Principles of Data Management. V. Megalooikonomou Query Processing / Optimization (based on notes by C. Faloutsos at CMU). Data-files. catalog. Overview of a DBMS. Naïve user. casual user. DBA. DML parser. DDL parser. DML precomp. trans. mgr.
E N D
Temple University – CIS Dept.CIS616– Principles of Data Management V. Megalooikonomou Query Processing / Optimization (based on notes by C. Faloutsos at CMU)
Data-files catalog Overview of a DBMS Naïve user casual user DBA DML parser DDL parser DML precomp. trans. mgr buffer mgr
Overview - detailed • Motivation - Why q-opt? • Equivalence of expressions • Cost estimation • Cost of indices • Join strategies
Why Q-opt? • SQL: ~declarative • good q-opt -> big difference • e.g., seq. Scan vs B-tree index, on P=1,000 pages
Q-opt steps bring query in internal form (e.g., parse tree) … into ‘canonical form’ (syntactic q-opt) generate alternative plans estimate cost; pick best
Q-opt - example p s TAKES STUDENT Canonical form p select name from STUDENT, TAKES where c-id=‘CIS616’ and STUDENT.ssn=TAKES.ssn s STUDENT TAKES
Q-opt - example p s TAKES STUDENT Hash join; merge join; nested loops; Index; seq. scan
Overview - detailed • Why q-opt? • Equivalence of expressions • Cost estimation • Cost of indices • Join strategies
Equivalence of expressions • … or syntactic q-opt • In short: perform selections and projections early • More details: see transformation rules in text
Equivalence of expressions • Q: How to prove a transformation rule? • A: use TRC, to show that LHS = RHS, e.g.:
Equivalence of expressions • Selections • perform them early • break a complex predicate, and push • simplify a complex predicate • (‘X=Y and Y=3’) -> ‘X=3 and Y=3’
Equivalence of expressions • Projections • perform them early (but carefully…) • Smaller tuples • Fewer tuples (if duplicates are eliminated) • project out all attributes except the ones requested or required (e.g., joining attributes)
Equivalence of expressions • Joins • Commutative , associative • Q: n-way join - how many diff. orderings? … Exhaustive enumeration too slow…
Q-opt steps bring query in internal form (e.g., parse tree) … into ‘canonical form’ (syntactic q-opt) generate alt. plans estimate cost; pick best
Cost estimation • E.g., find ssn’s of students with an ‘A’ in CIS616 (using seq. scanning) • How long will a query take? • CPU (but: small cost; decreasing; tough to estimate) • Disk (mainly, # block transfers) • How many tuples will qualify? • (what statistics do we need to keep?)
Sr #1 #2 #3 … #nr Cost estimation • Statistics: for each relation ‘r’ we keep • nr : # tuples; • Sr : size of tuple in bytes • V(A,r): number of distinct values of attr. ‘A’ • (recently, histograms, too)
Sr fr #1 #2 … #br Derivable statistics • fr: blocking factor = max# records/block (=?? ) • br: # blocks (=?? ) • SC(A,r) = selection cardinality = avg# of records with A=given (=?? )
Derivable statistics • fr: blocking factor = max# records/block (= B/Sr ; B: block size in bytes) • br: # blocks (= nr / fr )
Derivable statistics • SC(A,r) = selection cardinality = avg# of records with A=given (= nr / V(A,r) ) (assumes uniformity...) – eg: 30,000 students, 10 colleges – how many students in CST?
Additional quantities we need: • For index ‘i’: • fi: average fanout - degree (~50-100) • HTi: # levels of index ‘i’ (~2-3) • ~ log(#entries)/log(fi) • LBi: # blocks at leaf level HTi
Statistics • Where do we store them? • How often do we update them?
Q-opt steps bring query in internal form (e.g., parse tree) … into ‘canonical form’ (syntactic q-opt) generate alt. plans selections; sorting; projections joins estimate cost; pick best
Sr fr #1 #2 … #br Cost estimation + plan generation • Selections – e.g., select * from TAKES where grade = ‘A’ • Plans?
Sr fr #1 #2 … #br Cost estimation + plan generation • Plans? • seq. scan • binary search • (if sorted & consecutive) • index search • if an index exists
Sr fr #1 #2 … #br Cost estimation + plan generation seq. scan – cost? • br (worst case) • br/2 (average, if we search for primary key)
Sr fr #1 #2 … #br Cost estimation + plan generation binary search – cost? if sorted and consecutive: • ~log(br) + • SC(A,r)/fr (=#blocks spanned by qualified tuples) • -1
Sr fr #1 #2 … #br Cost estimation + plan generation estimation of selection cardinalities SC(A,r): non-trivial
Sr fr #1 #2 … #br Cost estimation + plan generation method#3: index – cost? • levels of index + • blocks w/ qual. tuples ... case#1: primary key case#2: sec. key – clustering index case#3: sec. key – non-clust. index
Sr fr #1 #2 … #br HTi Cost estimation + plan generation method#3: index – cost? • levels of index + • blocks w/ qual. tuples .. case#1: primary key – cost: HTi + 1
HTi Cost estimation + plan generation Sr method#3: index - cost? • levels of index + • blocks w/ qual. tuples #1 fr #2 case#2: sec. key – clustering index OR prim. index on non-key …retrieve multiple records HTi + SC(A,r)/fr … #br
Sr fr #1 #2 … #br Cost estimation + plan generation method#3: index – cost? • levels of index + • blocks w/ qual. tuples ... case#3: sec. key – non-clust. index HTi + SC(A,r) (actually, pessimistic...)
Cost estimation – arithmetic examples find accounts with branch-name = ‘Perryridge’ account(branch-name, balance, ...)
Arithm. examples – cont’d • n-account = 10,000 tuples • f-account = 20 tuples/block • V(balance, account) = 500 distinct values • V(branch-name, account) = 50 distinct values • for branch-index: fanout fi = 20
Arithm. examples • Q1: cost of seq. scan? • A1: 500 disk accesses • Q2: assume a clustering index on branch-name – cost?
HTi Cost estimation + plan generation Sr method#3: index – cost? • levels of index + • blocks w/ qual. tuples #1 fr #2 case#2: sec. key – clustering index HTi + SC(A,r)/fr … #br
Arithm. examples • A2: HTi + SC(branch-name, account)/f-account • HTi: 50 values, with index fanout 20 -> HT=2 levels (log(50)/log(20) = 1+) • SC(..)= # qualified records = • nr/V(A,r) = 10,000/50 = 200 tuples • SC/f: spanning 200/20 blocks = 10 blocks
Arithm. examples • A2 final answer: 2+10= 12 block accesses • (vs. 500 block accesses of seq. scan) • footnote: in all fairness • seq. disk accesses: ~2msec or less • random disk accesses: ~10msec
Overview - detailed • Motivation - Why q-opt? • Equivalence of expressions • Cost estimation • Cost of indices • Join strategies
2-way joins algorithm(s) for r JOIN s? nr, ns tuples each r(A, ...) s(A, ......) nr ns
2-way joins Algorithm #0: (naive) nested loop (SLOW!) for each tuple tr of r for each tuple ts of s print, if they match r(A, ...) s(A, ......) nr ns
2-way joins Algorithm #0: why is it bad? how many disk accesses (‘br’ and ‘bs’ are the number of blocks for ‘r’ and ‘s’)? r(A, ...) s(A, ......) nr ns nr*bs + br
2-way joins Algorithm #1: Blocked nested-loop join read in a block of r read in a block of s print matching tuples cost: br + br * bs r(A, ...) s(A, ......) nr, br ns records, bs blocks
2-way joins Arithmetic example: nr = 10,000 tuples, br = 1,000 blocks ns = 1,000 tuples, bs = 200 blocks alg#0: 2,001,000 d.a. alg#1: 201,000 d.a. r(A, ...) s(A, ......) 10,000 1,000 1,000 records, 200 blocks
2-way joins Observation1: Algo#1: asymmetric: cost: br + br * bs- reverse roles: cost= bs + bs*br Best choice? smallest relation in outer loop r(A, ...) s(A, ......) nr, br ns records, bs blocks
2-way joins Other algorithm(s) for r JOIN s? nr, ns tuples each r(A, ...) s(A, ......) nr ns
2-way joins - other algo’s sort-merge sort ‘r’; sort ‘s’; merge sorted versions (good, if one or both are already sorted) r(A, ...) s(A, ......) nr ns
hash join: hash ‘r’ into (0, 1, ..., ‘max’) buckets hash ‘s’ into buckets (same hash function) join each pair of matching buckets 2-way joins - other algo’s s(A, ......) r(A, ...) 0 1 max
More heuristics by Oracle, Sybase and Starburst (-> DB2) : in book In general: q-opt is very important for large databases. (‘explain select <sql-statement>’ gives plan) Structure of query optimizers: