Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Material Type: Notes; Class: Database Sys Implement; Subject: Computer Science; University: Georgia Institute of Technology-Main Campus; Term: Unknown 2003;
Typology: Study notes
1 / 12
This page cannot be seen from the preview
Don't miss anything!
1
Ling Liu Associate Professor College of Computing, Georgia Tech
DB
z Architectures z Complexity and general methodology
z Database Consistency and Constraints z Consistent Database State and Transactions z Undo logging z Redo logging (next lecture) z Undo/Redo logging (next lecture) z Checkpoints (next lecture)
3
Query Optimization
z Iterative Approach (nested loop) z Sort-based z Hash-based z Index-based
DB
Pipelining
z To accept one or both arguments in a stream without seeing the entire relation before starting
z When pipelining is not possible, then the arguments must be materialized (sorted on disk if it is large), before beginning the processing
7
Example
city
city
(indexed on city of Supplier)
(hash-join)
DB
Example
city
city
(hash-join)
(indexed on city of Supplier)
9
Query Processing Methodology
High-level Calculus-based Query (SQL)
Query Preprocessing
Query Preprocessing
Query Optimization
Query Optimization
Algebraic Query (a tree structure) LOGICAL SCHEMA
LOGICAL SCHEMA
INTERNAL SCHEMA
INTERNAL SCHEMA Execution Schedule (file access plan)
EXTERNAL SCHEMA
EXTERNAL SCHEMA
DB
z Heuristics-based (Algebraic Rewriting and Algebraic Transformation) ÆApply transformation rules according to a general strategy z Cost-based ÆMinimize a cost function I/O cost + CPU cost subject to a set of constraints
Query Optimization
13
Algebraic Transformation
πENAME
σW.RESP = "Manager“
E.ENO = W.ENO
E
Moving selection downward (using Cascade of selections, Commutativity of selections, and Commuting selection with binary operations)
W
Strategy 2
Plan 3
DB
Algebraic Transformation
πENAME
σW.RESP = "Manager“
E.ENO = W.ENO
E
W
πENAME
σW.RESP = "Manager“
E.ENO = W.ENO
E
W
Plan 3 Plan 4
Strategy 2 (two possible plans)
15
Example SELECT ENAME FROM E,W WHERE E.ENO = W.ENO AND W.RESP = "Manager" Strategy 1 (Algebraic rewriting) z πENAME (σRESP="Manager"∧E.ENO=G.ENO (E × W)) z πENAME (σRESP="Manager"∧E.ENO=G.ENO (W × E))
Strategy 2 (algebraic transformation) z πENAME ( E (^) ENO (σRESP="Manager"(W))) z πENAME (σRESP="Manager"(W) (^) ENO E)
Query Optimization: An Example
Different join ordering
Different join ordering
DB
Assume : z card (E) = 4,000; card(W)=10, z 10% of tuples in W satisfy RESP="Manager" (selection generates 1,000 tuples) (i.e., V(W, RESP) = 10) z No index on either E or W execution time proportional to the sum of the cardinalities of the temporary relations (join cost dominates the overall cost) searching is done by sequential scanning Strategy 1 Strategy 2 Cartesian prod. = 40,000,000 Selection over W = 10, Search over all = 40,000,000 Join(4000*1000) = 4,000, 80,000,000 4,010,
Cost of Alternatives
19
Query Optimization Issues
DB
Æ“optimal” Æcombinatorial complexity in the number of relations Æcost-based
Ænot optimal Ægroup common sub-expressions Æperform selection, projection first Æreorder operations to reduce intermediate relation size Æoptimize individual operations
Query Optimization Issues
21
Query Optimization Issues
DB
z static Æ compilation ⇒ optimize prior to the execution Æ difficult to estimate the size of the intermediate results ⇒ error propagation Æ can amortize over many executions z dynamic Æ run time optimization Æ exact information on the intermediate relation sizes Æ have to reoptimize for multiple executions z hybrid Æ compile using a static algorithm Æ if the error in estimate sizes > threshold, reoptimize at run time
Query Optimization Issues
