Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

deleting rows takes more time

Posted on 2010-11-23
6
Medium Priority
?
504 Views
Last Modified: 2013-12-07
Hi,

I have 3 tables A,B,C.......each of them has more then 100000 rows, n I would like to delete all the rows from three tables but it is taking too much of time.

Table A has triggers like, if anything deletes from A, it will add it to B and
second trigger like whatever is deleted from A, those records will be deleted from B also.

How can I solve this problem?
0
Comment
Question by:sridhar_ravva
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34200189
could you disable the triggers?

or,  truncate each table, that doesn't fire triggers
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 34200608
if you want to delete ALL records, best way is to use TRUNCATE.

Downside of using TRUNCATE is that you might have to disable constraints this table is being referred
0
 

Author Comment

by:sridhar_ravva
ID: 34201213
sorry, I missed something:
"second trigger like whatever is deleted from A, those records will be deleted from B also."

is wrong
actually table C has many other data also. I just need to delete all the data from table A and B but delete only those records from C which are deleted from A.
Table C has other data too, other than just table A.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 34201430
truncate A,  truncate B,

delete C where not exists (select null from A where a.col1 = c.col1 and a.col2 = b.col2)


expand to the appropriate columns and add NULL checks if necessary
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 34421478
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question