How does the optimizer compare different execution plans of the same query? Let's say we have two complex and structurally different execution plans, how can I know if they are providing the same result?
Asked
Active
Viewed 448 times
0
1 Answers
1
The task of an optimizer is to create several execution plans and compare them (typically by costs). Each execution plan can be seen as logical transformation.
For example: a simple join would always be the cartesian product of two tables. Each row from the first is read and combined with each row in the other table. Than the join condition is checked and the resulting row is used or discarded.
That would be our live without optimizer. The optimizer is using set theory and boolean algebra to transform it. Each transformation leads to the same result. For instance tt doesn't matter if you first sort two sets independent and join them afterwards or first join them and sort them as one.
Other optimizations are the way rows are fetched or compared. There are for example many join algorithms like nested loop join, hash join or sort-merge join. Each algorithm has different advantages and disadvantages. But they operate on the same data. The only (hypothetical) way a execution plan could lead to other results is a difference in rows stored and the index. For that reason the indices have to be always updated as a row is inserted/updated/deleted.
Jens W.
- 1,219
- 1
- 8
- 16
-
If all transformations lead to the same result, what makes the specificity of an execution plan? – maya-bf Mar 11 '14 at 14:54
-
Some execution plans are faster than others. Faster can be for example: less cpu, less io. – Jens W. Mar 11 '14 at 15:30
ROW_NUMBERordered on a non unique column. – Martin Smith Mar 11 '14 at 14:32