RE: Clusters and IOT's

Stephane and Ivan,

I've never experimented with clusters, mainly because of the reputation problems Stephane referred to.

It seems like I remember, and it was quite some time ago, that someone mentioned clustering, the traditional method, a single table and saw some benefit data retrieval wise. And it does make sense. Apparently for them, this outweighed the DML issues. Or maybe the data was fairly static. I don't know. This was a long time ago and IOT's may be a better solution nowadays anyway.

I also remember someone mentioning great success in using hash clusters for some large tables. I can't remember the particulars about their situation that made this a good choice for them.

This is something I would like to experiment with but just haven't found the time to do yet. The idea behind IOT's and hash clusters and when they can help is intriguing. I guess I need to get back to the manuals, books, Usenet, and the web to see if I can dig up some good info.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Stephane
> Faroult
> Sent: Thursday, June 21, 2001 4:13 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Clusters and IOT's
>
>
> > Ivan_Rivera_at_doh.state.fl.us wrote:
> >
> > Greetings to all.
> > I have to teach a small dba class tonight and we will be covering
> > clusters and index organized tables. I am a young dba and have never
> > used or managed either on. Can some one please give me some real
> > world examples of when its ideal to use these. Also which tends to be
> > better, hash clusters or index clusters. Any real world advice about
> > these would be helpful. Thanks allot. Ivan Rivera
>
> Ivan,
>
> Almost anybody with a long experience of Oracle tends to disregard
> clusters, because they have long been bug-ridden and when you have been
> bitten once ...
> Basically, the idea behind clusters is to improve performance in joins
> by physically grouping together the tables which are to be joined, and
> sometimes also to save space (since the key used for the join is stored
> only once). That is theory. In practice, your gain on joins is often a
> big loss on other accesses (since you are putting several tables in the
> very same blocks, to scan one of the tables you need to access more
> blocks than if it were alone), and there is an enormous waste of space
> because computing the proper storage parameters is a science which to my
> knowledge nobody has ever mastered outside benchmarks. Not to mention
> things such has when you drop or truncate a clustered table it takes
> ages and usually explodes rollback segments since it must be a DELETE of
> all rows (remember, storage is shared with other tables). Concerning
> hash clusters, I once tried to create three, bigger and bigger. Never
> succeeded in creating the third one (just the CREATE CLUSTER statement).
> Message : forget about them.
> IOTs are much more interesting and inherited from RDB, bought over from
> what was then Digital Equipment by Oracle a few years ago. As a result,
> IOTs are developed on the East Coast (Ma) while the rest is developed on
> the West Coast, and it shows. There are things which work with
> everything but IOTs (rather advanced features), and there are strange
> bugs and locking problems with partitioning, especially when done on the
> fly. You must know that there are some queries which are answered
> without accessing the table, simply because all the information is in
> the index. In some cases, you can use this and optimise a query by
> creating a concatenated index on all the columns in the SELECT list.IOTs
> are the same idea pushed to the limit: since all the data is in the
> index, why bother with the table? It's a great way to deal with big
> volumes of data (it saves a lot of space) when rows are relatively
> short. Now they are a bit confusing at the data dictionary level because
> IOTs are more indexes than tables, so the information is not always
> where you expect it.
> --
> HTH,
>
> Stephane Faroult
> Oriole Corporation
> Voice: +44 (0) 7050-696-269
> Fax: +44 (0) 7050-696-449
> Performance Tools & Free Scripts
> --------------------------------------------------------------
> http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
> --------------------------------------------------------------
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriole.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).