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: Why forwarding pointers?

Re: Why forwarding pointers?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Mon, 7 Oct 2002 17:50:08 +1000
Message-ID: <3Vao9.47796$g9.137560@newsfeeds.bigpond.com>


Hey: I didn't say this wasn't rocket science (and please note the double negative!!)!!!!

The fact is, yes -I doubt you'd ever try and fix up row migration without dealing with the PCTFREE problem that caused it. And the other fact is, as Jonathan points out, fixing up PCTFREE is an art form in itself.

Cheers,
HJR "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:anrdk7$6qf$1$8302bc10_at_news.demon.co.uk...
>
> 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.
> >
>
>
>
>
>
Received on Mon Oct 07 2002 - 02:50:08 CDT

Original text of this message

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