a*******t
发帖数: 891 | 1 According to BOL, TechNet etc,
Alter Index Rebuild = Drop index and Create index
But after I drop an index, it takes 2 hours to create index
whereas Alter Index Rebuild take 15 mins.
Why??? | j*******7
发帖数: 6300 | 2 Did you create the clustered index first? | a*******t
发帖数: 891 | 3 There are no clustered index on the tabled I tried.
I tested on 1 of the 20 or so non-clustered indexes.
【在 j*******7 的大作中提到】
: Did you create the clustered index first?
| gy
发帖数: 620 | 4 你的次序是: 先DROP + create, 然后再 Alter??
【在 a*******t 的大作中提到】
: According to BOL, TechNet etc,
: Alter Index Rebuild = Drop index and Create index
: But after I drop an index, it takes 2 hours to create index
: whereas Alter Index Rebuild take 15 mins.
: Why???
| a*******t
发帖数: 891 | 5 yes.
I am thinking the alter index rebuild doesn't actually drop the index, it
scans and don't touch the pages if it's still good?
【在 gy 的大作中提到】
: 你的次序是: 先DROP + create, 然后再 Alter??
| w*******e
发帖数: 1622 | 6 呵呵, 你难道没看出gy问你次序的原因吗??
【在 a*******t 的大作中提到】
: yes.
: I am thinking the alter index rebuild doesn't actually drop the index, it
: scans and don't touch the pages if it's still good?
| a*******t
发帖数: 891 | 7 day 1, drop, create, 2 hr
day 2, alter index rebuilt, 15 min
day 3, drop, create, 2 hr
no data change in between
and keywords in my original post: according to BOL and TechNet, alter index
rebuild = drop + create
【在 w*******e 的大作中提到】
: 呵呵, 你难道没看出gy问你次序的原因吗??
| j*******7
发帖数: 6300 | 8 也许逻辑上等价，但rebuild实际上并行建立那些index,而drop+create是串行执行？值
得研究。
Also I find the BOL says there are log differences per recovery models and per different index operations. | gy
发帖数: 620 | 9 这个,,,,看来还有其他什么解释吧?
zenny??
index
【在 a*******t 的大作中提到】
: day 1, drop, create, 2 hr
: day 2, alter index rebuilt, 15 min
: day 3, drop, create, 2 hr
: no data change in between
: and keywords in my original post: according to BOL and TechNet, alter index
: rebuild = drop + create
| z***y
发帖数: 7151 | 10 When you alter clustered index, all non-clustered indexes will repointed to
new clustered index. Also since we only alter clustered index database
engine will not spend time inventory the existing constraints--they will be
left untouched.
However, when you drop the clustered index, all indexes(clustered and non
clustered) are gone. All constraints will be modified/removed.
Actually my first statement is not 100% correct I just realized... it is
true that non clustered indexes will not be dropped |
|
