| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Cluster problem - Repost
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
> >
>
-- 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).Received on Thu Aug 01 2002 - 18:13:36 CDT
 |
 |