I feel the problem about being slow is stemming up from excessive fragmentation of space and row-chaining which is an expected result from repeated deletes and inserts. You don't really have to opt for dropping and recreating the objects when you can move tables and rebuild indexes and get the same effect. In real world scenario if you were in production databases, dropping/recreating tables/indexes would give you a serious headache in terms of rendering all your pl/sql code [procedures, functions, packages and triggers etc invalid]. Please change the practice before it turns into a habit.
Gurmohan
www.onlymath.com - Place to learn and teach math.
-----Original Message-----
From: Eric Levinson
[mailto:oracledba-ezmlmshield-x46680834.[Email address protected]
Sent: Wednesday, November 02, 2005 1:02 PM
To: LazyDBA Discussion
Subject: RE: select count(*) on a empty table
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
Oracle LazyDBA home page