If you delete all the rows of a table, why don't truncate it? It's much
faster and the allocated space is free,
which is not if you delete the rows - maybe this is the clue to your
problems.

regards

OMD Germany GmbH

Achim Luettel
Database Administration & Development
Grünstr. 15
D-40212 Düsseldorf
Germany
Phone: +49-211-38807-522
Fax: +49-211-38807-531

mailto:Achim.[Email address protected]
http://www.omd.com

OMD - The World's Most Awarded Media Agency





"Saju Paul " <oracledba-ezmlmshield-x52528826.[Email address protected]
02.11.2005 17:22

An: "LazyDBA Discussion" <[Email address protected]
Kopie:
Thema: 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