Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of masoodmortazavi@gmail.com
 designates 209.85.215.44 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type;
        b=lcN0U4YZdNmUP1DHz+hk2GatsNfkwshSc5D/r/vQ3LoWa9BZoTz+NX8g3aZTsu8ET/
         10GFa+O2c9JHuuMDsxd+o6TeBfE7X2jDcb260Q/gNMPK3yv7/eDuLH875Cd772AMVEsL
         7N0TQ2OYwnDOV9ocX9mRYIVbGXnAx0TYAyFAA=
MIME-Version: 1.0
In-Reply-To: <BANLkTim7E=HttqooaKcNFyAV9HjW_OvjrA@mail.gmail.com>
References: <BANLkTim7E=HttqooaKcNFyAV9HjW_OvjrA@mail.gmail.com>
Date: Mon, 13 Jun 2011 19:18:41 -0700
Message-ID: <BANLkTinHgzXAd4ivmo+HO+sPpS8RWH14ig@mail.gmail.com>
Subject: Re: Derby query optimizations / (materialized) Views and Queries
From: Masood Mortazavi <masoodmortazavi@gmail.com>
To: Derby Discussion <derby-user@db.apache.org>
Content-Type: multipart/alternative; boundary=bcaec52157d193f96904a5a2a709

--bcaec52157d193f96904a5a2a709
Content-Type: text/plain; charset=ISO-8859-1

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 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

--bcaec52157d193f96904a5a2a709
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">On Mon, Jun 13, 2011 at 6:39 PM, Masood =
Mortazavi <span dir=3D"ltr">&lt;<a href=3D"mailto:masoodmortazavi@gmail.com=
">masoodmortazavi@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"g=
mail_quote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">
I have a Derby table (T) with 10^7 records.<br>This table has 5 columns.=A0=
 <br>I have defined a Derby view (V) on one of those columns.<br>To produce=
 (V) all of (T) needs to be scanned. <br>There are about 2000 records that =
the view (V) contains.<br>

<br>Now, I &quot;cross&quot; (T) with (V), with an IN clause involving (V).=
<br>I have three questions:<br>[1] Is the optimizer smart enough to to scan=
 only O( (T)*(V) ) times (with the V comparisons involving in-memory &quot;=
materialization&quot; of (V)), or will it be O( (T)*(T) ) or something else=
?<br>

[2] What is the support that Derby 10.8.1.2 may be offering for materialize=
d views?<br>[3] If the answer to [1] is the larger number, would materializ=
ing (V) change things or improve them drastically, i.e. would Derby save th=
e 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?<br>

<br>With best regards to all good Derby friends,<br><font color=3D"#888888"=
>- m.<br></font></blockquote><div><br>I solve the materialization issue by =
a simple exporting of the view&#39;s select query and then importing it. <b=
r>
It works well.<br>I will rerun my IN cross query, against the now materiali=
zed table to see how much better or worse it does in this particular case. =
<br>The general gist of my questions above still remain.=A0 <br></div></div=
>
<br>Regards,<br>Masood<br><br><br>

--bcaec52157d193f96904a5a2a709--