Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Strange ROWID problem

Re: Strange ROWID problem

From: Allan Nelson <nels212_at_austin360.com>
Date: Sun, 02 Aug 1998 13:59:03 -0500
Message-ID: <35C4B6F7.F802C074@austin360.com>


SRINIVAS CHIKKA wrote:

> Hi,
>
> I have a dbout about using rowids to delete duplicate records. Please
> clarify this:
>
> When there is lot of data in the tables, and you use rowids to delete rows,
> what happens if Oracle decides to rearrange the data ?
>
> I mean, suppose you have ten million rows in a table all with certain
> rowids.
> Now you delete a lot of rows from the table using the query suggested.
>
> Then can Oracle rearrange the rows thereby changing the actual rowids of the
> rows ?
>
> If it can, and if it does, then the rowids may not be a good way of deleting
> the correct rows !
>
> If I am wrong in my concepts, please let me know.
>
> Thanks,
> Srinivas.Chikka
>
> William P. Mitchell wrote in message <01bdb650$42f39320$8059a8c0_at_04635>...
> >Perhaps I'm too late, but I think this is the query that you are looking
> >for:
> >
> >delete from mytable t
> > where not t.rowid = (select min(x.rowid) from mytable x
> > where x.column = t.column);
> >bill
> >
> ........

  You are correct, you have a conceptual issue here. Oracle does not move rows except in the case of chained rows. Even with a chained row the old block will have a pointer to the new block. This way of handling rows makes rowid a safe way to do deletions and avoid getting snapshot too old messages when doing deletes on very large tables.

Allan Received on Sun Aug 02 1998 - 13:59:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US