Re: unbalanced indexes -- common wisdom?

Connor wrote
> Here is a set of examples, all of which show an index that probably
> would not get any significant from an index rebuild:

you omitted the version (unless i'm blind). this looks to me like behaviour that could be radically different in 9.1 from 7.3 eg 1

> SQL> REM Statistics after test
> SQL> REM
> SQL>
> SQL> select HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, CLUSTERING_FACTOR
> 2 from index_stats, user_indexes;
>
> HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS CLUSTERING_FACTOR
> ---------- ---------- ---------- ----------- -----------------
> 2 111 1 0 146

which is representative of all patterns of activity except

> SQL> REM
> SQL> REM Example 5: Random inserts/deletes - Normally distributed
> SQL> REM
> SQL> @@prelim.sql
> SQL> drop table emp;

<snip>
>
> SQL>
> SQL> declare
> 2 v number;
> 3 begin
> 4 for i in 1 .. 5000 loop
> 5 v := mod((dbms_random.normal+5)*100000,50000)*2;
> 6 delete from emp
> 7 where empno = v;
> 8 commit;
> 9 v := mod((dbms_random.normal+5)*100000,50000)*2;
> 10 insert into emp
> 11 values (v,'padding');
> 12 end loop;
> 13 end;
> 14 /
>
> PL/SQL procedure successfully completed.

> SQL> REM
> SQL> REM Statistics after test
> SQL> REM
> SQL> select HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, CLUSTERING_FACTOR
> 2 from index_stats, user_indexes;
>
> HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS CLUSTERING_FACTOR
> ---------- ---------- ---------- ----------- -----------------
> 2 221 1 0 9870

So with a 'normal distribution' of insert/update activity we get a much higher clustering factor than with your other eg's. this is perhaps evidence again that the pattern of data entry might have a significant effect on the execution plans of queries. or am I just plain wrong at 11:30 at night?

--
Niall Litchfield
Oracle DBA
Audit Commission UK