Re: How to "Defragment" Tablespaces ?

Sybrand, you are being silly. You are asking me to prove the absence of a needle in a haystack. The discussion originated around defragmenting tablespaces. I breezed off an outline of a method to avoid the need for tablespace defragmentation. There is more to the method, but the essentials were presented and are sound.

The myth to which I referred is the mistaken notion that tables and their indexes must not reside in the same tablespace - or datafile, from the pre-raid days. What is missing here is any proof or even reasoning as to why that might be or ever have been been necessary.

By the way, you can drink a Pepsi and eat a packet of Pop Rocks at the same time and not have your stomach explode, but I cannot really prove that. The only reasons DBAs continue to defrag tablespaces are 1) it has always been assumed to be necessary and 2) vendors like Quest and CA sell products that defrag tablespaces and, like the AMA, have no interest in preventive medicine or sound space management practices.

The only plausible argument for tablespace defragging would be to avoid situations where odd-sized extents are "stranded" in a tablespace because all the objects required non-standard extents. So, you had to defrag to reclaim the space*. Using uniform extents eliminates that issue altogether.

IO contention is another matter. I assert that tables and their corresponding indexes can co-reside on the same tablespace without special issues of contention. Of course, you would expect them to be placed according to extent size requirements as described in my original response. Assuming that their extents were the same size, and that such size for the tablespace is mapped to the IO multiblock capabilities of the underlying operating and storage systems, no particular or unique IO contention will exist for them. Certainly not in a scaled environment where performance is actually an issue. Consider the following:

You are either reading randomly or you are doing a full table scan or a fast full scan of the index. In the latter two cases you do multiblock reads and accesses for the two objects are then mutually exclusive. That is, you do a full scan on either the index or the table, but not both at the same time. In cases of single block reads, such as b*tree index navigation and single block table (row) fetches, you have random reads on different parts of each object and there is no gain by placing them on different devices or tablespaces. The reads are random anyway and head positioning is irrelevant - ie you can neither predict nor control it. Factor in other users concurrently accessing the same objects and the reads even more random.

Now someone will come along and say, "Well, Ricky, fine and well, but what about those nested loop joins? Or those range-selects that naive application developers use in a pathetic counter-productive attempt to avoid sorts?" Glad you asked.

In the case of a nested-loop join, you read an index value and then get the corresponding row. The index is read randomly (logically sequential, physically random) and the single row is read randomly from the table. Again, no gain from separating the objects. You take a big hit for reading the same blocks repeatedly as you visit rows in logical index order, but we explode that myth in another thread.

So, show me why it is necessary or helpful to defrag a tablespace, or to deliberately separate tables and their indexes.

-ricky

• footnote: We also had that extent limit thing before 7.3, so we did the defrag drill more to coalesce than to defrag per se. Now we have unlimited extents and naturally, fools who actually attempt to have unlimited extents and find that it takes three days to truncate a table. But that is solved by locally managed tablespaces and is a topic for yet another thread.

Sybrand Bakker wrote:

> So far the only thing I see is non-documented assumptions.
> If you are so sure about this, and even use words like 'DBA myths', why
> don't you just come up with *Proof*
>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>
Received on Thu Apr 05 2001 - 15:30:46 CDT