Please help me solve this Oracle mystery...

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_at_cris.com) Received on Sun Jun 06 1999 - 09:09:05 CDT