Thanks for the feedback. I have another clue to share about the poor performance. If I use an INNER JOIN to join two VTIs, the performance is absolutely terrible. If I use the equivalent "FROM" and ID=ID syntax, it works OK (not great, but OK).
Also, we register our VTI tables as VIEWs and join the VIEWs. Could this confuse the optimizer? I will try tracing the optimizer--I'll dig that out and share the results. -----Original Message----- From: Jeffrey Lichtman [mailto:[EMAIL PROTECTED] Sent: Thursday, January 12, 2006 11:25 PM To: [email protected] Subject: Re: VTI, Indexed Lookup and the Query Optimizer >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. The optimizable interface does not guarantee indexability - it's simply the interface that the optimizer uses to consider a table (virtual or not) in the optimization process. When I left the Cloudscape/Informix/Derby project, indexable VTIs had not been implemented, and I believe the feature is still unimplemented. It would be a useful feature, but would take a lot of work. Rather than try to get indexing to work on VTIs, I'd consider why the query optimizer isn't using a hash join strategy, which would probably give much better performance than the Cartesian product you're getting now. The optimizer should consider hash join on a VTI if it is the inner table of the join and it is materializable - that is, if the parameters to the constructor of the VTI are query-invariant. So, for example, if the params are all constants, hash join should be considered, but if any parameter uses a column from the joining table, hash join cannot be considered. Also, hashing can be done only on equijoins. There are trace flags to help you figure out what the optimizer is doing. The trace output is voluminous but very helpful in diagnosing problems like this. I don't remember how to activate optimizer tracing - a little help, anyone? - Jeff Lichtman [EMAIL PROTECTED] Check out Swazoo Koolak's Web Jukebox at http://swazoo.com/
