What about using truncate?

-----Original Message-----
From: Saju Paul
[mailto:oracledba-ezmlmshield-x92213541.[Email address protected]
Sent: Wednesday, November 02, 2005 11:22 AM
To: LazyDBA Discussion
Subject: select count(*) on a empty table


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