Re: "Defrag" a table

If you are saying

> (I will not truncate the table)

because you don't want to lose the data in the table you can export (exp) it, deal with the table and then import (imp) the data when ready. No data loss.

Alternatively you can take Jonathan's advice with a holding table idea which produces a similar effect. Normally more useful if you want to retain data selectively. or if small amounts of data are to be kept.

Once you know how you plan to retain the data to be reloaded, then you need to decide between the two choices regarding the table itself. You can:-

Drop the table - then recreate it. Don't forget to recreate any indexes and constraints.

or

Truncate the table

Both reset the HWM and free storage. Truncate is quicker. These are the only actions that reset HWM that I know of.

Also referencial constraints that refer to this table will need to be considered. But by the sound of the nature of the table, I would guess it wouldn't have any. Always worth asking though.

Regards
--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)

                        <KevinALewis_at_HotMail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. Frederik Hansen <roirex_at_post1.tele.dk> wrote in message news:37EB3BF3.D3D50003_at_post1.tele.dk...
> Hi Kevin
>
> > again when deleted. Secondly a 'whole pile' of rows allocated towards
the
> > end of the table and then freed up again when deleted. Thirdly as a
result
>
> I have done it this way..
>
> > My understanding of how indexes building work could mean that there are
>
> It a very little table, so no other than an index due to the primary key
> is used.
>
> > Another factor might be when during this process you analyzed statistics
for
>
> Nope, I haven't done any analyzed statistics.
>
> > or Truncate it (Truncate removes ALL rows, so beware). Either will reset
the
> > HWM.
>
> How do I reset the HWM? (I will not truncate the table)
>
>
> Frederik Hansen
Received on Fri Sep 24 1999 - 09:05:29 CDT