Friday, March 26, 2010

VST - complete for DB Optimizer 2.5 !

The last step is finished before we functioinal freeze on the VST diagrams in DB Optimizer 2.5. We have now added Filter Ratios to the diagram ! Functional freeze is next week and production date is targeted for May 3.
  • 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;

No comments:

Post a Comment