Examples of Physical Query Plan Alternatives

Similar presentations

1Examples of Physical Query Plan AlternativesSelections from Chapters 12, 14, 15The slides for this text are organized into chapters. This lecture covers Chapter 12, providing an overview of query optimization and execution.This chapter is the first of a sequence (Chapters 12, 13, 14, 15) on query evaluation that might be covered in full in a course with a systems emphasis. It can also be used stand-alone, as a self-contained overview of these issues, in a course with an application emphasis. It covers the essential concepts in sufficient detail to support a discussion of physical database design and tuning in Chapter 20.1

2Query OptimizationNOTE: Relational query languages provide a wide variety of ways in which a user can express.HENCE: system has many options for evaluating a query.Optimizer is important for query performance.Generates alternative plansChoose plan with least estimated cost.Ideally, find best plan.Realistically, consistently find a quite good one.

3A Query (Evaluation) PlanAn extended relational algebra treeAnnotations at each node indicate:access methods to use for each table.implementation methods used for each relational operator.ReservesSailorssid=sidbid=100rating > 5sname(Simple Nested Loops)(On-the-fly)ReservesSailorssid=sidbid=100rating > 5sname

4Query Optimization Multi-operator Queries: Pipelined Evaluation C B AOn-the-fly: The result of one operator is pipelined to another operator without creating a temporary table to hold intermediate result, called on-the-fly.Materialized : Otherwise, intermediate results must be materialized.CBA

15SummaryA query is evaluated by converting it to a tree of operators and evaluating the operators in the tree.There are several alternative evaluation algorithms for each relational operator.Query evaluation must compare alternative plans based on their estimated costsMust understand query optimization in order to fully understand the performance impact of a given database design on a query workload19