Re: "Defrag" a table

How have you assessed that you have a 'fragmented' table. Given your description, all I would have expected are the following things. First a few holes in the existing row allocation would have been used and then freed up 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 the High Water Mark for the table is raised causing Full Table Scans to take longer for this table.

My understanding of how indexes building work could mean that there are holes in the packing of the index. this would cause the reading of a sectino of index to read more blocks than otherwise.

Another factor might be when during this process you analyzed statistics for the table or its indexes. If these are not current then you might not get the best performance.

To solve a High Water Mark issue you need to reorganize / rebuild the table or Truncate it (Truncate removes ALL rows, so beware). Either will reset the HWM. If it is just the indexes the action will depend on your DB version. On a later version of 7 or certainly by 8 you can ALTER INDEX index_name REBUILD, otherwise you need to drop the indexes and recreate them.

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:37EB1F82.86C2D19C_at_post1.tele.dk...
> Hi
>
> Is there anyway to "defrag" a table? I have a table which normaly have
> tree rows, some how someone (for test perpose) inserted 1023115 rows. I
> now after I have deleted all the uwanted rows it take from 3-7 sec. to
> do a "Select *" on the tree rows...
>
> So how do I make it go fast again??
>
> Frederik Hansen
Received on Fri Sep 24 1999 - 03:28:06 CDT