db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: VTI, Indexed Lookup and the Query Optimizer
Date Fri, 13 Jan 2006 05:26:23 GMT
Is this what you were referring to, Jeff?

 derby.language.logQueryPlan  Function

When this property is set to true, Derby writes the query plan information
into the derby.log file for all executed queries.

This property is useful for debugging to know what query plan was chosen by
the optimizer.



On 1/12/06, Jeffrey Lichtman <swazoo@rcn.com> wrote:
>
>
> >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
>                                 swazoo@rcn.com
>                                 Check out Swazoo Koolak's Web Jukebox at
>                                 http://swazoo.com/
>
>

Mime
View raw message