Coalesce only works on free extents, i.e. puts small adjacent free extents into larger free extents. Deleting rows from a table does not free up any extents.

rbv


> If you COALESCE the tablespace instead of using the move command, would that
> help performance in this case?
>
> Mark
>
>
>
>
> -----Original Message-----
> From: Shergill Gurmohan
> [mailto:oracledba-ezmlmshield-x80069642.[Email address protected]
> Sent: Wednesday, November 02, 2005 9:32 AM
> To: LazyDBA Discussion
> Subject: RE: select count(*) on a empty table
>
>
> If some data has been put in ever since last delete then the truncate
> wouldn't be an option at this time. Rebuilding table online with no
> significant data will also bring the hwm down. You can use the following
> statement and vary the storage clause also if desired:
> ALTER TABLE <tablename> MOVE Tablespace <New or same tablespacename> PctUsed
> 80 PctFree 10 IniTrans 1 MaxTrans 255 Logging Parallel 2 Storage (Initial 16K
> next 16 K PctIncrease 0 MinExtents 1 MaxExtents UNLIMITED Freelists 1
> Freelist Groups 1 Buffer_Pool Default);
> After move it is good idea to rebuild the indexes online also if your tables
> has some data in there.
>
> Gurmohan
> www.onlymath.com - Place to learn and teach math.
>
>
>
> -----Original Message-----
> From: rbvogel
> [mailto:oracledba-ezmlmshield-x6886649.[Email address protected]
> Sent: Wednesday, November 02, 2005 11:29 AM
> To: LazyDBA Discussion
> Subject: Re: select count(*) on a empty table
>
>
> OH.... forgot another way to reset HWM... "truncate table ....." even
> easier than "alter table move..."
>
> rbv
>
>
> > We have a table that had some 20,000+ rows. All rows in this table were
> > deleted using sqlplus. Now the response time for a select count(*) on this
> > empty table is very slow. The table has 5 user indexes and 3 system
> > generated indexes.
> >
> > Assumed that Oracle was holding onto to some old index information and so
> > the statistics for the table and its indexes were re-computed using OEM
> > (enterprise manager). That did not help. So I deleted the statistics and
> > re-computed new statistics for this object and that did not help either.
> >
> > So had to take a drastic action of dropping the table and it indexes and
> > re-adding them. That took care of the response time problem but is there a
> > less drastic workaround to my problem. What am I missing here ? What sort
> > of cleanup is required on a table after all it's rows have been removed ?
> >
> > The database is Oracle 10g; the SQL tools used were sqlplus & isql (via
> > ODBC)
> >
> > TIA
> >
> >
> >
> > --------
> > website: http://www.LazyDBA.com
> > Please don't reply to RTFM questions
> > Oracle documentation is here: http://tahiti.oracle.com
> > To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> > To subscribe: see http://www.lazydba.com
> > By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
> >
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>

Oracle LazyDBA home page