Re: Index size growing abnormally .....Oracle bug ?

You could try

    select /*+ index */ count(ind_col_1)     from tab
    where ind_col_1 > {extreme value}

Couple of problems.

    It misses the branch blocks

    It may miss top-end blocks if there are lots of     entries starting with a NULL

    Oracle may choose to do a fast full scan in     parallel (if suitable parameters are set) which     would not clean the blocks.

You have to be in a fairly extreme case, though to need to do this - i.e. a single very large delete that guarantees to empty enough blocks to make it worth doing. Alternatives that may be cheaper are:

    alter index rebuild online;
    alter index coalesce (8.1 only)

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

DNP wrote in message <38CB4C1D.2FCA_at_btinternet.com>...


>Hello all --

>

>Question - which optimizer hint would be the best at getting Oracle to

>do some sort of scan on the entire index (to touch all the index blocks,

>branch and leaf) to solve the delayed block cleanout problem after many

>deletes?

>

>

>David P.