db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Masood Mortazavi <masoodmortaz...@gmail.com>
Subject Re: Derby query optimizations / (materialized) Views and Queries
Date Tue, 14 Jun 2011 02:18:41 GMT
On Mon, Jun 13, 2011 at 6:39 PM, Masood Mortazavi <masoodmortazavi@gmail.com
> wrote:

> I have a Derby table (T) with 10^7 records.
> This table has 5 columns.
> I have defined a Derby view (V) on one of those columns.
> To produce (V) all of (T) needs to be scanned.
> There are about 2000 records that the view (V) contains.
> Now, I "cross" (T) with (V), with an IN clause involving (V).
> I have three questions:
> [1] Is the optimizer smart enough to to scan only O( (T)*(V) ) times (with
> the V comparisons involving in-memory "materialization" of (V)), or will it
> be O( (T)*(T) ) or something else?
> [2] What is the support that Derby may be offering for
> materialized views?
> [3] If the answer to [1] is the larger number, would materializing (V)
> change things or improve them drastically, i.e. would Derby save the large
> (T) sized scan to produce (V), or even better, would it do that and also
> bring all of (V), which is rather small, into memory (in large enough chunks
> or completely) for the purposes of the cross involving IN?
> With best regards to all good Derby friends,
> - m.

I solve the materialization issue by a simple exporting of the view's select
query and then importing it.
It works well.
I will rerun my IN cross query, against the now materialized table to see
how much better or worse it does in this particular case.
The general gist of my questions above still remain.


View raw message