Re: How to "Defragment" Tablespaces ?

Brian-

The notion that there are IO conflicts when tables and indexes share a drive was misplaced before RAID became poplular. The benefits of RAID have nothing to do with the separation of tables and indexes. You state:

> True, if all datafiles were on RAID that striped the data across
> multiple volumes, then there is a really good chance that your indexes
> and your tables will be on different volumes thus increasing performance
> and scalability. This is not a myth, but sound theory and database
> design employed by DBAs worldwide.
>

How does the separation of tables and indexes improve performance and scalability? You say it is sound theory and database practice, but I say that it is simply tradition based on superstition, not sound theory at all. There is no sound theory that supports your assertion.

I described in my previous reply several Oracle block access scenarios and how indexes and tables do not conflict in their IO. If you are saying I am wrong, then tell us why you think so. Describe an operation that proves me wrong. The idea that DBAs worldwide have been doing this for years is no recommendation at all.

As for onus, I don't propose to improve performance by placing tables and indexes in the same tablespace. I merely assert that this is no impediment to performance and scalability and that this performance myth is not a valid argument for the practice of tablespace defragmentation. I further assert that, with reasonable space management practices such as those I sketched in an earlier reply, there is no sound reason at all to do tablespace defragmentation.

Back to you...

• ricky