在大表上创建INDEX

本文介绍了在大表上创建INDEX的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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>在消息中写道 news：11 ********************** @ z14g2000cwz.googlegr oups ... 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> wrote in message news:11**********************@z14g2000cwz.googlegr oups... 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）写道： dfurtney (df******@hotmail) 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联机丛书 www.microsoft/sql/techinf。 ..2000 / books.asp