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;