Re: Please help me solve this Oracle mystery...

I was having the same problem at my work. What was happening was I had be testing data purging code for about a week with a table that had 1 million rows. I would fill the table up with data then purge it. After about a week, I finished my testing. Then i did a count on the table. It took about 30 seconds, however the table had no records.

So what I had to do is export the entire user. Drop the tablespace, delete the datafile, and start fresh with a new tablespace. Then imported the data. Now if rocks, even with 1 million rows.

John

NNOOR wrote:

> 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 Mon Jun 07 1999 - 00:41:09 CDT