Home » SQL & PL/SQL » SQL & PL/SQL » Deleting records from a table which is having 60 million records (Orace 10.2.0.3, Linux)
Deleting records from a table which is having 60 million records [message #402215] Fri, 08 May 2009 02:10 Go to next message
saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad
Junior Member
Hi All,

I have a table called f_draft which is having 60 million records, and i need to delete records from this table before deleting i am doing a validation with master table named item. It is taking 40 min.

Can anyone help me how to reduce the run time of this query.

Even i tried with using hint PARALLEL as below


DELETE /*+ PARALLEL (fd) */ FROM f_draft fd
WHERE EXISTS (
SELECT 1
FROM item i
WHERE TRUNC (fd.startdate) < TRUNC (i.postdate)
AND i.item = fd.item);


thanks in advance
Re: Deleting records from a table which is having 60 million records [message #402230 is a reply to message #402215] Fri, 08 May 2009 02:35 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
We need a little bit more info:
1. size of the master table (number of records)
2. keys?
3. indexes?
4. explain plan?

MHE
Re: Deleting records from a table which is having 60 million records [message #402234 is a reply to message #402215] Fri, 08 May 2009 02:56 Go to previous message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
Possible things you can do is :

1. Gather statistics on both tables.
2. Create a function based index trunc(date) in the item table.
Previous Topic: To get four top most records from a table
Next Topic: Reserved words Location
Goto Forum:
  


Current Time: Tue Dec 06 02:10:20 CST 2016

Total time taken to generate the page: 0.19471 seconds