Question

deleting rows from oracle table using ROWIDS

Asked by: mallik_123

Hi

I am using oracle Database version 8.1  and  I  have a  table with 7 million rows.
I am planning to delete only 0.7 million rows using rowid.

i am using the following procedure




it is taking 1 hour in development data base to delete 7 million records.

but in production it is taking 1 hour delete 0.7 million records .

I have a couple of questions like

is it the correct way to delete rows using rowid?

if so,  why it is taking such a long time in production?
please clarify

Thanks
Mallik

********************************************
 
create or replace procedure crpi_inv_purge as 
 
v_count number ;
 
cursor c1 is select rowid from crpi.CRPI_INVENTORY_HIST
WHERE inventory_date < (to_date('06-30-2009','MM-DD-YYYY') - 60);
 
 
begin
 
v_count := 0;
 
 
for i in c1
loop
 
delete from crpi.CRPI_INVENTORY_HIST where rowid = i.rowid;
 
v_count := v_count+1;
 
if v_count=1000 then
 
commit;
 
v_count := 0;
 
end if;
 
end loop;
 
commit;
 
exception 
 
when others then 
 
dbms_output.put_line ( 'Exception in deleting rows    :'||sqlerrm);
 
end;
/
 
 
********************************************

                                  
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:

Select allOpen in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-10-03 at 08:07:35ID24782483
Topics

Oracle 8.x

,

Databases Miscellaneous

,

PL / SQL

,

Oracle Database

Participating Experts
6
Points
0
Comments
28

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Rowid
    The oracle 8i rowid,is it unique identifier for a row in a database or with in a table?
  2. bitmap conversion to rowid
    When Oracle join a b-tree and a bitmap index and bitmap conversion is occured. there is from_rowid to join bitmaps, and to_rowid to get the data from the table. how does this from and to rowid going on? how much does it cost? (building bitmap from rowid sounds costly) do you ...
  3. Oracle ROWID
    Hi all experts, I Want to get the ROWID for a row that is Newly inserted. Suppose we have fired an Insert Query Insert into TableName value(Col Values); Now I want to get the ROWID for this Row is there any way to do so ? Thanx in advance Akshay
  4. Query based on rowid in Oracle
    Hi, I have table with 3 million records. I am using ado with vb to get the data from the oracle database. I wish to limit the records returned by the query to 200 so that it takes less time for the initial query. When the user scolls down the I will get the other records. Can...
  5. rowid changed
    I access our Oracle Database through TOAD, and as far as I know nothing was changed on the Oracle Server...but the rowid is no longer displayed numerically it now returns a value such as AAABerAAMAALrZyAAA. What's up with that...how can I get the numerical value to display i...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: billprewPosted on 2009-10-03 at 08:29:59ID: 25485689

Why aren't you just doing the following inside the commit loop, why are you using rowids?

delete from crpi.CRPI_INVENTORY_HIST
WHERE inventory_date < (to_date('06-30-2009','MM-DD-YYYY') - 60)
and rownum < 1000;

~bp

 

by: mallik_123Posted on 2009-10-03 at 09:35:44ID: 25485899

Hi

infact  the below code is initial version of the code and it is taking 1 hour to delete
0.7 million records.

delete from crpi.CRPI_INVENTORY_HIST
WHERE inventory_date < (to_date('06-30-2009','MM-DD-YYYY') - 60)
and rownum < 1000;

*******************

to tune the statement further  i have incorporated  ROWIDS.

hope you have got better idea of the problem

Thanks
Mallik

 

by: mrjoltcolaPosted on 2009-10-03 at 10:21:03ID: 25486020

>>to tune the statement further  i have incorporated  ROWIDS.


This has not tuned the statement. It has turned one SQL execution into 700,000 SQL executions because each "delete where rowid = ..." is an individual execution.

You should revert back to the original logic.

 

by: billprewPosted on 2009-10-03 at 18:53:26ID: 25487832

It's probably not worth a lot of time, but every little bit helps, I'd recommend doing the "to_date('06-30-2009','MM-DD-YYYY') - 60" one time before the DELETE statement and assign that value to a variable that can be referenced in the query.  Doubt it will save much though.

Clearly there is a difference between what you are doing in DEV versus PROD, or the hardware or database are set up differently.  Is the hardware different, or the load on the systems different?  Could there be more indexes of cascaded deletes in PROD?  

