Home » RDBMS Server » Performance Tuning » To increase performance Delete statement
To increase performance Delete statement [message #135340] Wed, 31 August 2005 03:12 Go to next message
ashishodia
Messages: 10
Registered: August 2005
Location: Mumbai
Junior Member


Hi All,

I have a procedure to purging tables using followins delete statement each statement have large number of records to delete and take vaery long time .ther is any way to off logging (no need to maintain log for these statement ) or anyother way to improve performance of these stmt.


DELETE FROM CS_ACTIVATE_DEACTIVATE_SERVICE WHERE TRUNC(TIME_STAMP) < TRUNC(SYSDATE) - 30;
COMMIT;
( more then 60,000 record to delete )
take more time


DELETE FROM P_CS_ACT_DEACT_SERVICE WHERE TRUNC(TIME_STAMP) < TRUNC(SYSDATE) - 7;
COMMIT;

( more then 60,000 record to delete )
take more time


DELETE FROM CS_SEND_SMS WHERE TRUNC(MAKEDATE) < TRUNC(SYSDATE) - 30;
COMMIT;

( more then 60,000 record to delete )
take more time

DELETE FROM ERROR_LOG WHERE PROCESS IN ('XLU','XLD','SMS','SC') AND TRUNC(SYSDATE) -TRUNC(RUN_DATE) > 7;
COMMIT;
( max 100 record to delete )



DELETE FROM UPLOAD_DETAILS_TD A WHERE A.MAP_CODE IN ('XLU','XLD','SMS','SC') AND A.BATCH_ID IN
(SELECT B.BATCH_ID FROM UPLOAD_SUMMARY_TH B WHERE A.BATCH_ID=B.BATCH_ID AND A.MAP_CODE=B.MAP_CODE
AND TRUNC(SYSDATE) - TRUNC(B.MAKEDATE) > 15);
COMMIT;
( max 20 record to delete )



DELETE FROM UPLOAD_SUMMARY_TH WHERE MAP_CODE IN ('XLU','XLD','SMS','SC') AND
TRUNC(SYSDATE) - TRUNC(MAKEDATE) > 15;
COMMIT;
( max 20 record to delete )


DELETE FROM CS_CASE_DETAILS WHERE
TRUNC(SYSDATE) - TRUNC(CONTACT_DATE) > 91;
COMMIT;
( more than 5000 records to delete )


Regards,

Amit hishodia


Re: To increase performance Delete statement [message #135402 is a reply to message #135340] Wed, 31 August 2005 07:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Few thoughts here.
1. First do not commit between the deletes.
Do a one final commit.
2. When was the last time you analyzed / gathered the statistics for the
concerned table and index (if any )
After every major delte, you need to gather stats again.
3. It may also becuase of allocated extents.
Every time you delete, YOu only delete records.
Extents are never deallocated.
try this ( this will not move your indexes. you have to it seperately)
alter table mytable move tablespace same_tablspace;
try your delete now ( after gathering stats)
4. 60,000 is not at all a big deal.
First apply the above methods. You will see the difference.
If you have already done, we may need to look into other.

60,000 records is what you delete.
what is the total count of records?
say if you have 70,000 records and you are deleting 60,000 records out of it,
then the CTAS should help. DO something like this

create table another_mytable NOLOGGING as select (all the 10,000 records you want to keep) from mytable;
truncate table mytable.
insert /*+ append */ into mytable as select * from another_mytable.


Re: To increase performance Delete statement [message #135424 is a reply to message #135340] Wed, 31 August 2005 09:16 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Mahesh mentioned a lot of good ones, and 60,000 isn't very many, unless you have a very slow hardware. If after trying all the above you still don't see improvement, may try disabling many of your indexes on tables before the delete, then rebuilding them afterwards. Especially if you have lots of indexes that won't actually aid the deletion but slow it down due to them being maintained.
Re: To increase performance Delete statement [message #135494 is a reply to message #135340] Wed, 31 August 2005 17:05 Go to previous messageGo to next message
ChrisJr
Messages: 17
Registered: August 2005
Junior Member
Other thought is mytable can be dropped and another_mytable can be renaned to mytable.Might be faster.

create table another_mytable NOLOGGING as select (all the 10,000 records you want to keep) from mytable;
drop table mytable.
rename another_mytable to mytable;
Re: To increase performance Delete statement [message #135563 is a reply to message #135494] Thu, 01 September 2005 04:26 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
In scenario described by Chris, you will be required to rebuild your indexes.

--Girish
Re: To increase performance Delete statement [message #135590 is a reply to message #135563] Thu, 01 September 2005 06:23 Go to previous message
IngRMP
Messages: 7
Registered: September 2005
Location: Spain
Junior Member
checks the existence of indexes in all the tables.
Previous Topic: query tuning with NVL
Next Topic: Union takes more time please help
Goto Forum:
  


Current Time: Fri Dec 09 14:00:32 CST 2022