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:30:06 GMT
On Mon, Jun 13, 2011 at 7:18 PM, Masood Mortazavi <masoodmortazavi@gmail.com
> wrote:

> 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.
> Regards,
> Masood

As described above, I used Derby's wonderful system utility procedures to
produce a materialization of the view in question, above.
The cross query became faster than a blizzard.
So, I think I have answered my own question from a practical point of view.
Other insights are welcome.

Take care!

View raw message