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

What could be happening is called 'high water mark'. As rows are inserted into a table, Oracle maintains a high water mark. Later, if rows are deleted, the high water mark remains. When you query the table, Oracle reads it all the up to the high water mark. Most good Oracle tuning books will have a good discussion on this. The only way to lower the high water mark is to either reorg the table or truncate the table (if you are purging all the rows).

John Seitz wrote:

> 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 - 23:50:10 CDT