Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure (oracle 9i)
stored procedure [message #354331] Fri, 17 October 2008 14:48 Go to next message
rajkraj
Messages: 1
Registered: October 2008
Junior Member
Hi,

We have some huge data(100 million) in a oracle table Table1 which has key1,key2,key3,key4 as key columns and
every week we get around 30 million rows in a file,which we load into a temporary table TempTbl2 .Now we have to create a stored procedure which searches for these 30 million rows (which are in TempTb2) in table Table1 and deletes them from Table1.

Can some one guide me,how to write a stored procedure for this.

Thanks in Advance.
Re: stored procedure [message #354348 is a reply to message #354331] Fri, 17 October 2008 23:40 Go to previous messageGo to next message
anasjamil
Messages: 28
Registered: April 2008
Location: KARACHI
Junior Member

hi,
i have not tried these steps but hope that these will work

for retreiving all data to delete use two select queries and
between them a minus.

store all that data (to be deleted) in another table


apply a delete query between the newly created table and the old one.


regards,
anas jamil
Re: stored procedure [message #354350 is a reply to message #354331] Sat, 18 October 2008 00:00 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
Try to Provide samples. It will be better for the people to give you suggestion.
icon7.gif  Re: stored procedure [message #354351 is a reply to message #354350] Sat, 18 October 2008 00:07 Go to previous messageGo to next message
anasjamil
Messages: 28
Registered: April 2008
Location: KARACHI
Junior Member

samples? sorry i did not get your point.how ever i have tried the above procedure and it worked.

regards,
anas jamil
Re: stored procedure [message #354355 is a reply to message #354331] Sat, 18 October 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delete table1 where ... in (select ... from table2)

Regards
Michel
Re: stored procedure [message #354418 is a reply to message #354331] Sat, 18 October 2008 13:15 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Michel's code has a small flaw:

Instead of this:

delete table1 where ... in (select ... from table2)

It is a two part operation. It should be this:

delete table1 where ... in (select ... from table2)
Go to lunch.  A very loooong lunch.

Kevin
Re: stored procedure [message #354429 is a reply to message #354331] Sat, 18 October 2008 21:40 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
First, let me say that I am not a Data Warehouse guru. However, I'm going to take a stab at this, and I do hope someone who does have experience with DWs will point out where I am wrong.

First of all, it seems that what we are trying to do is delete 30% of a very large table. And it appears that the rows we need to remove are scattered all over the table, so we can't just drop a partition and be done with it. So here is my plan:

1) Take down the application.

2) Drop all indexes except for the bitmapped indexes on the keys which you use (you _do_ have bitmapped indexes on those, don't you?)

3) Load your temp table (nologging) with just the key values (why load data fields whose sole purpose in life is to get deleted?) These keys should have bitmapped indexes also.

4) Insert into a 2nd table using a NOT IN operator. Make sure that logging is turned off for this 2nd table. You want to do an anti-join, and you want to exploit those bitmapped indexes. If I understand this correctly, Oracle will do the anti-join by performing bit operations on the indexes, and then, once it has a set of rowids for the desired rows, it will fetch them doing just one table scan on the original table.

5) Rename the original table to something else.

6) Rename the 2nd table to the original table.

7) Build the missing indexes.

Cool Drop the renamed original table.

9) Except that somewhere between (5) and (Cool you need to take a backup. Exactly where the best place to do it depends on your precise backup strategy.
Re: stored procedure [message #354460 is a reply to message #354429] Sun, 19 October 2008 07:01 Go to previous messageGo to next message
phani1980@gmail.com
Messages: 3
Registered: October 2008
Junior Member
When you have to handle millions of Records for SELECT from tables or DML (INSERT or UPDATE or DELETE) then it is always better to use the FORALL and BULKCOLLECT.

For FORALL sample Example, go through the link given below.

http://alloracletech.blogspot.com/2008/10/forall.html

For the BULK COLLECT sample example, please go through the following link.

http://alloracletech.blogspot.com/2008/10/bulk-collect.html

Re: stored procedure [message #354464 is a reply to message #354460] Sun, 19 October 2008 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Useless blog pages that explain nothing and have examples in a not formatted and not readable format.

Regards
Michel
Re: stored procedure [message #354513 is a reply to message #354460] Mon, 20 October 2008 02:20 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
When you have to handle millions of Records for SELECT from tables or DML (INSERT or UPDATE or DELETE) then it is always better to use the FORALL and BULKCOLLECT.


Actualy, it is almost always better to use straightforwards SQL rather than Pl/Sql
Previous Topic: Remote data writing with Stored Procedure
Next Topic: convert secconds to day:hour:minute:seconds format
Goto Forum:
  


Current Time: Tue Dec 06 04:34:09 CST 2016

Total time taken to generate the page: 0.05639 seconds