Home » SQL & PL/SQL » SQL & PL/SQL » Performance issue during delete
Performance issue during delete [message #238366] Thu, 17 May 2007 11:49 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
i want to delete rows from transaction detail tables. Only those rows should be deleted
where in transaction master the data is beyond 01-Apr-2005 for a certain location.

Select * from Trans_Detail td
WHERE EXISTS (
with subq as
(select loc_id , transaction_id from trans_master where loc_id in (SELECT loc_id
FROM loc_master WHERE loc_name = 'AUS')
and sys_date < '01-Apr-2005')
select 1
from subq
where td.loc_ID = subq.loc_id
AND td.TRANSACTION_ID = subq.TRANSACTION_ID)

So basically i query the trans_master first for a particular loaction and date range. I get a few rows.
THose rows should be deleted from the trans detail table. Can i use delete command in subquery factoring.

I am trying to rewrite the old query so it takes the least amonut of time. But this query below too is taking around 3 hrs. there are approx 100,000 rows.


delete from Trans_Detail td
WHERE EXISTS (
with subq as
(select loc_id , transaction_id from trans_master where loc_id in (SELECT loc_id
FROM loc_master WHERE loc_name = 'AUS')
and sys_date < '01-Apr-2005')
select 1
from subq
where td.loc_ID = subq.loc_id
AND td.TRANSACTION_ID = subq.TRANSACTION_ID)
Re: Performance issue during delete [message #238367 is a reply to message #238366] Thu, 17 May 2007 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
1) Please read & FOLLOW #1 Sticky post at top of forum
2) please post table descriptions & indexes
3) please post EXPLAIN_PLAN
Re: Performance issue during delete [message #238370 is a reply to message #238366] Thu, 17 May 2007 12:35 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
bella13 wrote on Thu, 17 May 2007 12:49
i want to delete rows from transaction detail tables. Only those rows should be deleted
where in transaction master the data is beyond 01-Apr-2005 for a certain location.

and sys_date < '01-Apr-2005'



Please do not compare DATE columns to character strings. You have to use a TO_DATE function.
Re: Performance issue during delete [message #238373 is a reply to message #238370] Thu, 17 May 2007 13:01 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
sys_date is a date column...
Re: Performance issue during delete [message #238374 is a reply to message #238366] Thu, 17 May 2007 13:04 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>sys_date is a date column...
SO what? '01-Apr-2005' is a STRING; not a DATE!

1) Please read & FOLLOW #1 Sticky post at top of forum
2) please post table descriptions & indexes
3) please post EXPLAIN_PLAN

[Updated on: Thu, 17 May 2007 13:06] by Moderator

Report message to a moderator

Previous Topic: Local Partitioned Index Status
Next Topic: Data Encryption
Goto Forum:
  


Current Time: Sat Dec 10 10:59:57 CST 2016

Total time taken to generate the page: 0.06615 seconds