Home » SQL & PL/SQL » SQL & PL/SQL » Historical Data Delete Query (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit)
Historical Data Delete Query [message #612165] Sun, 13 April 2014 23:24 Go to next message
ArjunK
Messages: 1
Registered: April 2014
Junior Member
Hi Friends,

I am using the below query to delete the historical data based on the load_id and last_mod_date. but it is taking long time to complete. The 'sales' table has around 24 Million data.

Please suggest or provide any inputs to improve the performance of this query.


-------------------------------------------------------------------------------------------------------------------
DELETE FROM SALES SAL

WHERE (SAL.SALES_ROW_ID) IN (SELECT DISTINCT SALES_ROW_ID

                               FROM SALES

                               WHERE  LAST_MOD_DATE < (SYSDATE - 7)

                               )

                               AND SAL.LAST_MOD_DATE < (SYSDATE - 7)

                               AND SAL.LOAD_ID NOT IN (

                 SELECT MAX(SAL1.LOAD_ID)

                               FROM SALES SAL1

                 WHERE SAL1.SALES_ROW_ID = SAL.SALES_ROW_ID

                 GROUP BY SAL1.SALES_ROW_ID

                 )

-----------------------------------------------------------------------------------------------------------



Thanks ,
Arjun
*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Sun, 13 April 2014 23:30] by Moderator

Report message to a moderator

Re: Historical Data Delete Query [message #612166 is a reply to message #612165] Sun, 13 April 2014 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 23160
Registered: January 2009
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/#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: Historical Data Delete Query [message #612168 is a reply to message #612166] Mon, 14 April 2014 00:12 Go to previous message
Littlefoot
Messages: 19893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you might try with removing DISTINCT as it does nothing smart here. You don't really care whether that SELECT returns unique set of SALES_ROW_ID or not (and, if it does, it has to filter out all duplicates which - in a data set of 24 million rows - might take some precious time).
Previous Topic: how to overcome mutating table error
Next Topic: Distinct & Group By when used Analytic Function
Goto Forum:
  


Current Time: Mon Dec 22 08:58:40 CST 2014

Total time taken to generate the page: 0.08623 seconds