Download Optimizing Database Queries: Compilation, Execution Plans, Table Scanning, Cost Parameters and more Assignments Deductive Database Systems in PDF only on Docsity!
1
CS 411
Database Systems
14: Query Optimization
2
Announcements
- Homework 3 will be posted after this class
- Due at 3 pm on Wednesday, November 14
- Graduate project stage 2 due on November 14
- Project stage 4 due on Friday, November 16
- Post your URL on the class newsgroup. 3
Query Compilation
SQL query Query compilation Query execution query plan storage data metadata Parse query Select logical query plan Select physical plan SQL query query expression tree logical query plan tree physical query plan tree 4
Review
- Logical/physical operators
- Cost parameters and sorting
- One-pass algorithms
- Nested-loop joins
- Two-pass algorithms 5
Query Execution Plans
Purchase Person Buyer=name City=‘urbana’ phone>’ 5430000 ’ sname (Simple Nested Loop Join) SELECT S.sname FROM Purchase P, Person Q WHERE P.buyer=Q.name AND Q.city=‘urbana’ AND Q.phone > ‘ 5430000 ’ ! Query Plan:
- logical tree
- implementation choice at every node
- scheduling of operations. (Table scan) (Index scan) Some operators are from relational algebra, and others (e.g., scan, group) are not. 6
Cost Parameters
- Cost parameters
- M = number of blocks that fit in main memory
- B(R) = number of blocks holding R
- T(R) = number of tuples in R
- V(R,a) = number of distinct values of the attribute a
- Estimating the cost:
- Important in optimization
- Compute I/O cost only
- We compute the cost to read the tables
- We don’t compute the cost to write the result (because pipelining)
7
Scanning Tables
- The table is clustered (I.e. blocks consists only of records from this table): - Table-scan: if we know where the blocks are - Index scan: if we have a sparse index to find the blocks
- The table is unclustered (e.g. its records are placed on blocks with other tables) - May need one read for each record 8
Scanning Clustered and
UnclusertedTables
Disk R Disk (clustered) R (B(R) = 2)^ 2 Reads (unclustered) 4 Reads (T(R) = 4) 9
Duplicate elimination "(R)
when B("(R)) <= M
R Input buffer Scan before? M- 1 buffers (Hash table) Output buffer B(R) = 6 T(R) = 12 Secondary storage 5 8 7 4 3 4 12 10 2 5 6 11 M = 8 (^15274384) 7 8 3 4 5 12 h(x) = x mod 7 10 2 6 11 1056211 5812101147362 Cost: B(R) Hash values 0 1 2 3 4 5 6 10
What if B("(R)) > M?
- Two-pass algorithms based on
- Sorting
- Hashing
- Indexing 11
Duplicate elimination "(R)
when B(d(R)) > M
Two-Pass Algorithms Based on Sorting
- Simple idea: sort first, then eliminate duplicates
- Step 1 : sort runs of size M, write
- Step 2 : merge M- 1 runs, but include each tuple only once - Cost: B(R)
- Total cost: 3 B(R), Assumption: B(R) <= M^2 Q: Why? Because we^ sort M * (M - 1) blocks at most. (we merge M-1 subsorted lists of size M.)^12
Two-Pass Algorithms Based on Sorting
M buffers Same M buffers R Sorted sublists of R First block Compare the first records
Partitioned
Hash-Join
! Read in a partition of R, hash it using h2 (<> h!). Scan matching partition of S, search for matches. B main memory buffers Disk Buckets for R 1 2 M- Disk Buckets for S 1 2 M- h 2
Partitioned
Hash-Join
! Read in a partition of R, hash it using h2 (<> h!). Scan matching partition of S, search for matches. B main memory buffers Disk Join Result Disk Buckets for R 1 2 M- Disk Buckets for S 1 2 M- h 2 Input buffer Output buffer 21
Partitioned Hash Join
- Cost: 3 B(R) + 3 B(S)
- Assumption: min(B(R), B(S)) <= M^2 22
Join based on sorting and hashing
Join based on sorting Join based on hashing Approximate M required Disk I/O SQRT(max(B(R),B(S)) 5(B(R)+ B(S)) SQRT(B(S)) where B(S) <= B(R) 3(B(R) + B(S))
Indexed Based Algorithms
- In a clustered index all tuples with the same value of the key are clustered on as few blocks as possible a a a a a a a a a a
Index Based Selection
- Selection on equality: !a=v(R)
- Clustered index on a: cost B(R)/V(R,a)
- Unclustered index on a: cost T(R)/V(R,a)
25
Index Based Join
• R S
- Assume S has an index on the join attribute
- Iterate over R, for each tuple fetch corresponding tuple(s) from S
- Assume R is clustered. Cost:
- If index is clustered: B(R) + T(R)B(S)/V(S,a)
- If index is unclustered: B(R) + T(R)T(S)/V(S,a)
26
Index Based Join
- Assume both R and S have a sorted index (B+ tree) on the join attribute
- Then perform a merge join (called zig-zag join)
- Cost: B(R) + B(S) Index Index 1 3 4 4 4 5 6 2 2 4 4 6 7 27
Query Optimization
28
Optimization
- Chapter 16
- At the heart of the database engine
- Step 1 : convert the SQL query to some logical plan
- Step 2 : find a better logical plan, find an associated physical plan
SQL – > Logical Query Plans
Converting from SQL to Logical Plans
Select a 1 , …, an From R 1 , …, Rk Where C #a1,…,an(! (^) C(R 1 $ R 2 $ … $ Rk)) #a1,…,an(% (^) b1, …, bm, aggs (! (^) C(R 1 $ R 2 $ … $ Rk))) Select a 1 , …, an From R 1 , …, Rk Where C Group by b 1 , …, bl
37
The three components of an optimizer
We need three things in an optimizer:
- Algebraic laws
- An optimization algorithm
- A cost estimator 38
Algebraic Laws
39
Algebraic Laws
- Commutative and Associative Laws
- R & S = S & R, R & (S & T) = (R & S) & T
- R! S = S! R, R! (S! T) = (R! S)! T
- R! S = S! R, R! (S! T) = (R! S)! T
- Distributive Laws
- R! (S & T) = (R! S) & (R! T) 40
Algebraic Laws
- Laws involving selection: -! (^) C AND C’(R) =! (^) C(! (^) C’(R)) =! (^) C(R)!! (^) C’(R) -! (^) C OR C’(R) =! (^) C(R) U! (^) C’(R) -! (^) C (R! S) =! (^) C (R)! S - When C involves only attributes of R -! (^) C (R – S) =! (^) C (R) – S -! (^) C (R & S) =! (^) C (R) &! (^) C (S) -! (^) C (R! S) =! (^) C (R)! S
Algebraic Laws
- Example: R(A, B, C, D), S(E, F, G) -! (^) F= 3 (R !D=E S) =? -! (^) A= 5 AND G= 9 (R !D=E S) =?
Algebraic Laws
- Example: R(A, B, C, D), S(E, F, G) -! (^) F= 3 (R !D=E S) = (R !D=E! (^) F= 3 (S)) -! (^) A= 5 AND G= 9 (R !D=E S) = !A= 5 (! (^) G= 9 (R !D=E S)) = !A= 5 (R !D=E !G= 9 (S)) = !A= 5 (R) !D=E !G= 9 (S)
