?
Solved

Query based on rowid in Oracle

Posted on 2003-03-06
13
Medium Priority
?
17,802 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
[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
  • 3
  • 2
  • 2
  • +4
13 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

800 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