db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: [jira] Commented: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions
Date Fri, 19 Nov 2010 15:33:35 GMT
> 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

Mime
View raw message