1

I have a table - 

emp_record

which has 40,000 records And I want to fetch the records from java code and below is the requirement -

  1. At a time only 1000 records should be returned
  2. In the next hit next 1000 records
  3. continue till all the records exhaust
  4. The SQL query should not be nested, like
 select *
 from(
 SELECT a.*,rownum rn 
 FROM distributor  a)
 WHERE rn  >= 3000 and rn < 4000;

Any sort of help is much appreciated.

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
Arvind Kumar
  • 459
  • 5
  • 21

2 Answers2

3

This sounds very artifical as 40.000 records is nothing. I would just read them in one query and perhaps hand them out in batches. In that case you can use statement.setFetchSize(1000) to make the JDBC driver fetch 1000 records at a time and position that as solving the requirement.

Alternatively if you are on Oracle 12 you can use:

select * from distributor
order by something_unique
offset x rows fetch next 1000 rows only

where x is the starting position. It does the same thing as the rownum construction, but with a much nicer syntax.

ewramner
  • 5,810
  • 2
  • 17
  • 33
  • May be it's typo or visibility problem but I have mentioned 40,000 record not 40.000. And the records in the table can exceed much more than this. BTW thanks for answer. – Arvind Kumar May 29 '18 at 07:57
  • I tried to execute but got the error - ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 3 Column: 1 QUERY - "select * from distributor order by DISTRIBUTOR_ID offset 10 rows fetch next 1000 rows only" Running the query on Oracle SQL Developer(4.0.0.13) – Arvind Kumar May 29 '18 at 07:58
  • 1
    Forty thousand records is not much regardless of the thousand separator character used. Are you sure that you are on Oracle 12 (select * from v$version)? This is not supported on 11g or older. – ewramner May 29 '18 at 08:15
  • my bad , missed the oracle 12 pre-requisite to the query. Mine is "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production" I am able to run the query "select * from v$version;" properly. Any other solution please? – Arvind Kumar May 30 '18 at 06:29
  • 1
    There are several ways to do it with inline views (nested SQL), but as you don't want to use that I'm out of ideas. Perhaps you could get all the 40000 ids in one select (argue that the ids are smaller than the full records) and then fetch 1000 at a time with selects that use in (id1, id2, ..., id1000)? – ewramner May 30 '18 at 07:06
0

You could use a query that uses ROWNUM by first ordering the rows by a primary key/unique key. Idea from the query here : Best practice for pagination in Oracle?

SELECT * 
  FROM (SELECT A.*, rownum rn
          FROM (SELECT *
                  FROM emp_record
                 ORDER BY pri_key_col) A
         WHERE rownum <= :n * 1000)
 WHERE rn > (:n - 1) * 1000;
  • pri_key_col : Primary key/ unique key column of your table.
  • n : number indicating the batch. If n = 1, it returns first 1000 records ordered that way, n=2 gives next 1000 and so on.
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • 1
    This answer is valid and runs ok if the number of rows is in your range of 40.000. But please be aware that it won't scale once you reach a million rows or so. Then it hurts that you need an full index scan for each batch. I tested it on a table 2.3 mio rows. The first block taks 0.9 seconds, the 100th 1.3 s, the 1000th already 4.0 s, the last one 7.6 s. – wolφi May 28 '18 at 10:45
  • 1
    Well, it breaks "The SQL query should not be nested" and is very similar to the query in the question. – ewramner May 29 '18 at 08:18