On Tue, Jun 14, 2011 at 6:09 AM, Rick Hi= llegas wrote:
Hi Masood,

On 6/13/11 7:30 PM, Masood Mortazavi wrote:
On Mon, Jun 13, 2011 at 7:18 PM, Masood Mortazavi <masoodmortazavi@gmail.com <= ;mailto:maso= odmortazavi@gmail.com>> wrote:

=A0 =A0On Mon, Jun 13, 2011 at 6:39 PM, Masood Mortazavi
=A0 =A0<= masoodmortazavi@gmail.com <mailto:masoodmortazavi@gmail.com>> wrote:
=A0 =A0 =A0 =A0I have a Derby table (T) with 10^7 records.
=A0 =A0 =A0 =A0This table has 5 columns.
=A0 =A0 =A0 =A0I have defined a Derby view (V) on one of those columns. =A0 =A0 =A0 =A0To produce (V) all of (T) needs to be scanned.
=A0 =A0 =A0 =A0There are about 2000 records that the view (V) contains.
=A0 =A0 =A0 =A0Now, I "cross" (T) with (V), with an IN clause in= volving (V).
=A0 =A0 =A0 =A0I have three questions:
=A0 =A0 =A0 =A0[1] Is the optimizer smart enough to to scan only O( (T)*(V= ) )
=A0 =A0 =A0 =A0times (with the V comparisons involving in-memory
=A0 =A0 =A0 =A0"materialization" of (V)), or will it be O( (T)*(= T) ) or
=A0 =A0 =A0 =A0something else?

I think this depends on the view, the query, and the statistics on the unde= rlying table. I'm sure that Derby's view materialization could be i= mproved.

=A0 =A0 =A0 =A0[2] What is the support that Derby 10.8.1.2 may be offering=
=A0 =A0 =A0 =A0for materialized views?

View materialization wasn't a focus of the 10.8.1.2 release. It is poss= ible that the automatic calculation of statistics may improve the performan= ce of materialized views in some cases.

=A0 =A0 =A0 =A0[3] If the answer to [1] is the larger number, would
=A0 =A0 =A0 =A0materializing (V) change things or improve them drastically= ,
=A0 =A0 =A0 =A0i.e. would Derby save the large (T) sized scan to produce (= V),
=A0 =A0 =A0 =A0or even better, would it do that and also bring all of (V),=
=A0 =A0 =A0 =A0which is rather small, into memory (in large enough chunks = or
=A0 =A0 =A0 =A0completely) for the purposes of the cross involving IN?

=A0 =A0 =A0 =A0With best regards to all good Derby friends,
=A0 =A0 =A0 =A0- m.

=A0 =A0I solve the materialization issue by a simple exporting of the
=A0 =A0view's select query and then importing it.
=A0 =A0It works well.
=A0 =A0I will rerun my IN cross query, against the now materialized table<= br> =A0 =A0to see how much better or worse it does in this particular case. =A0 =A0The general gist of my questions above still remain.

=A0 =A0Regards,
=A0 =A0Masood

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.
You may get better performance by using an INSERT ... SELECT statement rath= er than exporting the view and re-importing it. That will eliminate the red= undant writing of the view to an external file. You may also investigate us= ing a temporary table to store the intermediate result (for details, see th= e Reference Guide section on DECLARE GLOBAL TEMPORARY TABLE).

Hope this helps,
-Rick

Thank y= ou very much.
Yes, INSERT . . . SELECT would have been very helpful and = more concise.
We may have other uses for the extra data file, in a Hadoo= p environment.
Given the statistics of the source data, doing an "IN" against a = materialized view turned out to be a lot faster, as expected.

Thanks= again and with very best regards,
m.

P.S.
By the way, not sup= porting materialized views is not a big deal.
I'm not suggesting it should be supported.

--001636d3478560505604a5af265e--