Re: Clusters

And what about Hash Clusters, huh? Huh??

(My favourite segment, actually!)

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
news:3c8a7f7e_1_at_mk-nntp-1.news.uk.worldonline.com...


> "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

>
>
>
>
>
>
>