Hi Oyvind,
I agree that this is inelegant. As you note, this approach step by step
forces a plan which the current Derby optimizer is capable of
considering--with or without the covering index. Regardless of whether
we teach the optimizer some better tricks, I think it's worth beefing up
our support for in-memory temporary tables:
o There are always deceptively simple queries which the optimizer
misjudges. It's good to give the customer tools for getting unstuck
while they wait for the bugfix release.
o Often the customer knows facts about the data which the optimizer
can't plausibly learn.
o The current Derby optimizer is capable of considering only a very
limited subset of the useful plans.
Cheers,
-Rick
[EMAIL PROTECTED] wrote:
Rick Hillegas wrote:
It might help to add another column to the index so that it covers
both the restriction and the ordering information. And if we could
add a primary key to a temporary table, then something like the
following might take us in the right direction:
create index time_index on orders( time, orderID );
declare global temporary table session.ztemp
( orderID varchar( 50 ) primary key )
not logged;
-- all the information we need is in the index so there's no need
-- to probe the base table
insert into session.ztemp
select orderID
from orders
where time between '10/01/2002' and '11/30/2002'
;
-- hopefully the temporary table didn't have to spill to disk.
-- no sort should be needed for this query and you can just
-- stop siphoning out rows after you get the first 1000.
select l.*
from orders l, session.ztemp r
where l.orderID = r.orderID
order by orderID;
If I understand this correctly, you're here relying on the fact that
the primary key constraint on the temporary table creates an
underlying index, so the records inserted can be read out in sorted
order.
This is also a form of sorting. Shouldn't the engine be able to use
similar techniques in the execution of the original query, without
relying on the user splitting up the query, creating temporary tables
etc.?
