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