Re: Cluster problem - Repost

I think that you are pushing the app owners in exactly the right direction.

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

> Tim,
>
> Thanks for your reply.
> Basically I think that this was a mistake on someones
> part. The thinking was probably "Gee these two tables
> have the same single column PK , so let's create a
> cluster, 'cause there faster!". However, when the db
> was created, the other table never was coded to use
> the cluster.
> Now that the app owners realize that they are using a
> 1 table, 1 column cluster they are having second
> thoughts. Besides, this is a heavily used (DML) table
> and the doc says that heavily used (DML) should not be
> clustered, single read only tables are OK.
> Since this is the case IOT's should not be used here
> either.
> I am not sure if it is more I/O intensive to read the
> cluster index and then find the correct table row or
> read a b-tree index and find the correct table row.
>
> In any case, the goofy results I am seeing in the
> select statement make me very nervous about continuing
> to use them.
>
> I am pushing the app owners to drop the cluster and
> rebuild the table normally.
>
> As always I appreciate any comments or thoughts you
> might have.
>
> Thanks again,
> Frank
> --- Tim Gorman <Tim_at_SageLogix.com> wrote:
> > 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).
> >
> >
> >
>
>
> __________________________________________________
> 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).