	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

deleting rows from oracle table using ROWIDS

Asked by mallik_123 in Oracle 8.x, Databases Miscellaneous, PL / SQL, Oracle Database

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

Get your answer NOW

         
           
             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:

           
           
             ********************************************
 
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;
/
 
 
********************************************

           
         

Attachments:

CRPI-INVENTORY-PURGE1.txt (545 bytes) (File Type Details)

code to delete records froma oracle table using rowid

	Title
1	Intermitten commits - howto?
2	Removing hierarchical data
3	Faster Way to Create Data (Text File I…
4	1NF Discussion
5	ORA-01002: fetch out of seque…
6	Why this index is not being fired?