Home » SQL & PL/SQL » SQL & PL/SQL » Select records within a range from a BIG table
Select records within a range from a BIG table [message #18933] Thu, 21 February 2002 15:09 Go to next message
Messages: 2
Registered: February 2002
Junior Member
we have some very big tables, ( usually more than 300,000 reocrds, some even reach 9,000,000 records.), we need to select records within a range, for example, select * from TABLE where row>100 and row<200 .
I know we can use sub-selection to do this, for example, a solution is
select * from (select rownum rn, col1,col2... from TABLE) where rn>100 and rn<200
but we think that it is not efficient to retrieve data from out such big table, just consider everytime the sub-select select more than 1 million rows first...

can anyone has a better idea to improve the performance?
Re: Select records within a range from a BIG table [message #18935 is a reply to message #18933] Thu, 21 February 2002 15:33 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
there is no other way except the way u mentioned.
you can always restrict rows returned by inline view by specifying where condition.

select * from (select rownum rn, col1,col2... from TABLE where rownum<200) where rn>100 and rn<200
Re: Select records within a range from a BIG table [message #18946 is a reply to message #18933] Fri, 22 February 2002 03:42 Go to previous message
Christopher Beattie
Messages: 5
Registered: January 2001
Junior Member
I don't think there is a way to optimize this fully, but you can at least remove the burden of the trailing end of the query by doing that criteria first within the subquery. In other words.

select * from (select rownum rn, col1,col2... from TABLE where rownum < 200) where rn>100

rownum is a mechanism that works for simple things, but there are inherant difficulties when applied to large tables. Inserting and deleting rows can change the row numbers. If the table is large enough (which appears to be the case) and is fairly stable it might be logical to add a sequence field to the table. That way you can use the sequence number as the "start" of the query, and the rownum as the end of the query.

Select col1,col2... from TABLE where seqnum > 100 and rownum < 100

Note in this query the rownum is a count of the records after the sequence point, and not the absolute record number. You need to get the last sequence number in the query if you want to have them sequentially displayed. And since the sequence number is a part of the table, inserts and deletes that take place during the time the several queries are performed will not be a problem, since new records are added at the end of the sequence, and deletes will not disturb the sequences of the other records as can happen with recno.
Previous Topic: Quick way to change schema owner in PL/SQL code
Next Topic: ORA-01843: not a valid month
Goto Forum:

Current Time: Sun Sep 19 12:48:23 CDT 2021