Solved

fetching records from Oracle

Posted on 2002-06-14
5
611 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
countHi2 challenge 7 44
strCount chalenge 3 50
Unable to start eclipse ? 17 83
Problem to start Neon 20 52
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…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now