Solved

fetching records from Oracle

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How  can  i  resolve  HTTP Status 404 -? 8 76
ejb entity bean example issue 2 40
Print Rhino Java Array in Javascript 1 54
Java pass by reference 3 69
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
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 tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

738 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