Re: Opinions on multiple extents on tables/indexes

On 6 May 1997 13:31:31 GMT, scott.overby_at_sdsdata.com (Scott Overby) wrote:

>What is the conventional wisdom on having multiple extents on tables/indexes
>and the effect on performance?
>
>
>
>--
>==========================================================================
>Scott Overby E-mail scott.overby_at_sdsdata.com
>Sr. Systems Analyst
>Strategic Data Systems FAX (414) 459-9123
>615 Penn Ave.
>Sheboygan, WI 53082
>===========================================================================
>

If the extents are contiguous, then there really won't be a real performance hit. This also applies if the system is performing full table scans. I see more a performance hit in fragmented indexes. Here is where you can get a bigger bang for your buck.

I usually look at fragmentation after I have tuned the rest of the system (memory utilization, hot files, appropriately placed files and tablespaces...). I have found that defragmentation is not nearly as important as some of the other things you can do with the system. For example, look at a side by side comparison of a database that is refreshed frequently vs your production system. Do you see much of a difference?

-sheilah scheurich
DBA
scheuric_at_sprynet.com Received on Wed May 07 1997 - 00:00:00 CDT