Re: Index maintenance...

Depending how the load operation is written, it is possible for it to do more damage to the index than normal day to day arrival of data.

For example:

    Delete 25% of all rows
    Insert 'replacement volume'
    commit

might leave lots of nearly empty space
in an index that isn't going to be re-used until the next big load; whereas

    Delete 25% of all rows
    commit
    Insert 'replacement volume'
    commit

might leave the same index very well
packed.

An insert of a large volume of perfectly sorted data might leave 2/3rds of the
leaf blocks at about 50%, and one third
99% empty (I have an test case that
demonstrates this in my indexing tutorial, it's a little contrived, but not extremely so).

What's the dove-and-corn effect ?

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Optimising Oracle Seminar - latest dates.
http://www.jlcomp.demon.co.uk/seminar.html


"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message
news:c7abns$lm2$1_at_nntp.fujitsu-siemens.com...


> Hi!

> We've got a consultant (for a canned app) here who just hinted at a

possible performance improvement


> if one recreates the indexes after a big load.

> He said he noticed an improvement but didn't run any benchmarks. He used

8.1.something and 9.0.something.


> We are using 9.2.0.4.

> So, what's behind this?

> Does this apply to us too?

>

> Lots of Greetings!

> Volker