Re: How to "Defragment" Tablespaces ?

> > > 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?
> > >
> Here's a thought for you, Brian:
>
> Why were clusters invented? I was under the impression that that could
> speed up
> operations by putting frequently used tables and their indexes into one,
> physical
> place. O/S's being what they are, the read operation will fetch some
> 64kB - good chance
> everything you need is in that (disk) buffer: indexes, tables, the lot.
> 1 read, have all.

Clusters were invented to basically pre-join tables. If table_a and table_b are often joined on a shared column, then you can pre-join the tables on this column (called the cluster key). It does not place the table and indexes into one physical space, only the two (or more) tables in the cluster. The indexes can still reside in a different location for clusters. So reading a cluster will not read the tables and indexes in the same operation. Reading the tables and indexes will be different operations. But reading the join of table_a to table_b on the cluster key will be one operation.

Personally, I've never seen a good case for clusters. In every situation where a cluster has been proposed, I've always said that the same could be accomplished just by denormalizing the schema. I'd like to hear of a good case where a cluster is better than denormalizing two tables. So if someone has a good case, please let me know.

Have a good day!
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!
========================================