Databases Reference
In-Depth Information
We can show an example of the commutativity rule like this:
1 Join 5 - > 5 Join 1
Listing 5-22.
Since the new alternative is equivalent to the original operator, it is placed in the same
group, as shown in Table 5-9, along with two other example operators which were added
using the same commutativity rule.
Group 6
Join 3 & 4
Join 1 & 5
Join 5 & 1
Group 5
Join 2 & 4
Join 4 & 2
Group 4
Scan Customer
Group 3
Join 1 & 2
Join 2 & 1
Group 2
Scan Individual
Group 1
Scan Contact
Table 5-9:
Memo structure after commutativity rule.
Given that this is a simplified example, and I imagine you've got the picture by now,
I'll stop generating logical alternatives at this point. However, rest assured that the
Query Optimizer has many other transformation rules in its arsenal with which to
generate alternatives.
Towards the end of the process, after some implementation rules are applied, physical
operators will be added to the memo structure. A few of them have been added to Table
5-10, and they include data access operators like Clustered Index Scan, and physical join
operators like Nested Loops Join, together with Merge Join and Hash Join.
