>
> Ari,
> Hello once again. I am hoping you can help with
> a question I have. Is it possible to do a sort of
> "rowid arithmetic". I will explain what I mean. I want
> to be able to retrieve a record by rowid, then based
> on it's rowid, I would like to give the user "Go to Previous"
> and "Go to Next" buttons, and when the user clicks those
> buttons I would be able to have done some "rowid arithmetic"
> and defined what the rowid should be for the previous and
> next records. I hope this makes sense. I have had no luck
> getting help with this, even for someone to tell me if this
> is even possible.
> Thank you for your time in reading this. I will
> greatly appreciate any assistance you are willing to
> part with.
>
> Thank you,
> John
> --
>
> **********************************************************
> * John Holland (holland@blairlake.com) *
> * Technical Director *
> * BlairLake New Media (www.blairlake.com) *
> * Tel 816.756.2121 Fax 816.756.2992 *
> * 104 West 42nd Street, Kansas City, MO 64111-2301 *
> **********************************************************
>
Hi John,
It seems that this is impossible, due to the way rowid is setup. The rowid for
each record looks something like: 00014CD0.0004.0011
The rowid is seperated in 3 portions. The first is the the block number of the database
file that the record is in, the second is some internal pointer (record in a block), and
the third is the file the record is in.
In a perfect setup (one in which a table is given its own tablespace and the
records are loaded in order and there are no deletions or records chaining
across blocks), then you could decrement the middle portion until it gets to 1,
then decrement the third portion and reset the middle portion to the maximum
number of records in that block.
You can see already that it is getting complicated. Just to find the maximum
number of records in a block is complicated. This is for a "perfect" setup.
Things that could throw off rowid arithmetic:
1) If records are loaded/inserted out of order.
2) If any record is deleted (it will leave gaps in rowids)
3) If more than one object is in the tablespace with the table
4) If records span more than one block
If you want to navigate as you want, you could have a column with a sequence
value (the first record gets a 1, the next a 2, etc.). Since there could be
missing numbers (if records are deleted, for instance), you can decrement the record by going to
"select max(sequenced_column) when sequenced_column <
(whats_in_current_sequenced_column)".
You can also increment the record by going to
"select max(sequenced_column) when sequenced_column >
(whats_in_current_sequenced_column)".
I hope this helps! BTW, I think you had a good question and would like to pos
my response on my web page along with 70+ other tips. Is this ok with you?
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 70+ technical tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page