Re: Fragmentation in Oracle

Hi Tom,

Though I did not try your method, I was able to prove what I said before. Please try this on your site and see what happens.

create table sv_dave as select * from <<<any large table>>> create index sv_dave on sv_dave (<<any unique field>>>) <<< check the number of extents>>>
delete from sv_dave;
insert into sv_dave as select * from <<<the same large table>>> commit;
<<<< check the number of extents on the index>>>>
>>> do the insert / delete /commit repeatedly a few times
<<<< check the number of extents on the index >>>> It should have grown.

The table I used was 27000 records and the index field was defined as number(7).

In my example the first time I create the index it had 2 extents. After I deleted / inserted / committed a few times it had grown to 9 extents though the number of records in the table was still 27000 records.

The only explanation I would have that your test did not work was because the size of the table / index was relatively small.

By the way, do you work for Oracle? Your address is *.us.oracle.com?

Thomas Kyte wrote in message <346331ed.7800857_at_newshost>...
>On Thu, 6 Nov 1997 20:11:28 -0000, "Dave S." <dsingh_at_tgtsolutions.com>
 wrote:
>
>>Try using an 80% (or higher) PCTUSED setting on the table. Please see
>>below.
>>
>>Also beware, Index space IS NEVER REUSED. If you add records, then delete
>>them, the index space is not release nor reused.
>>
>
>
>Thats not true. Index space is reused. (like a table, space is not
 released to
>be used by other objects, but it is reused within the index).
>
>To show this, I created a table. I indexed a column in that table. Put
 1,000
>rows in there. I repeatedly delete and add records to this table. After I
>delete 500 records and add 500 new records (using values never before seen
 in
>the table), I analyze the index and dump the results. I do this over and
 over
>and over. If Oracle never reused index space, we would expect the index to
 grow
>infinitely large over time. In fact, the index never really grows -- it
 reuses
>the space as it is freed up. the following table is the output of the

.<<<<< Data was removed to save space>>>>>
>-----------------------------------------------------
>
>[snip]
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Bethesda MD
>
>http://govt.us.oracle.com/ -- downloadable utilities
>
>---------------------------------------------------------------------------
 -
>Opinions are mine and do not necessarily reflect those of Oracle
 Corporation
>
>Anti-Anti Spam Msg: if you want an answer emailed to you,
>you have to make it easy to get email to you. Any bounced
>email will be treated the same way i treat SPAM-- I delete it.
Received on Fri Nov 07 1997 - 00:00:00 CET