~bp

 

by: mallik_123Posted on 2009-10-04 at 04:02:25ID: 25488805

Hi

infact  the below code is initial version of the code and it is taking 1 hour to delete
0.7 million records.

delete from crpi.CRPI_INVENTORY_HIST
WHERE inventory_date < (to_date('06-30-2009','MM-DD-YYYY') - 60)
and rownum < 1000;

*******************

to tune the statement further  i have incorporated  ROWIDS.

if deleting records based on ROWIDS is not a good approach
but I have read somewhere that we can delete using ROWIDs along with Primary Key Column.
we dont have a PK column  but we have unique index on three columns of the table.

as we have seen in one of the above comments  that using ROWIDS
makes 700,000 sequel executions  then
in this scenario  what is the best way to delete from the Oracle Table.

for example  the below code   is taking 1 hour to delete 700,000  records.

delete from crpi.CRPI_INVENTORY_HIST
WHERE inventory_date < (SYSDATE - 60);

what  are the best alternatives we have,  to delete 700,000  from the above table
in less than 1 hour.  I want to delete faster than the approach we have that is

delete from crpi.CRPI_INVENTORY_HIST
WHERE inventory_date < (SYSDATE - 60);



your help is highly appreciated.

Regards
Mallik





 

by: billprewPosted on 2009-10-04 at 07:26:46ID: 25489281

How many records will be left in the table after the deletes?  It might make more sense to copy them off, then truncate the table and readd them.

~bp

 

by: mallik_123Posted on 2009-10-04 at 07:44:13ID: 25489332

Hi

there will be about 7 to 10 million total records will be there in that table.
out of that we are planning  to delete 700,000 records  every week ie 0.7 million records.

this is not a one time job . we are scheduling the purge program with the code  I told
in my previous mails .  it will run every week.
since this is a weekly program, we can not copy into a different table
and then truncate the old table  and insert into the old table  or rename the new table.

i got to improve the performance with new and efficient code.

Mallik

 

by: billprewPosted on 2009-10-04 at 08:45:36ID: 25489525

Have you compared the schemas between DEV and PROD?

What is the hardware difference?

Are there any Oracle init differences or other settings?

~bp

 

by: franckpachotPosted on 2009-10-04 at 11:49:20ID: 25490181

Hi,
Your procedure is faster than the delete statement not because of the rowid usage, but because it allows you to do intermediate commits without re-reading the rows. If the delete statement does a full scan, than running it several times will read several times the same blocks.
If you have an index on inventory_date, then the delete statement should be faster.
You can improve your procedure by using bulk operations:
 - do your select of rowsid with fecth ... bulk collect into ... limit 1000
 - do a forall ... delete where rowid=...
Regards,
Franck.

 

by: mallik_123Posted on 2009-10-12 at 16:02:07ID: 25555806

Hi

I have tested the above solution but still it is very slow.
any idea what has happened?

Thanks in advance.
Mallik

 

by: HenkaPosted on 2009-10-13 at 23:07:02ID: 25567688

I am surprised that you are afraid that deleting will take at about 1 hour. You have written:
"this is not a one time job . we are scheduling the purge program with the code  I told
in my previous mails .  it will run every week."
so if job run e.g. on Sunday night I think that there will be no problem with its duration.

I see that you commit when 1000 rows are deleted. Maybe you can increase this number.

Also bulk operations can help as Franck has written, but I am not sure that all these operations are in 8i.

 

by: slightwvPosted on 2009-10-14 at 05:14:45ID: 25569565

What you read about rowid's is correct as long as you understand what it is doing.  The fastest way to access a row is with rowid.  In your case, you're duplicating the query to get the rowids so I really don't see a benefit.

what is the explain plan for the delete statement in production?

what OS?

you might be able to use the parallel hint.

 

by: mallik_123Posted on 2009-10-14 at 20:15:06ID: 25576956

Hi  slightwv:

the explain plan in production is as below for the delete statement

this delete statement is in a PL/SQL block and execute as many rows as the select retrieves.

delete from crpi.CRPI_INVENTORY_HIST where rowid = 'AADHoXACIAAAMzUAAA123%%'

Explain Plan

ID   ---   PARENT_ID  ----  QUERY PLAN   (these are columns)


