Xref: alice comp.databases.oracle.misc:32896 comp.databases.oracle.server:52199
Path: alice!news-feed.fnsi.net!hammer.uoregon.edu!logbridge.uoregon.edu!newsfeed.stanford.edu!newsfeed.concentric.net!global-news-master
From: Nnoor@cris.com (NNOOR)
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server
Subject: Please help me solve this Oracle mystery...
Date: 06 Jun 1999 07:09:05 PDT
Organization: Concentric Internet Services
Lines: 28
Message-ID: <7jdve1$epg@chronicle.concentric.net>


Hi,

We are running Oracle 8.0.5. Machine is a dual Pentium Pro 200 with
512MB RAM and RAID level-3 hard drives.

In a query, a table with only 45 records was part of a join. Other
two tables had about 100,000 record each. The query ran very slow.
When the smaller table (only 45 records, indexed, two fields--one
varchar(10), the other varchar(35), indexed and joined on the smaller
field with fields of same kind) was dropped from the join, the
query was lightning fast. Nothing else changed in the query! When
drop and recreate the smaller table and make it part of the join 
again, query is now very fast. So dropping the table and recreating
it fixed the problem. Same thing happened on another query with
another table. Now, since we were "experienced", we dropped and
recreated that table and the problem fixed itself.

The Question: What do you think was causing such a behaviour? Can
the data in Oracle go corrupt? Fragmented? To a degree that a table
of only 45 records could bring a query to it's knees? Are there any
tools that we can use to check the integrity of the rest of the
data? Where would you start?

Thanks very much for your help.

Regards,
Nasir Noor (nnoor@cris.com)