?
Solved

fetching records from Oracle

Posted on 2002-06-14
5
Medium Priority
?
621 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 450 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses
Course of the Month9 days, 21 hours left to enroll

762 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