Re: Q:Table Fragmentation. How do I reduce it?

Connor McDonald wrote in message <350E3E15.56FA_at_bhp.com.au>...

>Also worthy of note that in a normal (ie multi-user) environment then as
>a general rule, lots of extents will NOT affect performance. The
>popular belief that the performance of one extent is always better than
>many is a myth...

Well, that's not true if you have a system with large tables that you frequently full (or range scan - when you've partitioned the data or taken an explicit load order). The performance can be much worse, especially if the extent sizes are not matched to the IO size of the machine and the multi-block read count is also set to an unsuitable figure.

Not to mention what the heavy fragmentation is doing to the free-space coalescing...

Sure, many people become paranoid about having data in more than, say, 5 extents, and that is just silly. Likewise, throwing your hands up and saying any amount of extents is not a problem is also silly.

>
>This is not to say the exp/imp will not assist. Unloading and reloading
>the data has many benefits in terms of better packing the rows etc
>etc...But exp compress=y OR exp compress=n will give you the same
>benefits...

Er, no it won't. If you took one of my databases where I've spent the entire design stage setting suitable initial and next extent values for all my objects and imported it with compressed extents, well, I'd have to shoot you.

The point I'm trying to make is that this is a complex area. The original poster should read up a little. The Oracle White Papers on space management are a good start.

Steve Phelan.

>--
>==========================================
>Connor McDonald
>BHP Information Technology
>Perth, Western Australia
>"These views mine not BHP..etc etc"
>
>"The only difference between me and a madman is that I am not mad."
Received on Tue Mar 17 1998 - 00:00:00 CST