Re: Poor perfmance after large delete

In article <5jftum$igu$1_at_ocoee.iac.net>,

   mwagoner_at_iac.net (Mark Wagoner) wrote:
>We had been testing our database setup with sample data and decided to try
>and go live. The main table had 1.4 million rows, which I deleted by doing
>a series of DELETE FROM WHERE statements (I tried to truncate the table,
>but Oracle said there were constraints even after I disabled them all, but
>that is another problem). After about 2 hours the main tables were empty
>so I went in and did a SELECT COUNT(*) to make sure. It took almost 3
>minutes for the result to come back! It took less than 2 seconds when the
>table was full!

A delete doesn't reset the high water mark, so a full table scan will still read the same number of blocks, even while you delete all rows. Try a truncate on your empty table.
Maybe you had an index on your table, allowing Oracle to do a full index scan instead of a full table scan. This can explain the difference of 3 mins to 2 secs.

>I have rebuilt the primary key and the unique keys using ALTER INDEX ...
>REBUILD. I have also coalesced the tablespace to make sure any extra
>extents were freed. I even executed the analyze_schema procedure to update
>the dictionary. Nothing seems to help.

Coalescing only coalescs existing adjacent free extents, it does not free any empty extents occupied by the table. Truncate will free these. (Except the initial extent ofcourse.)

>Anybody know what is going on? I called Oracle but have yet to hear back
>from them (it has been 3 days now). I am afraid to start loading
>production data if the performance is going to be this bad.
>
>Version is Workgroup Server 7.3.2.2.1 on NT 4.0 w/service pack 1 installed.
>Any help would really be appreciated.

As a last resort, you can try an export/import with the compress=y option.

Regards, Stefan.

Name      :G.R.S. Deisz
Phone     :+31-50-5855954
E mail    :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
DISCLAIMER:This statement is not an official statement from, nor
           does it represent an official position of, PTT Telecom BV.