Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of bpendleton.derby@gmail.com
 designates 209.85.212.172 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:user-agent:mime-version:to:subject:references
         :in-reply-to:content-type:content-transfer-encoding;
        b=oRyGG4snv64LCIuJ5ohmhx4X3CqcAbASkFBnuKX9JmPyJ0BvdakNIEvrVF3QjUIIRC
         eITK3QxpJnNR8uDBlqppLLOL2EoS+9gfdOytwv+mmD2e8UitsSHdTmiLxFkqbUI+pGI4
         QcsIrjiFT5d7usU/wxYQPMYkwcJHuz6lH+6Ns=
Message-ID: <4D14C591.1090301@gmail.com>
Date: Fri, 24 Dec 2010 08:08:49 -0800
From: Bryan Pendleton <bpendleton.derby@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
 rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: A small query related to database concepts.
References: <AANLkTikiRwDr7Z-Fbcr2qr9J4YLHOfgj9fHwyHwr_eso@mail.gmail.com>
In-Reply-To: <AANLkTikiRwDr7Z-Fbcr2qr9J4YLHOfgj9fHwyHwr_eso@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

> Isn't it make sense to  use the cached cursor of first query.

I think it's an interesting idea!

At the point where Derby is getting ready to compile the second
query, how would it know whether it already had a cached plan
for the first query?

It seems like you would need:
1) A loop which looked at all the existing cached query plans
2) A method which could take a cached query plan, and a new
query of interest, and returned "yes" if the cached query plan
satisfies the new query of interest.

I worry that implementing that method (2) would involve doing
much of the work of actually compiling that second query, so
in the end it's not clear that you'd save anything, except
that we'd be able to discard the second query's plan and save
on space in the compiled query cache (which does indeed tend
to fill up if the conditions are right).

Are there examples other than the one you mentioned ("select *"
versus "select A,B,C")? Some other examples would help demonstrate
the generality of your idea.

One situation that I have encountered in my work with Derby
involves whitespace differences in queries. Currently, Derby
treats
    select a from t
and
    select  a  from  t
as two different queries, and compiles both queries. It seems
like your approach would enable the second query to use the
already-cached query plan of the first.

However, I know that some Derby users have used the current
behavior as a feature. That is, sometimes there are cases where
the user knows that the existing cached query plan is sub-optimal
(for example, the tables have changed in size dramatically since
the query was compiled), and as a workaround they run the query
with a trivial whitespace difference in order to ensure they
get a new query plan, not the existing cached one.

I think your idea is interesting, and I think that Derby would
be a good platform for you to experiment with it; you could
implement your proposal and then write some benchmarks to try
to analyze its behavior.

thanks,

bryan