Given that we are currently guessing that
the problem is an unlucky pattern of empty
blocks appearing on the truncate, you might
try to do a coalesce on the index that causes
the problem immediately after the truncate.
The 'maintain global indexes' simply does a bulk delete (like an sql*load in reverse) from the index, it doesn't rebuild it. And this is why you can get the empty blocks.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 4th Nov 2005
You are 100% correct in your advice. I felt the same. Can you please suggest me any other workaround to achieve the same, I mean to bypass this oddity sequences what I am following. It would be a great help and great relieaf for me, if you could give any workaround. Otherwise, should I include index coalesce as a practice after all the prior sequences done? My question is, when I am truncating partitions and updating global indexes, wont that I am rebuilding them? If so, index still left with empty leafs?
Thanks for your time Jonathan.
-- //www.freelists.org/webpage/oracle-l