Can anyone comment on this behavior? Namely, can I get feedback as
to whether my interpretation of what makes a "bestPlan"--meaning the
best plan in "this round"--is correct? And if so, is this a bug in
the current Derby optimizer? And if so, any ideas as to how this
could be causing the two new failures in view.sql and refActions1.sql?
When optimizing a table subquery (a subquery in the FROM clause), we
must re-optimize the subquery every time it is put into a new
position in the outer query's join order. Once this is done, there
will (probably) be a best plan for the subquery in that context. The
optimizer will have to remember this best plan even when it considers
a different join order for the outer query, because it's possible
that the subquery's best plan for that optimization will be the best
one overall. This means that there should be two different best plans
for the subquery: the best plan found for the current optimization of
the subquery, and the best plan for all optimizations of the subquery.
I think you've found a bug, but the solution you're trying may not be
correct. I believe the optimizer currently keeps track of only two
"best plans" - the best access path for each table in a query (or
subquery) as it's currently being considered in a join order, and the
best overall join order and path for each table for the best plan
it's found so far. For subqueries it may need to keep track of
another "best plan" - the plan to use with the best plan found for
the outer query.
I'll have to think about whether it gets more complicated than this
if subqueries are nested. Would the optimizer have to keep track of
even more levels of "best plan" in cases like this (I hope not).
BTW, this problem doesn't happen with regular (non-table) subqueries,
because Derby defers their optimization until the optimization of the
outer query is finished.
As for the problems you're seeing in view.sql and refActions1.sql, I
suspect it's due to the fact that in some queries there are certain
join orders that won't work (e.g. if you pass a column from one table
to the constructor for a VTI, the VTI must come after the other table
in the join order). Your attempted fix is probably clobbering the
original best plan for the subquery in a case where no subsequent
best plan is possible. It would help if you posted the queries that
got these errors.
- Jeff Lichtman
[EMAIL PROTECTED]
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
