Solved

fetching records from Oracle

Posted on 2002-06-14
5
613 Views
Last Modified: 2012-06-27
Hi,

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.
0
Comment
Question by:joesat
5 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 7078454
have you tried rs.setFetchSize(20)
0
 

Accepted Solution

by:
CShantanu earned 150 total points
ID: 7078469
Hi joesat,
   I had already submited your answer.But never mind here it is again.

   As per my understanding you are facing prob while fetching data from OracleDB as well as you want that data to be of specific volume,  say first 20 recs. then next 20 recs. If it is the case please try out this.

  1) You need to optimized ur query by creating index on the column you are searching for.
     create index on x_id with some name say inx_x_id;
  2) Build your query with query optimizer by adding following line after select. as follows

String query =    select /* + INDEX (table_name,inx_x_id) */ x,y,z from table_name where x_id ='123'

  3) that's it now your can create Statement Object.
     stmt   = connDB.createStatement();
     stmt.setMaxRows(valCnt);
     rs = stmt.executeQuery(query);
   
   Make sure for each hit of next button increase valCnt by some amount.

That's it.
All the best



 
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 7078480
CShantanu:

welcome to ee.  Please read the tips on comments and answers at the bottom of the page.  Usually, experts only post comments b/c when you propose an answer it locks the question from the view of the other experts.  
0
 
LVL 92

Expert Comment

by:objects
ID: 7079835
And it appears your 'proposed' solution is exactly what joesat is already doing.
0
 

Author Comment

by:joesat
ID: 7080792
thanks guys
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
fibonacci ten numbers 4 40
Way to decrease size of apk file 9 71
JavaFX TableView not displaying correctly 3 20
Java: anonymous class 4 23
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question