Re: Row ID

On somewhat of the same note. Is it possible to split up a query by ROWID. I know that parallel query does something along these lines. For example:

select * from mytab where rowid between min(ROWID) and max(ROWID)/4;
select * from mytab where rowid between min(ROWID)/4 and max(ROWID)/2;
select * from mytab where rowid between min(ROWID)/2 and max(ROWID)*3/4;
select * from mytab where rowid between min(ROWID)*3/4 and max(ROWID);

I know there is a problem with not taking into consideration that max(rowid)/4 could be less than the min(rowid), I was just trying to make my point.

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:j1bRN4HaixU2jT6pYC0soaK3FxeW_at_4ax.com...
> On Sat, 4 Sep 1999 12:41:26 -0400, you wrote:
>
> >ROWID identifies the place where Oracle has stored the row. Anything
that
> >causes rows
> >to be relocated makes an old ROWID Obsolete.
> >
>
> No, there is only 1 case where a relocated row will change a rowid. It is
only
> in Oracle8i release 8.1. It is when you update a partition key and have
> explicitly enabled row movement. When you enable row movement in a
partitioned
> table, an update to the partition key is processed much like a
DELETE/INSERT
> rather then an update.
>
> If a row migrates (eg: it was too big to fit in the block it was on after
an
> update) -- the rowid does not change (consider the havoc this would play
with
> indexes which rely on the rowid not changing for the life of a row).
>
> Unless you enable row movement AND have Oracle8i AND update the partition
key --
> a rowid will never change for the life of a row. You have to delete the
row and
> insert it again to get a new rowid assigned to it (but its not the same
row
> anymore -- its a new row)
>
> That aside -- using a rowid for an audit table would be a very bad idea as
a
> unload/reload of the data would lose the connection between the data and
the
> audit trail (i didn't suggest they use a rowid btw -- they should not in
this
> case). I just want to point out that the rowid is a good thing to use in
many
> cases. Forms for example puts it to excellent use. Forms will silently
select
> out the rowid for every row it can (views and such might not permit a
rowid).
> It will use this rowid to help perform row locking as fast as possible.
For
> example, when you build a block on the EMP table using just the ENAME and
JOB
> columns, forms "selects rowid, ename, job from emp". When you attempt to
update
> a row in that block, forms issues:
>
> select * from emp where rowid = :block.rowid
> and ename = :block.ename and job = :block.job
> FOR UPDATE NOWAIT;
>
> If that returns exactly 1 row (and the access path is by rowid -- very
fast)
> then you have locked your row and the data hasn't changed.
>
> if that returns zero rows -- someone else has changed the data -- you
cannot
> update it.
>
> if that returns an error -- someone else has that row locked, you'll have
to
> wait.
>
> That shows that using a rowid in a session works very well. Its a good,
small,
> surrogate primary key that is very safe to use in a smallish time window.
Don't
> save it in a table but by all means use it in your apps. Web based forms
can
> especially make use of the rowid from screen to screen to remember what
row(s)
> they were working on.
>
> >If you have the "Oracle Concepts" manual (two volumes in the doc set),
> >look up
> >ROWID in there. You'll see a good treatment of when to avoid using
ROWIDs,
> >which is basically almost all the time.
> >
> >If you want to pin your records, go back to IMS or IDMS. Relational
isn't
> >for you.
> >
> >Fraser Boswell wrote in message <37CCF3C7.2945103D_at_spamme.ed.ac.uk>...
> >>I'm recording the RowID of records in an Audit table (thanks Thomas Kyte
> >>for the advice), but was wondering what would destroy the RowID making
> >>it invalid. If all records in the table are copied to a new table, old
> >>table dropped and new table renamed back to the original, would the
> >>RowID be invalid for the new table?
> >>What else would cause the RowID to become out of step with the table?
> >>
> >>Thanks in advance
> >>
> >>Fraser
> >>
> >
> >
> >
>
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation Received on Tue Sep 07 1999 - 05:54:32 CDT