在大表上创建INDEX [英] CREATE INDEX on large table
问题描述
SQL Server 7/2000:我们有相当大的表(3,000,000行)
,我们需要添加一些索引。在测试中,花了12个多小时
来创建一个针对该表的新INDEX。我们其中一个人建议我们使用新索引创建一个临时表,并将旧的
表中的数据复制到新表中,然后重命名。我明白这花了15分钟b
分钟。为什么移动数据和增加多个索引以增加索引的速度更快?
SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
that we need to add some indexes for. In a test, it took over 12 hours
to CREATE a new INDEX against this table. One of us suggested that we
create a temp table with the new index and copy the data from the old
table into the new one, then rename it. I understand this took 15
minutes. Why the heck would it be faster to move the data and build
multiple indexes incrementally vs adding an index??
推荐答案
排序表上的索引更快,因为索引过程不需要
来重新组织它作为创建索引。
" ; dfurtney" < DF ****** @ hotmail.com>在消息中写道
news:11 ********************** @ z14g2000cwz.googlegr oups.com ...
An index on a sorted table is quicker as the indexing process does not need
to reorganized it as its creating the index.
"dfurtney" <df******@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
SQL Server 7/2000:我们有相当大的表(3,000,000行)
我们需要添加一些索引。在测试中,花了12个多小时来创建一个针对该表的新INDEX。我们其中一个人建议我们使用新索引创建一个临时表,并将旧表中的数据复制到新表中,然后重命名。我明白这花了15分钟。为什么移动数据并逐步构建多个索引与添加索引相比更快?
SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
that we need to add some indexes for. In a test, it took over 12 hours
to CREATE a new INDEX against this table. One of us suggested that we
create a temp table with the new index and copy the data from the old
table into the new one, then rename it. I understand this took 15
minutes. Why the heck would it be faster to move the data and build
multiple indexes incrementally vs adding an index??
嗨
这似乎不太可能。
你没有提到表是否是堆(即没有聚集的
索引)。
您可能遇到过这样一种情况:旧表中的数据已经过时
命令构建附加索引是不断分裂
页。
Hi
This seems unlikely.
You did not mention if the table is a heap (i.e. there is no clustered
index).
You probably ran into a case where the data in the old table was so out
of order that building the additional index was constatnly splitting
pages.
dfurtney(df******@hotmail.com)写道:
dfurtney (df******@hotmail.com) writes:
SQL Server 7/2000:我们有合理的大表(3,000,000行)
我们需要添加一些索引。在测试中,花了12个多小时来创建一个针对该表的新INDEX。我们其中一个人建议我们使用新索引创建一个临时表,并将旧表中的数据复制到新表中,然后重命名。我明白这花了15分钟。为什么移动数据并逐步构建多个索引与增加索引相比更快?
SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
that we need to add some indexes for. In a test, it took over 12 hours
to CREATE a new INDEX against this table. One of us suggested that we
create a temp table with the new index and copy the data from the old
table into the new one, then rename it. I understand this took 15
minutes. Why the heck would it be faster to move the data and build
multiple indexes incrementally vs adding an index??
12小时创建三个索引百万行声音异常。
当然,如果表没有聚簇索引,但已经有几个非聚集索引的b $ b,你添加了一个聚集索引,然后它将需要一些时间,但仍然不是12小时。
一个可能的原因是,CREATE INDEX进程被阻止了
大部分时间是另一个过程。
-
Erland Sommarskog,SQL Server MVP, es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf。 ..2000 / books.asp
这篇关于在大表上创建INDEX的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!