Evaluation of Expression in Query Processing
Download as pptx, pdf1 like1,778 views
The document discusses query evaluation plans and optimization in a database management system. It describes how selection and projection operations can be performed on-the-fly without intermediate results. Different join algorithms like nested loops and sort-merge joins are compared and the factors affecting the choice of join algorithm, like indexes and additional selection conditions, are explained. Subquery materialization is also covered briefly.
More Related Content
What's hot (20)
Viewers also liked (6)
Similar to Evaluation of Expression in Query Processing (20)
Recently uploaded (20)
Evaluation of Expression in Query Processing
- 1. G.H Patel College of Engineering and Technology Department Of Computer Engineering DATABASE MANGMENT SYSTEM (2130703)
- 2. • QUERY EVALUATION PLAN G.H Patel College of Engg and Technology, Department Of Computer Engineering 2
- 3. ename (on the fly) planeId=100 AND rating>5 (on the fly) (nested loops) (file scan) Employees Maintenances (file scan) Method to use Sometimes it might be possible, to pipeline the result of one operator to another operator without creating a temporary table for the intermediate result. This saves in cost. When the input to a unary operator (e.g. or ) is pipelined into it, we say the operator is applied on-the-fly. G.H Patel College of Engg and Technology, Department Of Computer Engineering 3
- 4. • G.H Patel College of Engg and Technology, Department Of Computer Engineering 4
- 5. ename (on the fly) planeId=100 AND rating>5 (on the fly) Maintenances (file scan) (nested loops join) Employees (file scan) Cost for this plan: 300,000 I/Os for the join. and are done in the fly; no I/O cost for them. G.H Patel College of Engg and Technology, Department Of Computer Engineering 5
- 6. ename (on the fly) planeId=100 AND rating>5 (on the fly) Maintenances (file scan) (sort merge join) Employees (file scan) Cost for this plan: 7,500 I/Os for the join. and are done in the fly; no I/O cost for them. G.H Patel College of Engg and Technology, Department Of Computer Engineering 6
- 7. • • • • • G.H Patel College of Engg and Technology, Department Of Computer Engineering 7
- 8. • • G.H Patel College of Engg and Technology, Department Of Computer Engineering 8
- 9. • • • G.H Patel College of Engg and Technology, Department Of Computer Engineering 9
- 10. FOR EXAMPLE: SELECT * FROM STAFF WHERE ID = (SELECT MAX(MANAGER) FROM ORG) IN THIS STATEMENT, THE SUBQUERY NEEDS TO BE EVALUATED ONLY ONCE. THIS TYPE OF SUBQUERY MUST RETURN ONLY ONE ROW. IF EVALUATING THE SUBQUERY CAUSES A CARDINALITY VIOLATION (IF IT RETURNS MORE THAN ONE ROW), AN EXCEPTION IS THROWN WHEN THE SUBQUERY IS RUN. G.H Patel College of Engg and Technology, Department Of Computer Engineering 10
- 11. SUBQUERY MATERIALIZATION IS DETECTED BEFORE OPTIMIZATION, WHICH ALLOWS THE DERBY OPTIMIZER TO SEE A MATERIALIZED SUBQUERY AS AN UNKNOWN CONSTANT VALUE. THE COMPARISON IS THEREFORE OPTIMIZABLE. THE ORIGINAL STATEMENT IS TRANSFORMED INTO THE FOLLOWING TWO STATEMENTS: CONSTANT = SELECT MAX(MANAGER) FROM ORG SELECT * FROM STAFF WHERE ID = CONSTANT THE SECOND STATEMENT IS OPTIMIZABLE. G.H Patel College of Engg and Technology, Department Of Computer Engineering 11
- 12. • • • • G.H Patel College of Engg and Technology, Department Of Computer Engineering 12
- 13. G.H Patel College of Engg and Technology, Department Of Computer Engineering 13
- 14. • • • • • • G.H Patel College of Engg and Technology, Department Of Computer Engineering 14
- 15. • • • • • G.H Patel College of Engg and Technology, Department Of Computer Engineering 15
- 16. Employees (sin INT, ename VARCHAR(20), rating INT, age REAL) Maintenances (sin INT, planeId INT, day DATE, descCode CHAR(10)) SELECT ename FROM Employees NATURAL JOIN Maintenances WHERE planeId = 100 AND rating > 5; ename (planeId=100 AND rating>5 (Employees Maintenances)) G.H Patel College of Engg and Technology, Department Of Computer Engineering 16
- 17. ename planeId=100 AND rating>5 RA expressions can are represented by an expression tree. Maintenances Employees An algorithm is chosen for each node in the expression tree. G.H Patel College of Engg and Technology, Department Of Computer Engineering 17
- 18. • • QUERY EVALUATION PLANS • • • • QUERY OPTIMIZATION. G.H Patel College of Engg and Technology, Department Of Computer Engineering 18
- 19. • CLUSTERING INDEX • "PRIMARY" • NON-CLUSTERING INDEX • "SECONDARY" G.H Patel College of Engg and Technology, Department Of Computer Engineering 19
- 20. EMPLOYEES MAINTENANCES • • • • 1000 • • • • 500 G.H Patel College of Engg and Technology, Department Of Computer Engineering 20
- 21. R.ATTR = VALUE (R) • • • • • R.ATTR < VALUE (R) • WHY? • G.H Patel College of Engg and Technology, Department Of Computer Engineering 21
- 22. • • THAT’S EASY • • THIS IS MORE EXPENSIVE • • G.H Patel College of Engg and Technology, Department Of Computer Engineering 22
- 23. • • INDEX NESTED LOOPS JOIN • SIN • • ANALYSIS • • • 300,000 I/OS G.H Patel College of Engg and Technology, Department Of Computer Engineering 23
- 24. SORT-MERGE • • ANALYSIS • MAINTENANCES EMPLOYEES • • MAINTENANCES • EMPLOYEES • • (MUCH BETTER!!) G.H Patel College of Engg and Technology, Department Of Computer Engineering 24
- 25. • INDEX NESTED LOOPS JOIN • SORT-MERGE JOIN • WHY BOTHER WITH INDEX NESTED LOOPS JOIN? • INDEX NESTED LOOPS • MAINTENANCES • MAINTENANCES MAINTENANCES EMPLOYEES G.H Patel College of Engg and Technology, Department Of Computer Engineering 25
- 26. • • MAINTENANCES EMPLOYEES • MAINTENANCES • G.H Patel College of Engg and Technology, Department Of Computer Engineering 26
- 27. • OBSERVE THAT THE CHOICE OF INDEX NESTED LOOPS JOIN IS BASED ON CONSIDERING THE QUERY AS A WHOLE, INCLUDING THE EXTRA SELECTION ON MAINTENANCES, RATHER THAN JUST THE JOIN OPERATION BY ITSELF. • QUERY OPTIMIZATION • G.H Patel College of Engg and Technology, Department Of Computer Engineering 27
- 28. • • G.H Patel College of Engg and Technology, Department Of Computer Engineering 28
- 29. G.H Patel College of Engg and Technology, Department Of Computer Engineering 29