[prev in list] [next in list] [prev in thread] [next in thread] 

List:       odtug-sqlplus-l
Subject:    SV: How to delete from multiple tables simultaneously
From:       Kim Berg Hansen <kbh () thansen ! dk>
Date:       2011-11-17 9:18:03
Message-ID: E05F40FF844ABB4CB801F7E9F678379B1B5D05A5 () EXCHDB02SVR ! thansen ! dk
[Download RAW message or body]

If there had been a multitable delete (DELETE ALL) just as there is a multitable \
insert (INSERT ALL), that could have been the way. But alas, there is (AFAIK) no such \
thing :-) Your bulk operations could perhaps be speeded up by something like:

	Delete from table1 where key in (select key from keytable);
	Delete from table2 where key in (select key from keytable);
	Delete from table3 where key in (select key from keytable);
	.....
	Delete from keytable;

But if records can arrive in your keytable while you are running the process, you may \
have to beware of how you start your transaction.

An alternative could be to use a global temporary table instead of your PL/SQL table.
Create a GTT with ON COMMIT DELETE ROWS to hold values from the keytable. Then do \
something like:

	Insert into gtt select key from keytable;
	Delete from table1 where key in (select key from gtt);
	Delete from table2 where key in (select key from gtt);
	Delete from table3 where key in (select key from gtt);
	.....
	Delete from keytable where key in (select key from gtt);
	Commit; -- Here the rows in the GTT dissappears

A GTT solution would emulate reasonably closely your present logic, but quite \
possibly be faster than the PL/SQL bulk operations.


Med venlig hilsen / Best regards
 
T. Hansen Gruppen A/S
 
 
Kim Berg Hansen
Chef-udvikler / Senior System Developer
 
web: www.thansen.dk
e-mail: kbh@thansen.dk
 
Tlf: +45 63 41 69 00
Fax: +45 63 41 69 90

-----Oprindelig meddelelse-----
Fra: odtug-sqlplus-l-bounce@fatcity.com [mailto:odtug-sqlplus-l-bounce@fatcity.com] \
                På vegne af Amin Adatia
Sendt: 17. november 2011 02:34
Til: ODTUG-SQLPLUS
Emne: How to delete from multiple tables simultaneously

Is there a way to delete from multiple tables (44) simultaneously using another table \
which has the key data for the tables? At present I read the key table (BULK COLLECT) \
and then do FORALL … in sequence for all the tables  .. and then delete the records \
of the processed data in the Table of key data.  

 

Regards

Amin Adatia (amin @ knowtech.ca)

KnowTech Solutions Inc. (www.knowtech.ca)

Mobile: +1-613-864-8378

 


[prev in list] [next in list] [prev in thread] [next in thread]