Re: Reorganize a Table with a Long Field

Chrysalis wrote:
>
> Stéphane TALBOT wrote:
> >
> > Hello !
> >
> > I have a Table containing a LONG field. I have 13 expends, and I would like
> > to reorganize it. I do not have PL-SQL installed.

Chrysalis <cellis_at_iol.ie wrote:
> Firstly, why do you need to re-organize?
> Contrary to popular opinion, multiple extents are *not* a performance
> handicap until you start getting > 100 where the space required to keep
> track of chains get noticeable.

This is pretty much true except I would put it at a more variable size because if your row size is 2048 bytes and your extent size is 40 K then you've got a real problem trying to get the rows out of a table with 100 extents of 40 K (e.g 2000 rows). I wouldn't want to bet my ass on this one. I try for less than 20 extents and usually like to have each extent rather large and not sparsely spaced. You can do a map of the tablespace to see where the extents are located. Sparcely spaced extents add to disk scans/seeks and read times for the head to move to the new spot with the extent.

>
> If you *must* re-organize and you have the space, the simplest way is to
> create new_table as select * from old_table storage(...);
> drop old_table; -- after backup!
> rename new_table to old_table;
>
> Alternatively, you could EXPort your table, drop it (after backup),
> redefine it (don't let IMP do this) and then IMPort it.

And, don't forget the SQL*Plust 'copy' command. The beauty of this command is it will copy long columns and has variable commit array lengths. The documentation is in the SQL*Plus manual not the SQL manual. Remember that the copy command is like the ttitle command and spans only one line so you must use '-' or the continuation character to format it nicely like a typical select.

Hope this helps,
Marc

-- 
||----------------------------------------------------------------------||
|| Marc Marchioli
|| The DataBase Group, Inc.                       voice:  214-528-9459
|| 4011 Travis St.                                fax:    214-528-9459
|| Dallas, TX 75204-7512
|| marcm_at_computek.net                         @
|| (Challenging ORACLE and UNIX every day)    |-}ORACLE,UNIX
||                                            /\-------------->