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
更多推荐
在大表上创建INDEX
发布评论