Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
need suggestion in deletion for five tables at a time

577162
Member Posts: 358
Hi,
I need some suggestion regarding a deletion and i have the following scenario.
tab1 contains 100 items.
for one item tab2..6 tables contain 4000 rows.So the loop will run for each item and will delete 20,000 lines and will do a commit.
Currently for 5,00,000 deletion it is taking 1 hr.All the tables and indexes are analysied.
CURSOR C_CHECK_DELETE_IND
IS
SELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y';
type p_item IS TABLE OF tab1.item%type;
act_p_item p_item;
BEGIN
OPEN C_CHECK_DELETE_IND;
LOOP
FETCH C_CHECK_DELETE_IND bulk collect INTO act_p_item limit 5000;
FOR i IN 1..act_p_item.count
LOOP
DELETE FROM tab2 WHERE item = act_p_item(i);
DELETE FROM tab3 WHERE item = act_p_item(i);
DELETE FROM tab4 WHERE item = act_p_item(i);
DELETE FROM tab5 WHERE item = act_p_item(i);
DELETE FROM tab6 WHERE item = act_p_item(i);
COMMIT;
END IF;
END LOOP;
exit when C_CHECK_DELETE_IND%notfound;
END LOOP;
Hope i have explained the scenario.Can you please suggest me the right approach.
Thanks in advance.
I need some suggestion regarding a deletion and i have the following scenario.
tab1 contains 100 items.
for one item tab2..6 tables contain 4000 rows.So the loop will run for each item and will delete 20,000 lines and will do a commit.
Currently for 5,00,000 deletion it is taking 1 hr.All the tables and indexes are analysied.
CURSOR C_CHECK_DELETE_IND
IS
SELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y';
type p_item IS TABLE OF tab1.item%type;
act_p_item p_item;
BEGIN
OPEN C_CHECK_DELETE_IND;
LOOP
FETCH C_CHECK_DELETE_IND bulk collect INTO act_p_item limit 5000;
FOR i IN 1..act_p_item.count
LOOP
DELETE FROM tab2 WHERE item = act_p_item(i);
DELETE FROM tab3 WHERE item = act_p_item(i);
DELETE FROM tab4 WHERE item = act_p_item(i);
DELETE FROM tab5 WHERE item = act_p_item(i);
DELETE FROM tab6 WHERE item = act_p_item(i);
COMMIT;
END IF;
END LOOP;
exit when C_CHECK_DELETE_IND%notfound;
END LOOP;
Hope i have explained the scenario.Can you please suggest me the right approach.
Thanks in advance.
Answers
-
Hi All,
Can anyone please suggest me on the same .I want suggestion regarding the best approach in this situation. -
drop the loop
DELETE FROM tab2 WHERE item in (SELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y' ); DELETE FROM tab3 WHERE item in (SELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y' ); DELETE FROM tab4 WHERE item in (SELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y' ); DELETE FROM tab5 WHERE item in (SELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y' ); DELETE FROM tab6 WHERE item in (SELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y' );
if you can accomplish the task without looping, by all means do so!
you could also do a bulk delete.forall j in indices of act_p_item save exceptions DELETE tab2 WHERE item = act_p_item(j);
etc for tab3 to 6
but unless the querySELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y'
is horrible, I'd stick to theDELETE FROM tab2 WHERE item in (SELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y' );
path
Edited by: tanging on Dec 21, 2009 10:19 AM -
I assume the following: 1) tabs 2..6 have a foreign key referencing tab1 with "on delete cascade" option;
2) tabs 2..6 have index defined on the top of the foreign key field;
3) if assumption (1) and (2) are valid or you can make them valid then you can execute just two statements:
delete from tab1 where delete_item_ind = 'Y';
commit;
foreign key with on delete cascade option will take care of child records when parent record is being deleted.
Edited by: Dmitriy S on Oct 3, 2011 7:25 PM
This discussion has been closed.