VST – complete for DB Optimizer 2.5 !
blue numbers are the percent of the table returned after the predicate filters have been applied
red numbers are the two table join sizes
green numbers are the table sizes
The idea is to start at the most selective filter and then join into keep the running row set to the smallest size.
Start at A, the most selective filter
Join to C, the smallest running row set size
Join to G, the smallest row set size
Join last to D
This path was almost 3x as fast as the default path chosen by the optimizer. I just looked at the diagram , order the tables in that fashion and used the /*+ ORDERED */ hint
Interesting items in the diagram
only fields used in the where clause are shown by default
clicking on a link shows the fields used in the join (above I’ve clicked on two links higlight two joins)
Fields with an “F” have a filter on them
There many other interactive features in the diagram.
The text for this query was
select distinct * from foo.a, foo.c, foo.d, foo.g WHERE a.planted_date = to_date(’02/10/2008′,’dd/mm/yyyy’) AND a.pears = ‘D’ AND a.green_beans = ‘1’ AND a.planted_date = c.planted_date AND a.pears = c.pears AND a.zuchinis = c.zuchinis AND a.brocoli = c.brocoli AND a.planted_date = d.planted_date AND a.pears = d.pears AND a.harvest_size = d.harvest_size AND c.oranges = d.oranges AND c.apples = d.apples AND (d.lemons = 0 OR d.lemons IS NULL) AND a.planted_date = g.planted_date AND a.pears = g.pears AND a.harvest_size = g.harvest_size AND c.oranges = g.oranges AND c.apples = g.apples AND (g.lemons = 0 OR g.lemons IS NULL) and a.zuchinis=’0236′ORDER BY a.zuchinis, a.brocoli;