Re: How to "Defragment" Tablespaces ?

> Okay, finish the paragraph. Why is it a good idea? Is there a performance or
> scalability reason you can think of? Putting indexes and tables in the same
> tablespace is not "getting away" with anything. Presumably this is a scaled
> application and we are not really talking about discrete drives anyway...
> stripe and mirror everything.
>
> There was this misplaced notion that started years ago, before raid systems
> became popular, that tables and corresponding indexes had these terrible
> concurrency conflicts and had to be kept apart. Well, it never was really true
> but the DBA myth persists. If, on the other hand, you manage objects by extent
> size and have a range of standardized tablespaces, I/O balancing becomes
> trivial. Set it up for manageability first, tune later.

My "misplaced notion" is due to the fact that not every installation of Oracle is done on RAID systems. To me, this is your misplaced notion. 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.

The only true way of guaranteeing that indexes and tables are located on different volumes is to ensure that they are in different datafiles. This can only be accomplished by placing them in different tablespaces.

While everyone can agree that it is best to stripe and mirror everything, this ideal situation does not always occur in practice. Constrained budgets often lead to comprimises and one of the things that often goes out the window is the extra disk that is required to mirror data.

Now the onus is on you... In the absence of striping and mirroring, how do you propose to improve your performance and scalabilty while placing indexes and tables in the same tablespace?

Just my 3.14159265 cents worth,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================