Solved

fetching records from Oracle

Posted on 2002-06-14
5
617 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

691 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