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
>

Oracle LazyDBA home page