Re: Why forwarding pointers?

There are side-effects to consider, though, which makes the option for using the chained row table a better bet. (Especially if you have allowed for deferrable constraints).

If you have migrated rows (i.e. pctfree and growth problems) then it is quite possible that your data has a pattern of:

    insert short rows
    increase length for rows
So when you decide the migrated rows are a problem, you may have say) 90% of the data at full size, and 10% at 'new' short size.

If you move the table, you end up with lots of full size rows which are never going to grow again wasting PCTFREE in a lot of blocks. Alternatively, you modify PCTFREE to make sure that this data is packed - in which case you have 10% of your data packed too tightly, just waiting to grow and cause even worse migration problems.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html




Howard J. Rogers wrote in message ...


>

>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.

>