关于rebuild index online 及drop index后重建问题

wdjk1999

等受教

cc59

craete index ...  Oracle will scan the whole table for the data to build the index.

alter index ... rebuild oracle takes the data already in the index and just reorganizes it.
The orginal table is not scanned again for data guring this rebuild process.

cc59

When creating a new index that is a subset of an existing index or when rebuilding an existing index with new storage characteristics, Oracle might use the existing index instead of the base table to improve the performance of the index build.


from Oracle9i Database Performance Tuning Guide and Reference

SONGHEFEI

最初由 cc59 发布
[B]When creating a new index that is a subset of an existing index or when rebuilding an existing index with new storage characteristics, Oracle might use the existing index instead of the base table to improve the performance of the index build.


from Oracle9i Database Performance Tuning Guide and Reference [/B]

alter index  rebuid online  与alter index ....rebuild 是不同的！

cc59

最初由 SONGHEFEI 发布
[B]

alter index  rebuid online  与alter index ....rebuild 是不同的！ [/B]

我没有说这两一样

rollingpig

至少有几个不同
1。drop & create 的话，当前Table 无法使用该index ,可能会严重影响应用，只能在应用不需用到该 Index时使用 ， 而 alter index .. rebuild online 则没有这个限制，只是会消耗DB资源，不至于严重影响应用

2。Drop & create 只需占用一份index的space , 不像alter index .. rebuild online ,会在创建过程中需要占用新老两个index的space, 在free space不足的环境中，也许只能选用Drop & create

3. 至于 alter index .. rebuild ，或者alter index .. rebuild online 是否会使用 INDEX SCAN 代替 TABLE SCAN ,似乎不同环境有不同的结果。（RBO状态下）
我的一套环境中，alter index .. rebuild  ==> INDEX FAST FULL SCAN
alter index .. rebuild  online==> TABLE FULL SCAN
而另一套，则是alter index .. rebuild  /alter index .. rebuild  online 都采用了TABLE FULL SCAN

Oreborn

斑竹就是斑竹啊。
解释的很清楚...