Re: Does the number of extents affect the performance?

On Sat, 20 Sep 1997 15:13:35 -0500, Gary England <gengland_at_hiwaay.net> wrote:

>jkstill_at_teleport.com wrote:
>>
>> I ran a quickie test on this very subject the other day.
>>
>> I created to versions of a fairly large ( 65 meg ) table.
>>
>> One version had all rows in 1 extent. The other was in
>> 85 extents. I did not take DB_FILE_MULTIBLOCK_READ
>> into account. I simply sized the extents to ensure that a
>> large number of extents was created.
>>
>> Using tkprof, I discerned a maximum performance degredation
>> of 2% on the table with 85 extents when doing full table scans.
>>
>> When I consider the enormous savings that can be gained
>> elsewhere, ( tuning SQL for example ), it's hard to justify
>> spending time defragging tables.
>
>The impact to performance is the index file. Frag the data file into
>bits and pieces and you may see a difference; break up the index and
>you'll die quickly.

Well...

Call me a skeptic, but I really don't think so. Indexes aren't generally read in contigous blocks to start with.

And just as with table fragmention, show me the numbers and I will believe it.

Indexes do need to be rebuilt occasionally, though not because of space fragmentation. When rows in an index are deleted, Oracle does not reuse the block until all rows are deleted from it, causing an index to take up more space than necessary. Received on Sat Sep 27 1997 - 00:00:00 CDT