Re: Why forwarding pointers?

<ctcgag_at_hotmail.com> wrote in message
news:20021006184726.379$5X_at_newsreader.com...
> "Richard Foote" <richard.foote_at_bigpond.com> wrote:
> > Hi Xho,
> >
> > This is a no win situation and Oracle has chosen the path of low
> > performance hit now as it might not be an issue in the future or it
could
> > be fixed at a more appropriate time.
> >
> > When the update transaction causes a row to migrate there is already a
> > performance hit as Oracle needs to find another block to house the row
> > and apply all the necessary changes. To add all the necessary index
> > maintenance tasks as well could potentially impact such transactions to
> > an unacceptable level.
>
> Thanks, Richard. So it's mostly just the straightforward trade-off, not
> some subtler issues that I was overlooking.
>
>
> > However to say we have this "redirection hit forever" is not correct.
If
> > we detect this is an issue, we can rectify the problem at an appropriate
> > time when normal processing is not impacted.
>
> Well, you can rectify the problem. I'm not quite so talented yet. :)
>

Course you can! Alter table blah move; will fix it all up in one easy (but relatively expensive) command. It's updates that cause row migration; arranging for re-insertion of the data (which is what 'move' does) fixes it all up.

With a big table, 'move' causes a hideous full table scan, so the more elaborate mechanism is:

@$ORACLE_HOME/rdbms/admin/utlchain.sql (to create a chained rows table) analyze table blah list chained rows
create table blahhold as select * from blah where 1=2; insert into blahhold select * from blah where rowid in (select head_rowid from chained_rows);
delete from blah where rowid in (select head_rowid from chained_rows); insert into blah select * from blahhold;

...which has the advantage of deleting only the migrated rows, and re-inserting them. Move effectively does the same thing for every row in the table.

Alternatively: export table blah, truncate table blah, import table blah ign ore=y. Same deal: deleting all rows and re-inserting them fixes up migration issues.

Regards
HJR
> Xho
>
> --
> -------------------- http://NewsReader.Com/ --------------------
> Usenet Newsgroup Service
Received on Sun Oct 06 2002 - 18:30:01 CDT