db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Lichtman <swa...@rcn.com>
Subject Re: [OPTIMIZER] OptimizerImpl "best plans" for subqueries?
Date Fri, 17 Feb 2006 22:28:39 GMT

>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
                                 Check out Swazoo Koolak's Web Jukebox at

View raw message