Re: Defragmenting table

You were probably thinking of partitioned tables.

alter table blah truncate partition blahblah;

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Howard J. Rogers wrote in message <39dd29a3_at_news.iprimus.com.au>...


>

>"Howard J. Rogers" <howardjr_at_www.com> wrote in message

>news:39dd0e70_at_news.iprimus.com.au...

>>

>> <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;

>

>

>Total rubbish of course.  My apologies, and I can't think where it came

>from.

>

>Truncate table BLAH will do it.  Nothing to do with altering the table at

>all.  God.

>

>Regards

>HJR

>

>

>>

>> 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.

>>

>>

>

>