Completing the Physical-Query-Plan and Chapter 16 Summary (16.7-16.8)

1. Completing the Physical-Query-Plan and Chapter 16 Summary (16.7-16.8) CS257 Spring 2009 Professor Tsau Lin Student: Suntorn Sae-Eung Donavon Norwood

2. 16.7 Completing the Physical-Query-Plan • 3 topics related to turning LP into a complete physical plan • Choosing of physical implementations such as Selection and Join methods • Decisions regarding to intermediate results (Materialized or Pipelined) • Notation for physical-query-plan operators

3. I. Choosing a Selection Method (A) • Algorithms for each selection operators 1. Can we use an created index on an attribute? • If yes, index-scan. Otherwise table-scan) 2. After retrieve all condition-satisfied tuples in (1), then filter them with the rest selection conditions

4. Choosing a Selection Method(A) (cont.) • Algorithms for each selection operators 1. Can we use an created index on an attribute? • If yes, index-scan. Otherwise table-scan) 2. After retrieve all condition-satisfied tuples in (1), then filter them with the rest selection conditions • Recall  Cost of query = # disk I/O’s • How costs for various plans are estimated from σC(R)operation 1.Cost of table-scan algorithm • B(R) if R is clustered • T(R) if R is not clustered 2.Cost of a plan picking an equality term (e.g. a = 10) w/ index-scan • B(R) / V(R, a) clustering index • T(R) / V(R, a)nonclustering index 3.Cost of a plan picking an inequality term (e.g. b < 20) w/ index-scan • B(R) / 3 clustering index • T(R) / 3 nonclustering index

5. Example Selection: σx=1 AND y=2 AND z<5 (R) - Where parameters of R(x, y, z) are : T(R)=5000, B(R)=200, V(R,x)=100, and V(R, y)=500 • Relation R isclustered • x, y have nonclustering indexes, only index on z is clustering. Selection options: • Table-scan  filter x, y, z. Cost isB(R) = 200since R is clustered. • Use index on x =1  filter on y, z. Cost is 50 sinceT(R) / V(R, x) is (5000/100) = 50 tuples, index is not clustering. • Use index on y =2  filter on x, z. Cost is 10 sinceT(R) / V(R, y) is (5000/500) = 10 tuples using nonclustering index. • Index-scan on clustering index w/ z < 5 filter x ,y. Cost is about B(R)/3 = 67

6. Example (cont.) • Costs option 1 = 200 option 2 = 50 option 3 = 10  option 4 = 67 The lowest Cost is option 3. • Therefore, the preferred physical plan • retrieves all tuples with y = 2 • then filters for the rest two conditions (x, z).

7. II. Choosing a Join Method • Determine costs associated with each join algorithms: 1. One-pass join, and nested-loop join devotes enough buffer to joining 2. Sort-join is preferred when attributes are pre-sorted or two or more join on the same attribute such as (R(a, b) S(a, c)) T(a, d) - where sorting R and S on a will produce result of R S to be sorted on a and used directly in next join 3. Index-join for a join with high chance of using index created on the join attribute such as R(a, b) S(b, c) 4. Hashing join is the best choice for unsorted or non-indexing relations which needs multi pass join.

8. III. Pipelining Versus Materialization • Materialization (naïve way) • store (intermediate) result of each operations on disk • Pipelining (more efficient way) • Interleave the execution of several operations, the tuples produced by one operation are passed directly to the operations that used it • store (intermediate) result of each operations on buffer, which is implemented on main memory

9. R IV. Pipelining Unary Operations • Unary = a-tuple-at-a-time or full relation • Pipelining Unary Operations are implemented by iterators • selection and projection are the best candidates for pipelining. Unary operation In buf Out buf Unary operation In buf Out buf M-1 buffers

10. V. Pipelining Binary Operations • Binary operations : ,  ,- , , x • The results of binary operations can also be pipelined. • Use one buffer to pass result to its consumer, one block at a time. • The extended example shows tradeoffs and opportunities

11. VI. Notation for Physical Query Plans • Several types of operators: • Operators for leaves • (Physical) operators for Selection • (Physical) Sorts Operators • Other Relational-Algebra Operations • In practice, each DBMS uses its own internal notation for physical query plan.

12. Notation for Physical Query Plans (cont.) • Operator for leaves • A leaf operand is replaced in LQP tree • TableScan(R) : read all blocks • SortScan(R, L) : read in order according to L • IndexScan(R, C): scan index attribute A by condition C of form Aθc. • IndexScan(R, A) : scan index attribute R.A. This behaves like TableScan but more efficient if R is not clustered.

13. Notation for Physical Query Plans (cont.) • (Physical) operators for Selection • Logical operator σC(R)is often combined with access methods. • If σC(R)is replaced by Filter(C), and there is no index on R or an attribute on condition C • Use TableScanorSortScan(R, L) to access R • If condition C Aθc AND D for condition D, and there is an index on R.A, then we may • Use operator IndexScan(R, Aθc) to access R and • Use Filter(D) in place of the selection σC(R)

