db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: derby performance and 'order by'
Date Wed, 21 Sep 2005 16:34:07 GMT
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.


Oyvind.Bakksjo@Sun.COM 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.?

View raw message