VTI, Indexed Lookup and the Query Optimizer

[Westerfeld, Kurt]

OK, my head is swimming trying to sort through all the issues regarding query performance and VTI-based tables.  We have implemented several VTI tables that when joined together cause massive performance problems.   I have narrowed down the issues I have to two areas:   The VTI “2” version instances are marked reusable, and thus on each “next” call from the DRDA stack (we’re using either ODBC or JDBC interfaces) our VTI is not being constructed, but its last result is being “reopened.”  At any rate, our executeQuery() method is called once per row of one side of a join.  This is a clear optimization we should try and tackle. That being said (1), the optimization in (1) doesn’t help a whole lot because table scans are used to compare rows between two VTI tables.  That is, one side of the JOIN is scrolled forward, but the other side of the JOIN is a table scan.   What can be done about this?  It looks like FromVTI.java implements Optimizable, which I’m assuming is the interface to supply indexed lookup.  But, like I said, my head is swimming trying to figure out if I either 1) don’t understand how to enable an indexed lookup for my VTI or 2) if it’s not even possible to do so, in which case I should start to hack in that ability.   As always, any help is greatly appreciated! ________________________________________________________________________   Kurt Westerfeld