Correct - your assumption of row by row is false.
Thinking back on previous email I've sent - I suggested your index MIGHT be
running at 50% efficiency and asked you to let us know the avg_col_length
and num_nulls for the column in the index: you haven't supplied that
information so there's no way to tell what the effect of a rebuild might be.
You've also reported your index stats as showing 11 million leaf blocks,
and most of the I/O being for the index; we've also estimated one I/O for
every two rows inserted. If you rebuild the index and it drops to half the
size (maybe only temporarily) then it's still 5,5M blocks and that might
not be anything like low enough to make a significant dent in the I/O.
Another piece of information you should dig up is the number of distinct
blocks there are in the cache that belong to the index - this might give
you a hint about whether a rebuilt index or a bigger cache would help.
select count(*) from (select distinct file#, block# from v$bh where objd =
{data_object_id of index})
If this is one of the partitioned indexes you'll have to do this for each
partition in turn
Regards
Jonathan Lewis
On Thu, Apr 9, 2020 at 5:34 PM Bhavani Dhulipalla <bhavanidba6@xxxxxxxxx>
wrote:
Thank you Jonathon-
Since it is inserting 400 rows per call and my initial assumption that it
is being done row by row processing is false?
Also as you said , oracle is doing 8 buffer gets per row and 0.5 disk
reads per row , Can I rebuild the index to eliminate the scattered inserts
in the index?
Thank you
Bhavani
