-
Hi
I have a table called SHOPPER which is accessed by my first page of the web site. It actually checks whether the user has been to the iste before or not. When a user comes he may or may not register the status of which is shown in one column as Y or N .
This table is 2.9 million rows and I wish to delete appx 1.1 million rows. The problem i sthis is my PROD database and when i did a delete yesterday for about 20,000 rows the webiste cannot be accessed during that delete .
is there a way I can delete without locking the table or any other way in which I can delete these rows without affecting the website.
Thanks
-
It sounds like you need to pick an off-peak time to run
your deletes. (Do you have off-peak ?)
Because you are not deleting the entire
table, you CANNOT USE TRUNCATE, which deletes all rows in
the table very quickly.
It sounds like you need to write a PLSQL procedure/package
with a cursor loop, to delete the rows in small quantities at a
time, and then commits.
Try and tune the where clause of the delete statement to access the rows by rowid.
-
If you delete based on a criteria (like date, for example) why don't you partition the table as to truncate a specific partition instead of deleting. This will take a lot less time than deletion because rollback segment is not used an it doesn't lock the entire table.
-
Mark_Woz method should work.
As alternative, if you have time and resources you can
1) export the table into a temp table,
2)delete records from the temp table,
3)rename your production table to something else,
4) rename temp table to a production table name.
5)Drop a production table.
However, I wouldn't drop it right away untill I was absolutely sure I've 100% correct results during my delete. I think it would be a good idea to keep your production table for a while either as a db object or a dump file.
If you use scenario described above there will be absolute minimum down time for your users.
-
1. Create a temporary table with the key values from SHOPPER that you want to delete. (Assuming key_value is your PK)
create table delkeys as select key_value from shopper
where (the criteria you want to delete)
/
2. Write a PL/SQL Loop that deletes the keys one by one, commits every 1000 records, and then sleeps for 100 seconds. (To allow other updates to happen. Using _ as a space place holder).
declare
___i integer;
begin
___i:=0;
___for x in (select key_value from delkeys) loop
______i:=i+1;
______delete from shopper where key_value = x.key_value;
______if mod(i,1000) = 0 then
_________commit work;
_________dbms_lock.sleep(100);
______end if;
___end loop;
___commit work; -- commit the last partial batch
end;
-
Hi,
you can try this thing.
i. Create an index on the column you are using as a criteria to delete records in the table.
ii. Delete records from the table for every (1000-5000) rows.
-
Hi marist
I wrote a PL/SQL block and it deletes 100 rows at a time . Just one problem not getting back the control back of screen . May be you guys can help
declare
id number := 0;
begin
select count(*) into id from puneet;
loop
delete from shopper where shfrnbr in (select shfrnbr from puneet where rownum < 100);
delete from puneet where rownum < 100;
commit;
id := id - 100;
EXIT when id =0;
end loop ;
end;
-
change "EXIT when id =0;" to "EXIT when id <= 0;" and try.
"EXIT WHEN ID Less than or equal to 0 "
(in case the less than symbol is not displaying.
-
I would go with Jeff's solution. If you need to delete 1.1million rows in a specific amount of time you can adjust how long it sleeps for if 100 secs is too long, or increase the batch size to slightly larger.
Then you can just truncate the delkeys table for the next time you need to insert your keys into it.
