Re: Defragmenting table

<heepoon_at_my-deja.com> wrote in message news:8riuu0$tro$1_at_nnrp1.deja.com...
> 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.
>

alter table BLAH truncate;

Regards
HJR
> 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 - 19:25:13 CDT