db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Westerfeld, Kurt" <kwesterf...@managedobjects.com>
Subject RE: VTI, Indexed Lookup and the Query Optimizer
Date Sat, 14 Jan 2006 13:05:42 GMT
Thanks, I will play with that!

-----Original Message-----
From: Satheesh Bandaram [mailto:satheesh@Sourcery.Org]
Sent: Fri 1/13/2006 3:20 PM
To: derby-dev@db.apache.org
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.


> 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
>                                 swazoo@rcn.com
>                                 Check out Swazoo Koolak's Web Jukebox at
>                                 http://swazoo.com/

View raw message