Re: How to "Defragment" Tablespaces ?

Okay, John. I have to bite on this one. Regarding this assertion:

> If your SQL
> statements are doing FULL TABLE SCANS, then you need to do it. If you have
> a tuned database that correctly uses indexes, then it is really not a
> problem.

What do full table scans have to do with tablespace defragmentation? If you suggest that throughput for multiblock reads are enhanced by contiguous blocks, you are only correct up to a point. With reads of about 1 megabyte you get a benefit from multiblock reads. Anything less than that costs too much for head repositioning and is severly sub-optimal. More time spent moving heads than transferring data. Anything higher than 1 meg offers only slight additional benefits.

So, if you have a table that you expect to scan fully, simply use 1 meg extents. And, if your tablespace has uniform extents of 1 meg each, all objects - tables, indexes, whatever - can share that space and reuse extents and you never have to defragment anything. Optimal performance. If IO contention shows up on a datafile in that tablespace, simply migrate an appropriate object to another tablespace. IO balanced. No consideration as to table or index relationships necessary.

Anybody care to refute this? Bring logic and facts, not DBA traditions and superstitions.

• ricky