Re: Help: Re-Oraganizing a table

J.P. wrote:
>
> Personally, I don't understand what you are trying to achieve.
>
> If you are trying to re-insert all the records in a particular
> PHYSICAL order, this is gonn'a backfire on you, as the data can be
> spread all across the extent(s) and not necessarily be returned in
> that physical order.
>
> Perhaps telling more specifically what you are trying to achieve would
> help.
>
> JP
>
> u518615722_at_spawnkill.ip-mobilphone.net (Mike F) wrote in message news:<l.1017780267.1782043457@[64.94.198.252]>...
> > Hi, Daniel Morgan
> >
> > Here is my result
> >
> > SQL> truncate table direction;
> > truncate table direction
> > *
> > ERROR at line 1:
> > ORA-02266: unique/primary keys in table referenced by enabled foreign
> > keys
> >
> > That is why I have to disable those child table foreign keys first.
> >
> > And I need to drop and re-create some indexes because for some table,
> >
> > INSERT INTO mytable
> > SELECT *
> > FROM temp
> > ORDER BY primary_key_fields_or_whatever;
> >
> > will be extremely slow, so slow that you can not bear it.
> >
> > Any better idea?
> >
> > Thanks

There can be significant benefits in ordering data within a table - particularly if the table is predominantly readonly. Done well, you improve the clustering factor for (at least one) index and thus improve their "attractiveness" to the optimizer and use more efficient use of memory.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."