Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: unbalanced indexes -- common wisdom?

Re: unbalanced indexes -- common wisdom?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Sat, 25 May 2002 10:52:17 +0100
Message-ID: <3CEF5ED1.74B4@yahoo.com>


Niall Litchfield wrote:
>
> 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

Test version was 9013

Definitely...The table is created initially in "ascending order" and thus the index is pretty much as good as it could be in terms of clustering factor. The normal distribution (ie bell curve) insert/delete test means a scattering of rows most of which will fall within the middle range of empno's which hence reorders the rows in the table. As the rows drift further from the index order, the clustering factor inevitably gets higher...But this is a good thing - as the row sequencing does drift from linear, the index does get less effective, ie more index blocks needed to satisfy a given result set, so having it look less attractive to the optimizer is the right thing..

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."
Received on Sat May 25 2002 - 04:52:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US