we**********@ya hoo.com (Jay) wrote in message news:<95******* *************** ****@posting.go ogle.com>...
Hi I have a huge table with over 100million records and on regular
basis ineed to delete nearly a million records and insert a million
records.
Currently I delete indexes before going through the process and
recreate the indexes which takes a very very long time.
IS there a way to disable indexes and re enable them after doing
insert and delete by reindexing or anything of that sort?
OR
Is there an approach to append two tables with similar indexes?
Please air you opinior, will mean a lot to me.
Thnaks
Jay,
You have a few problems to bear in mind when dealing with tables this
large and with such a turnover of data.
Firstly, as you've discovered, index rebuilds can take an age. I'd
consider partitioning this table if possible, either horizontally or
vertically. Books Online has a good discussion on partitioning and
ways to retrieve data from partitioned tables. The major benefit for
you would be that the index rebuilds will each be on smaller tables
(the member tables within the partitioned table), and may not be
required on all of these tables either.
You could also try rebuilding your indexes using the SORT_IN_TEMPDB
option - this can be particularly good if tempdb is on its own
separate device (which it should be, ideally). It allows you to
reduce disk I/O contention on the table's database, and can improve
index rebuild speed significantly.
Secondly, by deleting/re-inserting 1 million rows out of 100 million,
you may be severely skewing your table data statistics that the
optimizer relies on. I say "may be" because I don't know what rules
your deletes and inserts follow; if you're deleting and re-inserting
randomly throughout the table, then maybe it won't be so bad, since
you're only changing 1% of the data. If you're deleting 1 million
rows from one end of the table and re-inserting at the other, however,
this could make a substantial difference to the "spread" of the
statistical information.
By rebuilding your indexes, you will be partially correcting for all
this, since statistics will be updated at the same time (unless you're
using the STATISTICS_NORE COMPUTE option). However, these will only be
sampled statistics with a table of this size (i.e. they won't take all
rows into account, only a certain percentage of them). Furthermore,
the statistics are only computed (and maintained automatically
thereafter) for the leading column in any composite indexes. You can
improve on this by running the UPDATE_STATISTI CS command and
specifying the INDEX option, (or COLUMNS if you want to also include
non-indexed columns), together with the FULL keyword, which will
recreate your statistics based on all the table data, not just a
sample.
For a table this size, you may want to consider disabling autostats
updates, too, since these can kick in at inconvenient moments, and be
quite an overhead on a table of this size. You may find it better to
disable the automated updates by using the STATISTICS_NORE COMPUTE
option when rebuilding the index, and to then schedule manual UPDATE
STATISTICS commands at a predictable time so as to give you more
control. If there is no suitable out-of-hours time to rebuild the
statistics manually, then it would be best to leave the auto update
option ON.
