Re: Cluster problem - Repost

I can't answer your question directly, but perhaps we can address it indirectly? At any rate, I'm curious for some more information...

I understand that you've just "inherited" this database recently, but do you know the reasoning why a cluster was employed here? Especially a single table cluster?

>From my experience, the only purpose for having a single-table cluster is to
use the mechanism of "cluster keys" to associate logical key-column data values with placement within database blocks.

In other words, clustering is one way of guaranteeing that rows with the same cluster-key value reside in the same database block(s). Is that the intent here? I have used such a mechanism in the past, mainly for reducing contention in a "to-do list" table where numerous concurrently-executing jobs are locking rows, each row representing a "job" in a list of "job" (i.e. a "to-do list"). Using a single-table cluster ensured that I wouldn't experience block-level contention on the table between job-processors. I also made it a hash-cluster so I wouldn't experience contention on an index either. Let's just chalk this up to being a duhveloper, and being young, dumb, and full of ... beans...

If you're not aware of such a justification, then I'd be hard-pressed to understand why such a mechanism would be used. It is certainly transparent to the application (i.e. does not alter the SQL) whether you have a normal "heap-organized" table, an "index-organized" table, or a "clustered" table, isn't it?

Clusters are one of those "niche" mechanisms which were designed to address a specific narrow set of requirements (like "index-organized" tables, multi-threaded server, connection manager, etc), while often impacting other requirements quite negatively. Clusters in particular seem doomed for de-support, if you care to read actual intentions into the lack of any recent enhancements or support by Oracle lately. If you're not aware of fulfilling any of those specific requirements, then you should consider converting the table to a normal "heap-organized" format using a simple CREATE TABLE AS SELECT, in my opinion...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, August 01, 2002 12:13 PM

> All,
>
> I am having a problem with a cluster. My environment
> is Oracle 8.1.7 on Win2k on a COMPAQ server. This is a
> production DB that has been running for a year or so
> and I have now taken it over.
>
> My problem is that there is a table that has 1 column
> in a cluster. The cluster does not contain any other
> columns / tables.
>
> In the DB when I issue the query:
> select * from table where seqno = 341124;
> I get no rows selected. I know that the data is there
> because when I run:
> select * from table
> where seqno = 341124 and defect_no =1;
> I get:
> SEQNO DEFECT_NO X Y TESTMODE_INDEX DEFECT_INDEX IO
> ----------- ---------- ---------- ----------
> -------------- ------------ -----
> NUM_BITS START_ROW START_COL END_ROW END_COL
> ADJACENT_DEF
> TEST_COUNTER
> ---------- ---------- ---------- ---------- ----------
> ------------
> ------------
> 341124 1 14 11 -1 20 R0
> 25 0 0 0 960 27
> 9999
> I have traced both queries and the first query uses
> the cluster index, while the second uses the PK on the
> table that is in the cluster.
>
> I have validated the cluster using analyze
> succesfully. I am not sure what else to try and Oracle
> just wants me to rebuild the cluster.
>
> I am interested in any info/expierences with clusters.
> Any general problems, corruptions etc as I am trying
> to talk the application owners out of them.
>
> Any other ideas appreciated!
>
> Thanks!
> Frank
>
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mr Frank Pettinato
> INET: xfire_girl_at_yahoo.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: Tim Gorman
  INET: Tim_at_SageLogix.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).