Using Indexes for data access [message #18715] |
Tue, 12 February 2002 02:47 |
Raghu Raman
Messages: 4 Registered: February 2002
|
Junior Member |
|
|
I need to display 50 records at a time from a table with the excess of 200,000 records. Right now I am doing a "select * from TABLENAME" and accessing the ResultSet to retreive 50 records in my Java code. This is extremely slow since the DB returns the entire ResultSet of 200,000 records.
The rows may not have contiguous IDs since there may be deletion of records.
Now, how do I send SQL queries such that only 50 records are returned?
Can this be done using INDEXES? If yes, How?
|
|
|
|
Re: Using Indexes for data access [message #18741 is a reply to message #18715] |
Tue, 12 February 2002 23:26 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
hi,
if you have row no.s 1, 3,4,5,8,10......... (with row no.s 2, 6,7,9 having been deleted)and you follow the suggestion, you will still get
rows 8 and 10 alone???? because these are the first 5rows. try and see.if u still have problem then we will discuss.
cheers
pratap
|
|
|
Re: Using Indexes for data access [message #18745 is a reply to message #18715] |
Wed, 13 February 2002 02:31 |
Raghu Raman
Messages: 4 Registered: February 2002
|
Junior Member |
|
|
I used the query - select * from (select *, rownum rn from TABLE) where rn >=51 and rn <=100;
This query works. but it is slow since the inner query returns the entire ResultSet and the outer query works on that.
Is there a way to improve performance of this query??? Stored Procedures??? any other options???
|
|
|
|