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.