Solved

deleting rows takes more time

Posted on 2010-11-23
6
501 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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 70

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

752 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