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: Re-use of Leaf Nodes

Re: Re-use of Leaf Nodes

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Wed, 12 May 2004 13:45:08 GMT
Message-ID: <ETpoc.34823$TT.11849@news-server.bigpond.net.au>


"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:40a217c8$0$23828$afc38c87_at_news.optusnet.com.au... SNIP.
>
> Oh lord! It never stops, does it?! Just when you think you've got
> something nailed, some other degree of freedom pops loose!
>

SNIP. Howard,

I investigated the INDEX REBUILD further and found the following results:  TEST_RUN# REBUIL UNIQUENESS PCT_FREE Leaf Blks ---------- ------ ---------- ---------- ---------

         1 Before NONUNIQUE          10        81
         2 After  NONUNIQUE          10        90
         3 Before NONUNIQUE           0        81
         4 After  NONUNIQUE           0        80
         5 Before UNIQUE             10        76
         6 After  UNIQUE             10        84
         7 Before UNIQUE              0        76
         8 After  UNIQUE              0        76

I ran the test run as before and then rebuilt the index while recording the statistics from DBMS_STATS before and after the index rebuild.

I was able to reproduce your result of having the index grow bigger by 8 or 9 blocks when PCTFREE=10 (the default). The only time I got an improvement was when I rebuilt a non-unique index with PCTFREE=0.

The unique index is smaller than the non-unique one.

This was done on 10.1.0.2 on WinXP Pro SP1.

Douglas Hawthorne Received on Wed May 12 2004 - 08:45:08 CDT

Original text of this message

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