Re: Defragmenting table

Hi,

   I encounted a similiar problem with high water mark. I would like to reset HWM. I see that you are suggesting to export the database and truncate that, and import back in. I am not sure what do you mean by truncating. What command is that on the command? Or I have to edit that manually? Thanks in advance.

In article <39d1d65d_at_rpc1284.daytonoh.ncr.com>,   "Sally Pearce" <anon_at_anon.com> wrote:
> 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.
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 05 2000 - 17:21:58 CDT