Home » RDBMS Server » Performance Tuning » Delete statement is taking more time for execution
Delete statement is taking more time for execution [message #446619] Tue, 09 March 2010 11:08 Go to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
Hi,

In my code I am using delete statement which is taking too much time to execute.

Statement is as follow:

DELETE FROM TRADE_ORDER_EMP_ALLOCATION T
WHERE (ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT)
IN (SELECT ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT
FROM LOAD_TRADE_ORDER
WHERE IND_IS_BAD_RECORD='N');


Tables Used:
o TRADE_ORDER_EMP_ALLOCATION Row count (329525880)
o LOAD_TRADE_ORDER Row count (29281)


Every column in "IN" clause and select clause is containing index on it

Every time no of rows which to be deleted is vary (May be in hundred ,thousand or hundred thousand )so that I am Unable to use "BITMAP" index on the table "LOAD_TRADE_ORDER" column "IND_IS_BAD_RECORD" though it is containing distinct record in it.


Even table "TRADE_ORDER_EMP_ALLOCATION" is containing "RANGE" PARTITION over it on the column "ARTEMIS_SOURCE_SYSTEM_ID". With this I am enclosing table scripts with Indexes and Partitions over it.


Can anyone please suggest me the way for fast execution in of above delete statement?


Regards,
Akash Nathile
Re: Delete statement is taking more time for execution [message #446632 is a reply to message #446619] Tue, 09 March 2010 13:32 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Delete statement is taking more time for execution [message #446736 is a reply to message #446619] Wed, 10 March 2010 03:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How long is too long? How long does it take, and how many rows are you deleting?

Quote:
Every column in "IN" clause and select clause is containing index on it

Do you mean that there is a single column index on each column?

Looking at the query, and absent an explain plan or trace, I'd create the following indexes:

TRADE_ORDER_EMP_ALLOCATION
(ARTEMIS_SOURCE_SYSTEM_ID
,NM_ARTEMIS_SOURCE_SYSTEM
,CD_BOOK_KEY
,ACTIVITY_DT)

and

LOAD_TRADE_ORDER
(IND_IS_BAD_RECORD
,ARTEMIS_SOURCE_SYSTEM_ID
,NM_ARTEMIS_SOURCE_SYSTEM
,CD_BOOK_KEY
,ACTIVITY_DT)
Re: Delete statement is taking more time for execution [message #448203 is a reply to message #446619] Sat, 20 March 2010 09:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
There are basically two things that take time in a delete such as this:

1) time needed to find the rows to delete
2) time needed to delete them

Thus your first step should be to find out how long it takes to find the rows you want.

select count(<some unindexed column>)
FROM TRADE_ORDER_EMP_ALLOCATION T
WHERE (ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT)
IN (SELECT ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT
FROM LOAD_TRADE_ORDER
WHERE IND_IS_BAD_RECORD='N');


This select will give you a pretty good idea of how long it takes to find the data. Make sure you clear you cache before you run the query, otherwise you are not taking into account physical I/O which you need to account for in a delete since you won't be querying the data more than once in real life (you are after all deleting it).

Once you have this, you know where to start your search for performance improvements. You will need to either tune #1, or #2 (or both). How to tune #2 is much harder because it involves things other than your typical sql tuning.

Kevin
Re: Delete statement is taking more time for execution [message #467191 is a reply to message #448203] Thu, 22 July 2010 10:15 Go to previous message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
also see if there are foreign keys on table then make sure that index exisis on that , otherwise before deleting the record it search for child record and if index is there , searching would be fast.
Previous Topic: reduce the elapsed time
Next Topic: index on foreign keys
Goto Forum:
  


Current Time: Mon Sep 26 09:23:29 CDT 2016

Total time taken to generate the page: 0.06869 seconds