OraFAQ Forum: Performance Tuning » delete no of records

Home » RDBMS Server » Performance Tuning » delete no of records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

delete no of records [message #469161]

Tue, 03 August 2010 10:25

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

I am using one script to delete the records from a table, its taking 1hr to delete.

declare
cursor c1 is select ownerid,ownertype from nightly_metric_projects
;
v1 c1%rowtype;
open c1;
loop
fetch c1 into v1;
exit when c1%notfound;
DELETE FROM DGT_ITEMEFFORTDATA WHERE OWNERTYPE = c1.OWNERTYPE
AND OWNERID = c1.OWNERID;
end loop;
close c1;
commit;

nightly_metric_projects--1200 records
DGT_ITEMEFFORTDATA--13200000

CAN WE REDUCE THE TIME.

Report message to a moderator

Re: delete no of records [message #469162 is a reply to message #469161]

Tue, 03 August 2010 10:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

no PL/SQL only SQL

DELETE FROM DGT_ITEMEFFORTDATA WHERE (OWNERTYPE, OWNERID) in ( select ownerid,ownertype from nightly_metric_projects);

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: delete no of records [message #469165 is a reply to message #469162]

Tue, 03 August 2010 10:35

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

Basic rule - if you can do it in a single sql statement it's almost always significantly faster than using PL/SQL

Report message to a moderator

Re: delete no of records [message #469166 is a reply to message #469162]

Tue, 03 August 2010 10:36

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

how much benifit i can expect .

Report message to a moderator

Re: delete no of records [message #469167 is a reply to message #469165]

Tue, 03 August 2010 10:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

both OWNERTYPE, OWNERID should be indexed

Report message to a moderator

Re: delete no of records [message #469168 is a reply to message #469167]

Tue, 03 August 2010 10:38

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

No idea, suggest you test it.

Report message to a moderator

Re: delete no of records [message #469169 is a reply to message #469167]

Tue, 03 August 2010 10:54

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

OK... BUT IT WILL DELETE 3542103 RECORDS. SO IS THERE ANY OTHER WAY TO IMPROVE.

Report message to a moderator

Re: delete no of records [message #469170 is a reply to message #469169]

Tue, 03 August 2010 10:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>OK... BUT IT WILL DELETE 3542103 RECORDS. SO IS THERE ANY OTHER WAY TO IMPROVE.
you can always ROLLBACK.

post EXPLAIN PLAN

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: delete no of records [message #469171 is a reply to message #469169]

Tue, 03 August 2010 10:56

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use DBMS_PARALLEL_EXECUTE.

Regards
Michel

Report message to a moderator

Re: delete no of records [message #469172 is a reply to message #469171]

Tue, 03 August 2010 11:00

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

Before messing about with advanced features why don't you test it and see how long it takes now.

Report message to a moderator

Re: delete no of records [message #469183 is a reply to message #469171]

Tue, 03 August 2010 12:01

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

how can i use DBMS_PARALLEL_EXECUTE to delete those records.Coudl you pl explain.

Report message to a moderator

Re: delete no of records [message #469185 is a reply to message #469183]

Tue, 03 August 2010 12:45

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please could you refer to the documentation and come back if there is something you don't understand or can't achieve?

Regards
Michel

Report message to a moderator

Re: delete no of records [message #469217 is a reply to message #469185]

Tue, 03 August 2010 23:41

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

And you might want to go for "alter session nologging" for table before delete

Report message to a moderator

Re: delete no of records [message #469222 is a reply to message #469168]

Tue, 03 August 2010 23:58

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

This is the plan

--------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | DELETE STATEMENT     |                            |  9276K|   433M| 2795
|   1 |  DELETE              | DGT_ITEMEFFORTDATA         |       |       |
|   2 |   NESTED LOOPS       |                            |  9276K|   433M| 2795
|   3 |    SORT UNIQUE       |                            |  1296 | 11664 |
|   4 |     TABLE ACCESS FULL| NIGHTLY_METRIC_PROJECTS    |  1296 | 11664 |
|*  5 |    INDEX RANGE SCAN  | IDX_DGT_ITEMEFFORTDATA_OWN |  7158 |   279K|    4
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("OWNERTYPE"="OWNERTYPE" AND "OWNERID"="OWNERID")
Note
-----

Report message to a moderator

Re: delete no of records [message #469223 is a reply to message #469222]

Wed, 04 August 2010 00:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It is about as good as can be expected.

Report message to a moderator

Previous Topic:	Query doesn't use Materialized View
Next Topic:	Difference between cost and bytes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 04 12:13:41 CDT 2024