14. Notation for Physical Query Plans (cont.) • (Physical) Sort Operators • Sorting can occur any point in physical plan, which use a notation SortScan(R, L). • It is common to use an explicit operator Sort(L) to sort relation that is not stored. • Can apply at the top of physical-query-plan tree if the result needs to be sorted with ORDER BY clause (г).

15. Notation for Physical Query Plans (cont.) • Other Relational-Algebra Operations • Descriptive text definitions and signs to elaborate • Operations performed e.g. Join or grouping. • Necessary parameters e.g. theta-join or list of elements in a grouping. • A general strategy for the algorithm e.g. sort-based, hashed based, or index-based. • A decision about number of passed to be used e.g. one-pass, two-pass or multipass. • An anticipated number of buffers the operations will required.

16. Notation for Physical Query Plans (cont.) • Example of a physical-query-plan • A physical-query-plan in example 16.36 for the case k > 5000 • TableScan • Two-pass hash join • Materialize (double line) • Store operator

17. Notation for Physical Query Plans (cont.) • Another example • A physical-query-plan in example 16.36 for the case k < 49 • TableScan • (2) Two-pass hash join • Pipelining • Different buffers needs • Store operator

18. Notation for Physical Query Plans (cont.) • A physical-query-plan in example 16.35 • Use Index on condition y = 2 first • Filter with the rest condition later on.

19. VII. Ordering of Physical Operations • The PQP is represented as a tree structure implied order of operations. • Still, the order of evaluation of interior nodes may not always be clear. • Iterators are used in pipeline manner • Overlapped time of various nodes will make “ordering” no sense.

20. Ordering of Physical Operations (cont.) • 3 rules summarize the ordering of events in a PQP tree: • Break the tree into sub-trees at each edge that represent materialization. • Execute one subtree at a time. • Order the execution of the subtree • Bottom-top • Left-to-right • All nodes of each sub-tree are executed simultaneously.

21. Summary of Chapter 16 In this part of the presentation I will talk about the main topics of Chapter 16.

22. COMPILATION OF QUERIES • Compilation means turning a query into a physical query plan, which can be implemented by query engine. • Steps of query compilation : • Parsing • Semantic checking • Selection of the preferred logical query plan • Generating the best physical plan

23. THE PARSER The first step of SQL query processing. Generates a parse tree Nodes in the parse tree corresponds to the SQL constructs Similar to the compiler of a programming language

24. VIEW EXPANSION A very critical part of query compilation. Expands the view references in the query tree to the actual view. Provides opportunities for the query optimization.

25. SEMANTIC CHECKING • Checks the semantics of a SQL query. • Examines a parse tree. • Checks : • Attributes • Relation names • Types • Resolves attribute references.

26. CONVERSION TO A LOGICAL QUERY PLAN Converts a semantically parsed tree to a algebraic expression. Conversion is straightforward but sub queries need to be optimized. Two argument selection approach can be used.

27. ALGEBRAIC TRANSFORMATION • Many different ways to transform a logical query plan to an actual plan using algebraic transformations. • The laws used for this transformation : • Commutative and associative laws • Laws involving selection • Pushing selection • Laws involving projection • Laws about joins and products • Laws involving duplicate eliminations • Laws involving grouping and aggregation

28. ESTIMATING SIZES OF RELATIONS • True running time is taken into consideration when selecting the best logical plan. • Two factors the affects the most in estimating the sizes of relation : • Size of relations ( No. of tuples ) • No. of distinct values for each attribute of each relation • Histograms are used by some systems.

29. COST BASED OPTIMIZING • Best physical query plan represents the least costly plan. • Factors that decide the cost of a query plan : • Order and grouping operations like joins, unions and intersections. • Nested loop and the hash loop joins used. • Scanning and sorting operations. • Storing intermediate results.

30. PLAN ENUMERATION STRATEGIES • Common approaches for searching the space for best physical plan . • Dynamic programming : Tabularizing the best plan for each sub expression • Selinger style programming : sort-order the results as a part of table • Greedy approaches : Making a series of locally optimal decisions • Branch-and-bound : Starts with enumerating the worst plans and reach the best plan

31. LEFT-DEEP JOIN TREES Left – Deep Join Trees are the binary trees with a single spine down the left edge and with leaves as right children. This strategy reduces the number of plans to be considered for the best physical plan. Restrict the search to Left – Deep Join Trees when picking a grouping and order for the join of several relations.

32. PHYSICAL PLANS FOR SELECTION Breaking a selection into an index-scan of relation, followed by a filter operation. The filter then examines the tuples retrieved by the index-scan. Allows only those to pass which meet the portions of selection condition.

33. PIPELINING VERSUS MATERIALIZING This flow of data between the operators can be controlled to implement “ Pipelining “ . The intermediate results should be removed from main memory to save space for other operators. This techniques can implemented using “ materialization “ . Both the pipelining and the materialization should be considered by the physical query plan generator. An operator always consumes the result of other operator and is passed through the main memory.