Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of bpendleton.derby@gmail.com
 designates 209.85.210.44 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=CVdfqsgm1utrXuyB7ymkm9SLOG14GjwPhe61r+hn7Cb2O1Q38ohoxhzEW/1o56Bath
         M0HJFMvVM7Ic23mr5FBGi2N0RyxGHUJG+upTfSRdyIQNx9PepYCCm8aYcAOm+IYmNDGo
         7M0OGgzQ6z/HZvmsl4spfIBZppRmw/snTMRh0=
Message-ID: <4CE698CF.6010202@gmail.com>
Date: Fri, 19 Nov 2010 07:33:35 -0800
From: Bryan Pendleton <bpendleton.derby@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
 rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: [jira] Commented: (DERBY-4908) Instability in
 CheckConstraintTest.testBuiltInFunctions
References: <653749.192911290157274944.JavaMail.jira@thor>
 <998961.55761.qm@web65706.mail.ac4.yahoo.com>
In-Reply-To: <998961.55761.qm@web65706.mail.ac4.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

> I always puzzle about this kind of change. Does this behavior mean the optimizer somehow change so it choose different path?
> Hence, the different order result set. Or, optimizer can decide to choose different path in general?

Hi Lily,

It's a great question, and I think it would be a great topic for the wiki.

The optimizer didn't change, but it definitely can decide to choose
different paths in general.

The optimizer considers a (large) variety of possible query execution
plans, and uses an estimation function to compute an estimate of the
cost of executing each plan. The optimizer attempts to choose the plan
with the lowest cost, by choosing the plan with the lowest estimated cost.
There are some additional wrinkles, but that's the high-level view.

In terms of ordering, the most common reason for an ordering difference
is the difference in behavior between a nested-loops join driven by an
index scan, versus a hash join.

Plans which scan indexes return the rows in key order, and so the rows
tend to be emitted in key order.

Plans which perform hash joins can jumble the rows up in an apparently-random
order, determined by the hash algorithm.

thanks,

bryan