?
Solved

Query based on rowid in Oracle

Posted on 2003-03-06
13
Medium Priority
?
17,943 Views
Last Modified: 2008-02-29
Hi,
I have table with 3 million records. I am using ado with vb to get the data from the oracle database. I wish to limit the records returned by the query to 200 so that it takes less time for the initial query. When the user scolls down the I will get the other records. Can I base my query on the rowid?. Like

select table.*,rowid from test where rownum <201 will be my initial query to get the 200 records

and sbsequently

select table.*,rowid from test where rownum <201 and rowid >prevrowid


will this be ok? or is there any other way I can do it.

Regards,
Prasad.
0
Comment
Question by:psdasari
  • 3
  • 2
  • 2
  • +4
12 Comments
 
LVL 1

Expert Comment

by:Ntareen
ID: 8080757
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

0
 
LVL 3

Expert Comment

by:rramineni
ID: 8080807
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  
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 8085955
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Expert Comment

by:BjornE
ID: 8086911
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)


0
 

Expert Comment

by:BjornE
ID: 8086919
Disregard that AND ROWID = NVL... line in the first query. :-)
0
 
LVL 3

Expert Comment

by:rramineni
ID: 8088528
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
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 8099737
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
0
 

Author Comment

by:psdasari
ID: 8120565
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?
0
 

Author Comment

by:psdasari
ID: 8122092
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.
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 8124083
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
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9940213
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:
http://www.experts-exchange.com/help.jsp#hs5

patelgokul
EE Cleanup Volunteer
0
 

Accepted Solution

by:
amp072397 earned 0 total points
ID: 10636818
PAQed, with points refunded (50)

Thanks very much!
amp, ee admin, amp*at*experts-exchange.com
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

579 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