db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oyvind.Bakk...@Sun.COM
Subject Re: derby performance and 'order by'
Date Wed, 21 Sep 2005 10:27:50 GMT
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.?

Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43419 / +47 73842119, Fax: +47 73842101

View raw message