ID      PARENT_ID      Query Plan
0            DELETE STATEMENT   Cost = 1
1      0        DELETE  CRPI_INVENTORY_HIST
2      1          TABLE ACCESS BY USER ROWID CRPI_INVENTORY_HIST

my OS is as below

SunOS yosemite 5.8 Generic_117350-43 sun4u sparc SUNW,Netra-T12

you want me to use  PARALLEL hint on delete statement?

please let me know how i can tune it better

Regards
Mallik



 

by: mallik_123Posted on 2009-10-14 at 23:32:36ID: 25577781

Hi

I can not use parallel hint on a delete statement as the main table is not partioned.

any idea??

thx
Mallik

 

by: slightwvPosted on 2009-10-15 at 05:10:45ID: 25579608

I was really wanting the explain plan for:
delete from crpi.CRPI_INVENTORY_HIST
WHERE inventory_date < (SYSDATE - 60);

Can't say I ever tried a parallel hint on a delete.  Guess I need to play around before I post.  I'll read up while I wait for the explain plan.

 

by: mallik_123Posted on 2009-10-16 at 00:21:10ID: 25587507

Hi

This is the plan
OPERATION                     OPTIONS             OBJECT_NAME                          POSITION

DELETE STATEMENT                                                                    5,087
                        
DELETE                                            CRPI_INVENTORY_HIST             1

TABLE ACCESS             BY INDEX
                                   ROWID                  CRPI_INVENTORY_HIST       1

INDEX                           RANGE SCAN           CRPI_INV_HIST_I            1


let me know if u need any more detais

Thx
Mallik

 

by: slightwvPosted on 2009-10-16 at 05:26:30ID: 25588820

The plan looks good to me.  Not sure how you can improve on it.

How long has it been since the index CRPI_INV_HIST_I has been rebuilt?  This might be the difference between test and production times.  The production index might be fragmented or skewed.

Also, I finally got a chance to test the parallel hint and it seems to work on 11g.  I don't have any 8.x databases left around to test it on so, I'll post what I ran on 11.1.0.7.

The first cost was 49.  With the hint the cost was 14.

drop table tab1;
create table tab1 (col1 char(1));
 
insert into tab1 values('a');
insert into tab1 values('b');
insert into tab1 values('c');
insert into tab1 values('e');
insert into tab1 values('f');
insert into tab1 values('g');
commit;
 
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
insert into tab1 (select * from tab1);
commit;
 
delete from plan_table;
commit;
 
explain plan for
delete from tab1 where col1='b';
 
SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options||' '||optimizer
   ||' '||object_name
   ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
   FROM plan_table
   START WITH id = 0
   CONNECT BY PRIOR id = parent_id
/
 
 
 
delete from plan_table;
commit;
 
explain plan for
delete /*+ parallel(tab1,4) */ from tab1 where col1='b';
 
SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options||' '||optimizer
   ||' '||object_name
   ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
   FROM plan_table
   START WITH id = 0
   CONNECT BY PRIOR id = parent_id
/

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:

Select allOpen in new window

 

by: mallik_123Posted on 2009-10-16 at 10:06:33ID: 25591295

Hi

I have   tested the delete statement  with parallel hint and with out parallel hint.

the details are as below  .  the cist is same. am i missing any set up parameter?

please let me know

EXPLAIN PLAN
    SET STATEMENT_ID = 'JAPAN2'
    INTO plan_table
    FOR  DELETE    FROM crpi.CRPI_INVENTORY_HIST
             WHERE inventory_date < (SYSDATE - 60);

      
      SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, position
    FROM plan_table
    START WITH id = 0 AND statement_id = 'JAPAN2'
    CONNECT BY PRIOR id = parent_id AND
    statement_id = 'JAPAN2';
      
      
      CREATE VIEW test18 AS
