The following represents the comparison of two explain plans for the same query.
Its tedious and unpleasant to read.
The same execution plan comparison is accomplished more simply and pleasantly with "SQL Join Topology" diagrams.
If we transform the execution plans into join topology diagrams it becomes easy to see the most important difference.
The most important difference is the starting table join.
Joins are made on only 2 tables at a time and the first 2 tables we join is the most
important decision for the execution efficiency of the SQL.
We can further expand on these ideas by adding information about the kinds of joins
and the kinds of table access
- full table scan
- index skip scan
- full index scan
resulting in a powerful graphic that allows easy identification of the differences and kinds of differences.
I think "SQL Join Topology" is a marvelous way of showing the most important information in an explain plan.
Join clouds have there limitations when the number of objects in a statement becomes large.
I'm am researching ways of indication the join topology using other visual metaphors besides join clouds.
I've started using 3 new terms regarding graphical execution plans
Join diagrams - layout the joins with master tables on top and details on the bottom
Join clouds - graphically groupings showing the order of joins in a Join diagram
Join topology - graphical combination of Join Diagrams and Join Clouds
Balanced Join Diagram
Here is a join diagram example. In order to draw the connections between tables in an SQL query all we need is the from clause shown here:
using the from clause we can lay out nodes fore each table and connection lines to the joining tables. The above diagram might be of some use, but it's limited. On the other hand if we impose order on the diagram we will have a powerful graphic.
Master Detail Join Diagram
Imposing the order of detail tables on top and master details on bottom we can start quickly identifying things in the query.
A master table returns one row when we join into it from a detail.
A detail can return multiple rows when joined into from a master.
The master should have a unique index on the join column(s) so that the optimizer knows the the relationship between the master and detail..
Given this arrangement we want to join down when possible because this will keep the result set the same size or smaller. Joining up potentially increases the result set. We want to keep the result set as small as possible for as long as possible.
Beside giving information about how to join, we see, more importantly, if there are any problems in the schema definition.