How to force the optimaizer to stop using an index ?
659979Sep 16 2008 — edited Sep 21 2008Hello,
I have an index on a very large table and I want to see how the applications running on this DB handles a situation in which this index does not exists.
Therfore I have 2 options:
1. drop the index - but this is too risky as if i will need to recreate it , it will last forever and will result in long application downtime which I can not afford.
The test and Dev environments does not contain ammounts of data like the prodicution so its hard to predict from these environments.
2. the second option is Modify the index columns statistics (which I have done) giving it low NDV (number of diftinct values) -- but sadly this did not work.
when cheking event 10053 I see a the line "Using prorated density: [ 1/(2*num_rows) ] of col #n as selectivity of out-of-range value pred" which I guess meens the High value of the column is lower then the value given it the query ( I do not run statistics every day , the current statistics for all columns (execpt this one ) suits best and I do not want to handle plan changes . also it will take a lot of time to gather table stats on daily basis.
So - to make a long story short - how do I force Oracle to avoid using an index but continue builing it and without dropping it?
I can not use the 'NO_INDEX' hint as it will requre change in many Applications.
I don need something like NO_INDEX hint on system level.
I am using Oracle 10g Enterprise edition.
the optimaized_mode is set to COST BASED.
Thanks in advance for any help resolving this complicated issue.
Regards,
Amit Zor.
ADBA.