Re: Cluster problem - Repost

Tim,

That's a nice use of single-table cluster. Never heard about this before. I thought it's useles to have a single-table cluster.

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

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alexandre Gorbatchev
  INET: alexandre.gorbatchev_at_avermann.de

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