-
best way to delete large number of rows
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,
Last edited by a128; 01-19-2003 at 08:44 PM.
-
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
I'm stmontgo and I approve of this message
-
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
-
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
A Wise Man Knows How much he doesn't know !!!
-
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.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
think you want a COMMIT in that loop
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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:
Code:
LOOP
DELETE with ur condition and
with rownum < (max records u think u can delete);
EXIT WHEN sql%ROWCOUNT = 0;
COMMIT;
END LOOP;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by DaPi
think you want a COMMIT in that loop
Thas correct....
forgot to type...
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|