Re: Clusters and IOT's

"Khedr, Waleed" wrote:
>
> SYS objects could be a good starting point for clustered objects.

You have a point :-). That said, the way dictionary objects are accessed (buffered in the library cache, most of the time) make them, IMHO, hardly suitable to be used as 'role model' for an application designed by mere mortals. Not to mention the fact that DML performed on SYS tables, aka DDL, is not precisely, as far as concurrency goes (CREATE TABLESPACE, anyone ?) what people try to achieve. I have always considered the SYS tables as an excellent exemple of relational design - used to, rather, because you have more and more ugly patches (I have subpartitioning in mind) over what was a clean design - more than as the ideal user application.
  Larry's remark about a single, clustered table, is quite true. The idea is that if you cluster the table on columns which are repeated (which of course excludes the primary key), then you have to store these columns only once for several lines - a relatively good analogy would be a BREAK ON under SQL*Plus with the suitable ORDER BY, showing duplicated values only once. It can save a lot of space, but for this to work it implies that the amount of bytes associated with one key is roughly constant, otherwise you are going to save space in some blocks and waste a lot elsewhere, so the benefit can be hard to predict. Something like ten years ago, I overheard a discussion into which another consultant was saying that you usually met customers in real need for clusters (case studies, really) once every five years. I had roughly come to the same conclusion, but that was in pre-IOT days. I think that if you cannot solve your problems with IOTs today, it is unlikely that clusters will help you.

-- 
Regards,

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



> 

> -----Original Message-----

> Sent: Thursday, June 21, 2001 8:17 PM

> To: Multiple recipients of list ORACLE-L

> 

> 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

-- 
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).