best way to delete large number of rows

[a128]

Hi,

I have a table containing about 2 million rows. I need to delete about a million of them based on simple criteria, say column x = 1. But I am worry about Oracle's RBS may grow so big that it can't handle it. What is the best way to delete rows in this case? If I have to allocate a big RBS for this purpose, how do I tell it to keep before-image of the deletion in this particular RBS? Or there is a better way to achieve this?

Thanks,

[stmontgo]

create a new table,
insert as select into the new table from the old,
backup the original table,
drop the original table
rename the new table to the old table,
rebuild indexes grants etc on the new table

rbs is the least of your concerns , mass deletes
can be a serious performance hit as such an action
does no reset the highwater mater and subsequent
scans will scnn up the high water mark even though
those blocks may be empty.

steve

[rajabalachandra]

you can delete from the table with logging off.
here you do not need to worry about rollback segments.
careful. you won't be able to go back in case any problem.

eg.

delete from table1
where x=y nologging;

- raja balachandran

[virajvk]

delete from table1 where x=y nologging;

I dont think this will work.
Good idea is already suggested by stmontgo

I use PL/SQL loop commiting after every 5000 rows

Regards

---------------------------
9i OCP
First post After Becoming Oracle Certified

[SANJAY_G]

Originally posted by rajabalachandra
you can delete from the table with logging off.
here you do not need to worry about rollback segments.
careful. you won't be able to go back in case any problem.

eg.

delete from table1
where x=y nologging;

- raja balachandran

Wrong! Nologging has no effect on rollback generation. Better delete in small bathces if you do not have sufficient rollback space.

[virajvk]

Wrong! Nologging has no effect on rollback generation. Better delete in small bathces if you do not have sufficient rollback space

Yes 100% True.

Now Why it will use Rollback Segments is becaz
READ CONSISTANCY FUNDAMENTALS

Regards

Viraj
------------
9i OCP

[abhaysk]

Select count(*) into variable from table where ur condition

variable:=Round((variable)/(max records u think u can delete))+1

For cnt 1 to variable loop

delete with ur condition and with rownum < (max records u think u can delete)+1.

Loop.

Regards
Abhay.

[DaPi]

think you want a COMMIT in that loop

[jmodic]

Originally posted by abhaysk
Select count(*) into variable from table where ur condition

variable:=Round((variable)/(max records u think u can delete))+1

For cnt 1 to variable loop

delete with ur condition and with rownum < (max records u think u can delete)+1.

Loop.

You've forgotten to stick a COMMIT inside your loop aftere each batch delete.

Besides, you can code this much more elegantly without the need for the initial SELECT if instead of FOR LOOP you simply use LOOP with EXIT:

LOOP
  DELETE with ur condition and
         with rownum < (max records u think u can delete);
  EXIT WHEN sql%ROWCOUNT = 0;
  COMMIT;
END LOOP;

[abhaysk]

Originally posted by DaPi
think you want a COMMIT in that loop

Thas correct....

forgot to type...

Abhay.