Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Clusters

Re: Clusters

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Sat, 09 Mar 2002 10:05:07 GMT
Message-ID: <3c89da20.6089129@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:

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 Received on Sat Mar 09 2002 - 04:05:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US