SELECT id, parent_id,
lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
       decode(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH id = 0 and statement_id = 'JAPAN2'
CONNECT BY prior id = parent_id and statement_id = 'JAPAN2'

select * from test18

ID      PARENT_ID      Query Plan

0                           DELETE STATEMENT   Cost = 5087
1          0                     DELETE  CRPI_INVENTORY_HIST
2          1                     TABLE ACCESS BY INDEX ROWID CRPI_INVENTORY_HIST
3          2                      INDEX RANGE SCAN CRPI_INV_HIST_I


***********************************************************************

EXPLAIN PLAN
    SET STATEMENT_ID = 'JAPAN1'
    INTO plan_table
    FOR  DELETE /*+ parallel(crpi.crpi_inventory_hist,4) */    FROM crpi.CRPI_INVENTORY_HIST
             WHERE inventory_date < (SYSDATE - 60);

      
      SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, position
    FROM plan_table
    START WITH id = 0 AND statement_id = 'JAPAN1'
    CONNECT BY PRIOR id = parent_id AND
    statement_id = 'JAPAN1';
      
      
      CREATE VIEW test17 AS
SELECT id, parent_id,
lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
       decode(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH id = 0 and statement_id = 'JAPAN1'
CONNECT BY prior id = parent_id and statement_id = 'JAPAN1'

select * from test17

ID      PARENT_ID      Query Plan

0                             DELETE STATEMENT   Cost = 5087
1          0                       DELETE  CRPI_INVENTORY_HIST
2          1                       TABLE ACCESS BY INDEX ROWID CRPI_INVENTORY_HIST
3          2                       INDEX RANGE SCAN CRPI_INV_HIST_I


Thank you so much for ur hekp.

Mallik

 

by: mallik_123Posted on 2009-10-16 at 10:13:30ID: 25591372

Hi

infact to improve the performance I have used the ROWID  to delete in a loop.

which is the better way

1) to delete ;ike below statement

DELETE    FROM crpi.CRPI_INVENTORY_HIST
             WHERE inventory_date < (SYSDATE - 60);

2) i have used rowids of that table in a for loop  and then delete.
code attached.

which approach is better?  

both are taking currently  1 hour for 0.7 million records.

if u suggest one of the above solutions ,  how to improve it further?

i want to delete  0.7 million rows in less than one hour

i have attached my script also for improvement.

thanks a lot

Mallik

bo

 

by: slightwvPosted on 2009-10-16 at 10:31:28ID: 25591532

>>am i missing any set up parameter?

Sorry.  I forgot.  You have an index.  Parallel hint only works on a full table scan.

Try:
EXPLAIN PLAN
    SET STATEMENT_ID = 'JAPAN1'
    INTO plan_table
    FOR  DELETE /*+ full(crpi.crpi_inventory_hist) parallel(crpi.crpi_inventory_hist,4) */    FROM crpi.CRPI_INVENTORY_HIST
             WHERE inventory_date < (SYSDATE - 60);


>>both are taking currently  1 hour for 0.7 million records.

That's pretty much what everyone has been saying.  There really shouldn't be much of a difference since both methods pretty much access the exact same blocks thus should take about the same amount of time.

If the parallel hint doesn't help I'm not sure what other tricks I have other than the index in production being fragmented or skewed.

Would your system be affected by an online rebuild of CRPI_INV_HIST_I?

Since you say test is running faster, how close to production is your test system setup?

 

by: mallik_123Posted on 2009-10-18 at 03:09:23ID: 25599320

Hi

i have used the delete statement sent by you  with Full table scan and parallel hint.

the cost is same 5087
in the explain plan it is using the index again even if u give full table scan and parallel hint.

then i executed the below statement with index suppression by adding NULL to the inventory date

EXPLAIN PLAN
    SET STATEMENT_ID = 'JAPAN5'
    INTO plan_table
    FOR  DELETE /*+ full(crpi.crpi_inventory_hist) parallel(crpi.crpi_inventory_hist,4) */    FROM crpi.CRPI_INVENTORY_HIST
             WHERE inventory_date||null < (SYSDATE - 60);

the cost is very high like 55180.

let me know if u need any more details

Mallik

 

by: mallik_123Posted on 2009-10-18 at 05:34:27ID: 25599617

HI

I have  used the attached script  where in I have taken the Rowid column along with inventory_date
column and  trying call the BULK COLLECT in a For loop  along with a limit of 10000.

it is taking a lot of time to delete 0.8 million rows.

performance is much better  when i am deleting rows based on  ROWID  only.

after using the BULK Collect & FOR ALL  Statements  the performance is worse.

any idea what i am missing

thanks
Mallik

 
CREATE OR REPLACE PROCEDURE      CIM_Purge 
 
