Re: How to "Defragment" Tablespaces ?

Two weeks ago, I had the fortune of installing another hard disk on our system amd moving all indexes to this disk. The resulting increase in performance was incredible. Complex queries that took ages, took mere seconds afterwards.

Erich

Ricky Sanchez wrote in message <3ACE1E72.6DE37C75_at_more.net>...
>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
>
>
Received on Sat Apr 07 2001 - 02:56:00 CDT