Re: Clusters

"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message news:3c89da20.6089129_at_news-vip.optusnet.com.au...
> > can somebody explain
> > - whats a cluster is
> > - where and how it can be used
>
>
> The concepts manual talks about this in much better detail than anyone
> here can. But here goes my feeble attempt anyways.
>
> A "cluster" is the name given in Oracle to the ability to share each
> disk block between two or more tables. The theory goes more or less
> like this:
>
> - if you have two or more tables that are joined often, you can store
> their corresponding rows together in the same block so that the number
> of reads to execute the join is essentially "halved".
>
>
> How does Oracle do it? Well, imagine a "parent" table and its PK.
> Now, the FK of any "child" tables will be a copy of the PK of this
> one, won't it? So, it stores any given row of the parent table,
> including its PK, in a given block. In the same block, it will store
> as many rows of the child table as have a FK = to the PK just stored.
> AND it will only store the part of those rows that is not FK: after
> all, the PK is already there and = to the FK, no?
>
> Come time to join both tables where PK=FK, one single read of this
> block will get you the row with the PK and any rows with the FK.
>
>
>
> That's the theory. In practice, rarely can you fit an entire set of
> data for more than two tables in a single block. Result is that
> Oracle will chain blocks, which everyone under the sun will tell you
> is a no-no and something to avoid at all costs. Oh well, there goes a
> good theory...
>
>
>
> Now, the practice. In fact, clusters can do a lot more than just
> potentially help with joins. A few tricks can be used.
>
> One of the things that Oracle does for you is to physically SORT the
> rows in a cluster on value of PK. And it KEEPS this sorted order for
> you. Does this ring a bell as a potential way of reducing I/O times
> when a table has groups of rows with the same FK value?
>
> You can basically store all rows of a given FK in the same block (or
> small number of chained blocks, hopefully contiguous), using the FK
> column as the cluster column. Nothing says you MUST store two or more
> tables in a cluster! One only is perfectly feasible.
>
> And a few other tricks. But this one is the most common. I've used
> it successfully in V7 to dramatically reduce the I/O needed to join
> two tables, although only one was in the cluster. Dunno if this still
> would work as effectively in 8 or 9i, although I can't see why not.
>
> Anyways, there you have clusters in a nutshell. They would make a lot
> of sense in times when I/O access was an issue. That is not the case
> in most systems nowadays, so don't go overboard with them. There are
> a few overheads in using them: they can cause a lot of sorting and
> re-arranging to go on without you knowing. So go easy on them.
> Unless you know exactly WHY you are going to use them.
>
>
>
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam

Nuno,

Good explanation of clustered tables. But what if Prakash was really asking about clustered machines?

Paul Received on Sat Mar 09 2002 - 15:16:56 CST