| Delete statement is taking more time for execution [message #446619] |
Tue, 09 March 2010 11:08  |
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 #446736 is a reply to message #446619] |
Wed, 10 March 2010 03:48  |
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 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
