Even though the rows were deleted the table still had a high water mark established. This high water mark does not get reset until the table is reorganized.

Easiest way to do this. "alter table xxxxxxxx move ...." to the same tablespace. Since the table is empty none of the indexes should need to be rebuilt.

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