Eric, I think one approach to keep performance after a massive delete or
update on any table might be to rebuild all your indexes, at least your
PK.

Also, you may think about analyze your tables and your indexes regularly.

Regards

----- Original Message -----
From: "Eric Levinson "
To: "LazyDBA Discussion"
Subject: RE: select count(*) on a empty table
Date: Wed, 2 Nov 2005 10:01:36 -0800


We had similar problems. The table never had more than about 20K
rows,
however over the course of time, rows get inserted, deleted,
instered,
updated, probably over a million times. We found that sometimes you
just have to drop the table and recreate it. I kept the drop/create
scripts handy. Since the table was never empty at any one given time,
we couldn't just do a truncate. We would copy the data to a temp
table
with no indexes, and then drop and recreate the original table, and
then
copy the data back and drop the temp table.

Not sure why this happens. We were using 8.1.7.4 db. Sometimes the
select count(*) would cause an ORA-600 error.

It might be better to avoid hard deletes of rows, i.e. add a new
column
called "status" as number(1) type. Set the default to 0. When you
want
to delete a row, instead set the status to 1, and don't delete it.
Change your where clause in your code add AND STATUS=0 to all your
logic. When inserting, use an update instead and look for rows where
status=1, and make sure you set it to 0, along with all the other
columns in your table. This could be a huge reengineering project, or
it could be small depending on what you are doing. Basically you are
maintaining your own system for inserts/deletes.

Eric



-----Original Message-----
From: Saju Paul
[mailto:oracledba-ezmlmshield-x92213541.[Email address protected]
Sent: Wednesday, November 02, 2005 8: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



--------
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

--
___________________________________________________
Play 100s of games for FREE! http://games.mail.com/


Oracle LazyDBA home page