I got a similair problem but didnt have yet enough time to realy investigate it and find out how this histogramms work and how they are used by the CBO.
I guess the reason for the different behaviour is the value of density which change from 0.0003 to 0.005. So the optimizer thinks that it should be faster doing a fts then accesing each row via the index. Why? I haven't got a clue, he doesnt even know if the record exists!?
What happens if you lower the value of DB_FILE_MULTIBLOCK_READ_COUNT does he still do a fts?
Thanks for the links, but I am still not convinced as to why oracle behaves like this, according to my understanding its thinking somethink like
Avg No Of Non popular values per bucket=16.92425926283973353
Number of values per bucket=42
So Avg Number of popular values per bucket ~~ 23
It even isnt recognizing when queried with the very low (repeating values) & its going for FTS. I think 145 bucket size isnt sufficient for distinct count of 2000+ to abloutely determine the skewness of the column.
can somebody correct if i am wrong, or even suggest.
Well i am now getting convienced that Histograms on highly/averagely spread values should not be taken histograms. Well i only wanted to see how Oracle will calculate Evenness of the column.
Histograms will well be useful if distinct count of the value is well less than 254.
I would like to get suggestion anyhow ( from probabaly slimdave, pando, jurij, dapi, tamil, chris )
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
This guy has a lot of complex scripts that would do the histogram-trick correctly.
Does anyone use these scripts? And if so, do they work okay?
I don't want to just copy the scripts and run them obliviously, but I can't determine if these script are totally useless or not. The biggest problem - for me - is the technical english in some of these docs.
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.