Thanks, I will play with that!
-----Original Message----- From: Satheesh Bandaram [mailto:[EMAIL PROTECTED] Sent: Fri 1/13/2006 3:20 PM To: [email protected] Subject: Re: VTI, Indexed Lookup and the Query Optimizer Jeffrey Lichtman wrote: > 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. Also making the VTI instantiable only once will also force optimizer to perform hash join, if the VTI is choosen as the inner table. VTIs can inform optimizer by supportsMultipleInstantiations() returning false. Satheesh > > 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/ > >
