Re: returning rows between two positions in a large query

hi,

try this solution

1. create an index on the columns used for ordering your records : create index the_index on the_table (<columns>);
2. select <all the columns of the_table> from select (<all the columns of the_table>,rowum rn from the_table where
   <condition>)
   where rn between from_index and to_index;

<condition> is a dummy condition that would do so that oracle would use the
index for resolving the embedded query

for example :

create table the_table (c_1 number, c_2 number, c_other varchar2(30));

create index the_index on the_table (c1,c2);

insert into the_table values (10,5,'a');
insert into the_table values (5,10,'a');
insert into the_table values (5,5,'a');
insert into the_table values (10,10,'a');
insert into the_table values (15,5,'a');
insert into the_table values (26,1,'a');
insert into the_table values (26,2,'a');

select
 c_1,c_2,c_other
from
 (select rownum rn,c_1,c_2,c_other from the_table where (c_1>0 and c_2>0)) where
 rn between 3 and 5;

C_1 C_2 C_OTHER

---------- ---------- ------------------------------
        10          5  a
        10         10 a
        15          5  a

i think there is a way to force oracle for using a specific index, without the 'dummy condition', does anybody knows ? Received on Wed Jun 23 1999 - 05:02:00 CDT