db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Masood Mortazavi <masoodmortaz...@gmail.com>
Subject Derby query optimizations / (materialized) Views and Queries
Date Tue, 14 Jun 2011 01:39:10 GMT
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
[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.

View raw message