deleting rows from oracle table using ROWIDS

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

>>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.

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

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

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

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

Have you compared the schemas between DEV and PROD?

What is the hardware difference?

Are there any Oracle init differences or other settings?

~bp

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.

Hi

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

Thanks in advance.
Mallik

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.

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.

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

Hi

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

any idea??

thx
Mallik

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.

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

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:

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

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

>>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?

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

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:

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: