DROP INDEX的影响

freezr

肯定不会影响应用的。

wzy25

for index a the leading column is the same as index b,so when a sql using index b ,after index b is dropped ,
system auto choose index a for that sql

Yong Huang

One thing I can think of is that if the original plan has an index range scan (or even full index scan or fast full scan) on A and when A is dropped, a range scan on B could be too expensive (too many blocks) for CBO to continue to range scan. So CBO decides to use full table scan instead. This is a contrived case. But you may be able to create a case where this plan change happens.

Yong Huang

rollingpig

很难说

建议模拟你们的生产环境，做一个完整的测试~~

chriszhang_1117

最初由 Yong Huang 发布
[B]One thing I can think of is that if the original plan has an index range scan (or even full index scan or fast full scan) on A and when A is dropped, a range scan on B could be too expensive (too many blocks) for CBO to continue to range scan. So CBO decides to use full table scan instead. This is a contrived case. But you may be able to create a case where this plan change happens.

Yong Huang [/B]

正在使用中的index没有办法drop吧!

rwq_

最初由 chriszhang_1117 发布
[B]

example:
index A: user_info_ind1 索引建立栏位:user_id+user_no+create_date
index B: user_info_ind2 索引建立栏位:user_id

我的意思是说Drop index B后,
如果之前应用有用到index B,那么Drop Index B后对应用的影响有多大,还是说应用会自动去使用index A? [/B]

从理论上讲不会，只是使用A索引的代价可能比较大了。

chriszhang_1117

最初由 rwq_ 发布
[B]

从理论上讲不会，只是使用A索引的代价可能比较大了。 [/B]

什么理论?
有什么文档资料可以参考?

rollingpig

最初由 rwq_ 发布
[B]

从理论上讲不会，只是使用A索引的代价可能比较大了。 [/B]

hehe
代价大了，可能就代表着PLAN会变了~~

Yong Huang

最初由 chriszhang_1117 发布
[B]
什么理论?
有什么文档资料可以参考? [/B]

The cost may be the same for index unique scan but higher for other index scans. We need to do tests to confirm.

Yong Huang