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;
The lighter weight our temporary tables are, the better these kinds of
solutions will perform. Making temporary tables lighter weight could
boost the performance of large families of problem queries.
