fetching records from Oracle
Posted on 2002-06-14
I'm facing problem while fetching selected number of records from Oracle 8i.
e.g., We have huge number of records, say around 200,000 in oracle 8i. I had to fetch 20 records of the 200 records I need. These records are scattered through out the database.
When I write a query, say (select x,y,z from table_name where x_id="123" and rownum<=20), it is searching only the first 20 of the databse records and returning 2 records which matches with the above condition. whereas, I'm expecting first 20 of the 200 records initially.
I have tried some of the jdbc API's methods like one in Statement. Statement.setMaxRows(int varCnt); Initial count in varCnt is 20 and I am increasing varCnt by 20 each time user hits next button.
This will work but it takes hell lot of time. As it might be searching for 200,000 records first & then return only first 20 out of it which matches with above condition.
Please let me know if some one face similar prob. and achive any success in geting next - prev functionality through jdbc.