Re: Defragmenting table

Has the table had a significantly larger number of rows in the past? If so, the problem might be in the high water mark. The high water mark of the table shows what the largest number of records in the table was. When doing a count(*) the process keeps going until it reaches the high water mark, at which point it knows there's no more data and stops counting. If you have an empty table, that used to have 10,000 records in it, it will take as long to return '0 rows' as it would have done to return the '10,000 rows' when the table was full.

If this is the case you could reset the high water mark by creating a copy of the table, dropping the original, re-creating it and copying back .

You could also do it by exporting the table, truncating it (hauls the high water mark back to the beginning) and importing your data back.

"Leonard F Clark" <lfc_at_zoom.co.uk> wrote in message news:39ce4ece.14800031_at_125.0.0.1...
> Florent,
>
> Is this table significantly *wider* than the others (i.e. does it have
> a lot of columns, or some very large columns.
>
> We have found that a count(*) pulls the dictionary cache for that
> table (I think - it was a while ago and may have been some other part
> of the SGA). So one possible explanation is that a component of the
> SGA is sized too small.
>
> Len
>
> >Hi there,
> >I hope that someone could help me with a little problem.
> >I often have to do a query on a table, +/-30000 rows, but for some
> >reason the system takes between 10 to 40 secs to return from the select
> >count(*), statement, (other tables are 5 times the size and 3 times
> >faster).
> >There are 2 indexes and 2 primary keys. The size of the table would
> >indicate that it should be very quick.
> >For the purpose of the application I have to do a select count(*) often
> >as the exact number of rows varies from time to time, does any one know
> >a better way of doing it or a SQL statement that would defrag the table.
> >Any help would be great, (and would make me look great at work).
> >Thanks for all the help you can give.
> >Florent.
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
Received on Wed Sep 27 2000 - 06:13:36 CDT