1

I need to query a table in the database on a column which is a VARCHAR2. I need to retrieve the records in chunks, not all at one go. ROWNUM is used for this purpose.

The query is like this:

select * from SOMETABLE
where SOMECOLUMN > ?
and rownum <= 100
order by SOMECOLUMN

This query is run repeatedly by changing the SOMECOLUMN value. To start with, this query should be supplied with the least VARCAHAR2 value so that I get the first 100 records (not in any specific order though). Next time, the SOMECOLUMN value in the 100th record is used (order by is added to the query for this purpose) , so that it gets the next 100 records and so on.

(Assumption: SOMECOLUMN values are unique).

What can be the initial least value supplied to this query?

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Manikandan Kannan
  • 8,684
  • 15
  • 44
  • 65
  • 1
    You need to retrieve them in chunks, or process them in chunks - are you just paging for display? What mechanism are you using to retrieve them? I'm wondering if you can query all records but just fetch them in batches, which would mean Oracle takes care of this stuff for you, effectively. What happens if the data changes between your queries, and is your processing changing data in ths table (particularly the `somecolumn` value), and do you commit those changes between queries? – Alex Poole Dec 26 '13 at 18:05
  • 1
    But back to your question... your `NLS` settings will affect sort order and therefore the 'lowest' value. Is `somecolumn` indexed? You could select the `min()` value and compare with that? – Alex Poole Dec 26 '13 at 18:10
  • My processing does not change the data; It only reads; However, the data can gets updated by other process. The SOMECOLUMN is always in the increasing order. This is a batch process which reads the data and generates a text file. So even if some data is missed this time, it will pick the newly added data next time. How does Oracle handle this reading in chunks? How does NLS affect the lowest value? – Manikandan Kannan Dec 27 '13 at 00:27
  • In general Oracle does not support fetching in chunks! Your local NLS Setting defines how text is sorted, e.g. handling of lower-case and upper-case text. – Wernfried Domscheit Dec 27 '13 at 07:49
  • `SOMECOLUMN` is it some kind of timestamp or what? Does it ever change for particular record? Do you have any flag or other method to realize that some record was updated since last batch run? – Yaroslav Shabalin Dec 29 '13 at 17:15
  • SOMECOLUMN is either a timestamp or a number column which is in the increasing order. – Manikandan Kannan Jan 02 '14 at 19:37

1 Answers1

1

You can use MIN() or MAX() also for VARCHAR2 data, not only for numbers. You can use this one:

with t as
   (select SOMETABLE.*, rownum as THE_ROW from SOMETABLE order by SOMECOLUMN)
select *
from t
where THE_ROW between 1 and 100 -- (-> between 101 and 200, between 201 and 300 ...)
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110