16.7 Completing the physical-query-plan CS 257 – Database Systems Principles

1. 16.7 Completing the physical-query-planCS 257 – Database Systems Principles Presented by: ASMI SHAH (Class ID: 215)

2. Outline: • Principal Issues To Cover • Choosing A Selection Method • Choosing A Join Method • Pipeline V/S Materialization • Pipelining Unary Operations • Pipelining Binary Operations • Notation For Physical Query Plan • Ordering Of Physical Operations

3. Principal Issues To Cover: • Selection of algorithms to implement the operations of the query plan. • Decisions regarding when intermediate results will be materialized (created whole and stored on disk), and when they will be pipelined (created only in main memory). • Notation for physical-query-plan operators.

4. Choosing A Selection Method: • Use one comparison of the form AΘc, where A is an attribute with an index, c is a constant, and Θ is a comparison operator such as = or <. • Retrieve all tuples that satisfy the comparison from (1), using the index-scan physical operator discussed in Section 15.1.1. • Consider each tuple selected in (2) to decide whether it satisfies the rest of the selection condition. We shall call the physical operator that performs this step Filter ; it takes the condition used to select tuples as a parameter, much as the a operator of relational algebra does.

5. Choosing A Join Method: • Call for the one – pass join. • Sort-join when: • One or both arguments are already sorted on their join attribute • There are two or more joins on the same attribute. • If there is an index opportunity such as a join between R and S, where R is small and there is an index on the join attribute S.b, then we should choose an index-join. • If multipass join is needed, then hash.

6. Pipeline V/S Materialization • Materialization: The naive way to execute a query plan is to order the operations appropriately (so an operation is not performed until the argument(s) below it have been performed), and store the result of each operation on disk until it is needed by another operation. • More efficient way, Pipeline: Interleave the execution of several operations. The tuples produced by one operation are passed directly to the operation that uses it, without ever storing the intermediate tuples on disk.

7. Pipelining Unary Operations: • Unary operations - selection and projection - candidates for pipelining. • These operations are tuple-at-a-time, we never need to have more than one block for input, and one block for the output.

8. Pipelining Binary Operations: • Results of binary operations are pipelined. • One buffer to pass the result to its consumer, one block at a time. • Result varies depending on the sixe of the result and sizes of the other relations involved in the query.

9. Notation For Physical Query Plan:

10. Operators for leaves:

11. Physical operators for selection:

12. Physical sort operators: • SortScan(R,L), • Explicit physical operator Sort(L). • Used at the top of the physical-query-plan tree if the result needs to be sorted as in ORDER BY clause

13. Other relation algebra operations:

14. Ordering Of Physical Operations:

15. THANK YOU !!!