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


Oracle LazyDBA home page