Query based on rowid in Oracle

psdasari,

No you can not check  for range on  rownum,  and the second  will give you no records.


If you are using the  VB U can have a counter in your program and then the  reject the rows afer  200 and go to exit procedure, than on second run start from the previous id and then after reading 200 go to exit.

Good Luck.

NT

You could use inline view to do the same.

select a.*
  from ( select *, rownum from test ) a
 where a.rownum <201;

..


Select a.*
  from ( select *, rownum from test ) a
 where a.rownum between 201 and 400;


You cannot use rowid as rowid is internal to oracle.

HTH  

rramineni,

"select...where rownum between 201 and 400" will return zero records because rownum has to start from 1. No row will immediately satisfy the criterion "rownum >= 201" and therefore no rows will be returned.

You can use the rowid; it is a pseudocolumn, well-documented; and it is not "internal" to oracle!

Jeff

Actually, rowid would work very well, given a few considerations/restrictions:

1. If your table has no deletions made to it, then all inserted rows will be there in the order which they were first inserted.

2. Secondly, using ROWNUM <= 200 will yield records in subsequent queries, since this is a way of telling Oracle to stop once it has found 200 rows that match the where-clause criterias.

For instance in a table that is never deleted from.

SELECT A.ROWID AS MY_ROWID
,      A.*
FROM ACCOUNT A
WHERE ROWNUM < 4
AND ROWID = NVL(my_last_rowid,ROWID)

Now, take the MY_ROWID in the last record
and issue the next query:

SELECT A.ROWID AS MY_ROWID
,      A.*
FROM ACCOUNT A
WHERE ROWNUM < 4
AND ROWID > my_last_rowid;

You will see that you get another 3 rows, with a new rowid.

(If your table is indeed deleted from, you will need to order your records by rowid for this to work, but will give overhead on each query, as ordering a 3 million table is rather expensive if done many times. I would suggest you try a different approach if so. Like break it down on primary key elements)

Disregard that AND ROWID = NVL... line in the first query. :-)

Jeff,

I guess you did not get a chance to see my query. I am using the rownum returned from the inline view and not the rownum from the outer query. This definitely work and I use it in most of my queries where "next page" feature is required. I found this one to be very efficient.

As far as the rowid is concerned, I meant using it in third party tools like VB and others. The handling of rowid is different when used with ODBC compared to native sql. Also as BjornE mentioned, any deletions on the table would force you to order the data sets which is additional overhead.

I understand the original requirement is to provide this mechanism for a VB application.

HTH

rramineni,

Ok, agreed. I wasn't sure that rownum could be used as a column name or not since it is a "keyword" in Oracle.

The only problem is, the inner select still has to return all rows before the outer select is run.

Select a.*
 from ( select *, rownum from test ) a
where a.rownum between 201 and 400;

"select *, rownum from test" has to be executed in entirety (i.e. all rows retrieved into memory) before the outer select can choose the rows to return.

Since I've little experience with vb I'll shut up now...

Jeff

Using a counter in VB wont help for making the query fast as the query will take the same time to get the all the records from the database. I am looking for using a where clause in my query.

Using an inline view may be inefficient as the record number increases and the inner query has to get all the records.

I can get the min(rowid) from the table and every time increment the rowid by 200 and use this in my where clause for rowid. Is there a way to get the minrowid, maxrowid of a table from the oracle data dictionary?

Hi,
The rowid in Oracle 9i if of the following format
AAAUArABFAAAAAKAA8. Does the row id differes with different nls_languaage settings?. or the rowid will always be english characters?

Regards,
Prasad.

You can use MIN(rowid) and MAX(rowid) but it was never intended for this purpose and may not be supported or work every time.

In fact, I think there is no guarantee that updates will not cause rowids to change (I may be corrected on this).

Therefore, I'd say try it out maybe but be wary.

Jeff

This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in approximately one week.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
https://www.experts-exchange.com/help.jsp#hs5

patelgokul
EE Cleanup Volunteer