| Select records within a range from a BIG table [message #18933] |
Thu, 21 February 2002 15:09  |
Kampo
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 #18946 is a reply to message #18933] |
Fri, 22 February 2002 03:42  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
