Home » SQL & PL/SQL » SQL & PL/SQL » Using Indexes for data access
Using Indexes for data access [message #18715] Tue, 12 February 2002 02:47 Go to next message
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 #18719 is a reply to message #18715] Tue, 12 February 2002 04:00 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
hi,

instead of retiving all record try retieving records in chunk. here is an example, which select 6--10 records from emp table

select * from (select e.*,rownum rn from emp e)
where rn>5 and rn<=10

hope it helps

cheers
pratap
Re: Using Indexes for data access [message #18741 is a reply to message #18715] Tue, 12 February 2002 23:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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???
Re: Using Indexes for data access [message #18748 is a reply to message #18715] Wed, 13 February 2002 03:39 Go to previous message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
hi,

try this

select * from (select *, rownum rn from TABLE where rownum<100) where rn >=51;

if u r using oracle8i then there is another way of doing this.if u rusing it , then let me know.

cheers
pratap
Previous Topic: Sequence problem - URGENT!!
Next Topic: Re: PL/SQL script
Goto Forum:
  


Current Time: Thu Mar 28 14:17:26 CDT 2024