Hi Abhay,

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?


You may also have a look at:

Performance poor after analyzeing table
http://asktom.oracle.com/pls/ask/f?p...3126073805757,

Density
http://asktom.oracle.com/pls/ask/f?p...2969235095639,


If you should find a realy good document which explains well how the histogramm is working and how it should be used it would be great if you could make a notice about it in this thread