Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Re-use of Leaf Nodes
"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