--(errbuf       OUT  VARCHAR2,
--                                                retcode      OUT  NUMBER)
AS
 
 
p_purge_days               NUMBER:= fnd_profile.value('XMC_CRPI_INV_PURGE_DAYS');
l_count NUMBER;
profile_value_not_exists   EXCEPTION;
 
v_count number ;
retcode number;
errbuf  varchar2(2000);
 
cursor inv_hist_cur is select rowid,inventory_date  from crpi.CRPI_INVENTORY_HIST
WHERE inventory_date < (SYSDATE - 55);
 
 
        
 
  type t_row is table of rowid index by binary_integer;
  
  row_tab t_row;
  
    
  type t_inv_date is table of date index by binary_integer;
  
  row_inv_date t_inv_date;
  
  type t_str_num is table of varchar2(32767) index by binary_integer;
  
  row_str_num t_str_num;
  
  type t_item_num is table of varchar2(32767) index by binary_integer;
  
  row_item_num  t_item_num;
 
 
	
 
BEGIN
      retcode := 0;
 
	  IF p_purge_days IS NULL THEN
	       RAISE profile_value_not_exists;
	  END IF;
	 
	 -- *******WO27888  Mallik Code  Begins
	  	  
	  v_count := 0;
	  
	  l_count := 0;
	  
    open inv_hist_cur;
    LOOP
	  
	  fetch inv_hist_cur bulk collect into row_tab ,row_inv_date limit 10000;  --,row_str_num,row_item_num limit 10000;
 
	  for i in 1..row_tab.count
	  loop
	  
	  FORALL v_index in row_tab.first..row_tab.last
	  	       
	           delete from crpi.CRPI_INVENTORY_HIST where rowid = row_tab(v_index)
	           					and inventory_date =row_inv_date(v_index);
	           					--and store_number = row_str_num(v_index)
	           					--and item_number  = row_item_num(v_index);
         
	  
	  commit;
	  
	  exit when row_tab.count = 0;
	  
	  end loop;
	  
	  --l_count := l_count+1;
	  
	  exit when inv_hist_cur%notfound;
	  
     end loop; 
 
	  
	/*   SELECT rowid,INVENTORY_DATE,STORE_NUMBER,ITEM_NUMBER
	  BULK COLLECT INTO row_tab ,row_inv_date,row_str_num,row_item_num
	  FROM crpi.CRPI_INVENTORY_HIST
			WHERE inventory_date < (SYSDATE - p_purge_days);  */
	  
	  
	  
	 -- for inv_hist_rec in inv_hist_cur
	 -- loop
	  
	  --delete from crpi.CRPI_INVENTORY_HIST where rowid = inv_hist_rec.rowid;
	  
	/*  v_count := v_count+1;
	  
	    l_count := l_count+1;
	  
	    if v_count=5000 then
	  
	    commit;
	  
	    v_count := 0;
	  
	    end if;   */
	  
	  --end loop;
	  
	 COMMIT;
	 
	 -- *******WO27888   Mallik Code  Ends
 
/*      DELETE FROM CRPI_INVENTORY_HIST
 -- WO27888  WHERE TRUNC(inventory_date) < TRUNC(SYSDATE - p_purge_days);
             WHERE inventory_date < (SYSDATE - p_purge_days);      */ 	  --Commented by Mallik Sonti
      
	  -- l_count := SQL%ROWCOUNT;
		
	  
      xmc_logmsg(SYSDATE,'','No. of records deleted from CRPI_Inventory_Hist table:',row_tab.count);
 
EXCEPTION
      WHEN profile_value_not_exists THEN
         xmc_logmsg(SYSDATE, 'Purge Days', 'Profile Value Not Exists','');
         retcode := 2;
 
      WHEN OTHERS THEN
         xmc_logmsg(SYSDATE,'xmc_crpi_inventory_hist', SQLERRM, '');
         retcode := 2;
         ROLLBACK;
 
END CIM_Purge; 
/
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:

Select allOpen in new window

 

by: mallik_123Posted on 2009-10-18 at 08:15:33ID: 25600118

Hi

i have attached the script which is doing a good job I could complete in 15 minutes for
10 million records

please let me know  if you find any potential issues with this script.

Thanks a lot for all the help

mallik

 
CREATE OR REPLACE PROCEDURE      CIM_Purge 
 
