On Mon, Jun 13, 2011 at 7:18 PM, Masood Mortazavi <[email protected] > wrote:
> > > On Mon, Jun 13, 2011 at 6:39 PM, Masood Mortazavi < > [email protected]> 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 10.8.1.2 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 > OK. 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! Masood