--(errbuf       OUT  VARCHAR2,
--                                                retcode      OUT  NUMBER)
AS
p_purge_days               NUMBER:= fnd_profile.value('XMC_CRPI_INV_PURGE_DAYS');
l_count NUMBER;
profile_value_not_exists   EXCEPTION;
 
v_count number ;
retcode number;
errbuf  varchar2(2000);
 
cursor inv_hist_cur is select rowid,inventory_date,store_number,item_number  from crpi.CRPI_INVENTORY_HIST
WHERE inventory_date < (SYSDATE);
 
 
        
 
  type t_row is table of rowid index by binary_integer;
  
  row_tab t_row;
  
    
  type t_inv_date is table of crpi.CRPI_INVENTORY_HIST.inventory_date%type;
  
  row_inv_date t_inv_date;
  
  type t_str_num is table of crpi.CRPI_INVENTORY_HIST.store_number%type;
  
  row_str_num t_str_num;
  
  type t_item_num is table of crpi.CRPI_INVENTORY_HIST.item_number%type;
  
  row_item_num  t_item_num;
 
 
	
 
BEGIN
      retcode := 0;
 
	  IF p_purge_days IS NULL THEN
	       RAISE profile_value_not_exists;
	  END IF;
	 
	 -- *******WO27888  Mallik Code  Begins
	  	  
	  v_count := 0;
	  
	  l_count := 0;
	  
    open inv_hist_cur;
    LOOP
	  
	  fetch inv_hist_cur bulk collect into row_tab ,row_inv_date,row_str_num,row_item_num LIMIT 2000;  --,row_str_num,row_item_num limit 10000;
 
	
	  FORALL v_index in row_tab.first..row_tab.last
	         delete from crpi.CRPI_INVENTORY_HIST where rowid = row_tab(v_index)
	           					and inventory_date =row_inv_date(v_index)
	           					and store_number = row_str_num(v_index)
	           					and item_number  = row_item_num(v_index);
         
	  
	  commit;
	  
	--  exit when row_tab.count = 0;
	  
	
	  
	  --l_count := l_count+1;
	  
	  exit when inv_hist_cur%notfound;
	  
     end loop; 
 
	  
	/*   SELECT rowid,INVENTORY_DATE,STORE_NUMBER,ITEM_NUMBER
	  BULK COLLECT INTO row_tab ,row_inv_date,row_str_num,row_item_num
	  FROM crpi.CRPI_INVENTORY_HIST
			WHERE inventory_date < (SYSDATE - p_purge_days);  */
	  
	  
	  
	 -- for inv_hist_rec in inv_hist_cur
	 -- loop
	  
	  --delete from crpi.CRPI_INVENTORY_HIST where rowid = inv_hist_rec.rowid;
	  
	/*  v_count := v_count+1;
	  
	    l_count := l_count+1;
	  
	    if v_count=5000 then
	  
	    commit;
	  
	    v_count := 0;
	  
	    end if;   */
	  
	  --end loop;
	  
	 COMMIT;
	 
	 -- *******WO27888   Mallik Code  Ends
 
/*      DELETE FROM CRPI_INVENTORY_HIST
 -- WO27888  WHERE TRUNC(inventory_date) < TRUNC(SYSDATE - p_purge_days);
             WHERE inventory_date < (SYSDATE - p_purge_days);      */ 	  --Commented by Mallik Sonti
      
	  -- l_count := SQL%ROWCOUNT;
		
	  
      xmc_logmsg(SYSDATE,'','No. of records deleted from CRPI_Inventory_Hist table:',row_tab.count);
 
EXCEPTION
      WHEN profile_value_not_exists THEN
         xmc_logmsg(SYSDATE, 'Purge Days', 'Profile Value Not Exists','');
         retcode := 2;
 
      WHEN OTHERS THEN
         xmc_logmsg(SYSDATE,'xmc_crpi_inventory_hist', SQLERRM, '');
         retcode := 2;
         ROLLBACK;
 
END CIM_Purge; 
/
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:

Select allOpen in new window

 

by: ee_autoPosted on 2009-12-05 at 01:22:12ID: 25977973

Question PAQ'd, 500 points refunded, and stored in the solution database.

 

by: mallik_123Posted on 2009-12-06 at 04:32:39ID: 25983214

Hi

I have requested for a reveiw and feedback of my solution .
but no one responded to this solution.

Regards
Mallik Sonti

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...