Anybody tel me how to delete huge data from Oracle 9i DB

Question

Hi i have a table that is 5 GB now i was trying to delete like below:

delete  from tablename
where to_char(screatetime,'yyyy-mm-dd') <'2009-06-01' 

But its running long and no response. Meanwhile I tried to check if anybody is blocking with this below:

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2

but I didn't find any blocking also.

Can anybody tell me how can I delete this large data without any problem??

Thanks in advance

Answer 1

• Use a query condition to export necessary rows
• Truncate table
• Import rows

Answer 2

If there is an index on screatetime your query may not be using it. Change your statement so that your where clause can use the index.

delete from tablename where screatetime < to_date('2009-06-01','yyyy-mm-dd')

Answer 3

It runs MUCH faster when you lock the table first. Also change the where clause, as suggested by Rene.

LOCK TABLE tablename IN EXCLUSIVE MODE;

DELETE FROM tablename
where screatetime < to_date('2009-06-01','yyyy-mm-dd');

EDIT: If the table cannot be locked, because it is constantly accessed, you can choose the salami tactic to delete those rows:

BEGIN
  LOOP
    DELETE FROM tablename
      WHERE screatetime < to_date('2009-06-01','yyyy-mm-dd')
        AND ROWNUM<=10000;
    EXIT WHEN SQL%ROWCOUNT=0;
    COMMIT;
  END LOOP;
END;

Overall, this will be slower, but it wont burst your rollback segment and you can see the progress in another session (i.e. the number of rows in tablename goes down). And if you have to kill it for some reason, rollback won't take forever and you haven't lost all work done so far.

Answer 4

5GB is not a useful measurement of table size. The total number of rows matters. The number of rows you are going to delete as a proportion of the total matters. The average length of the row matters.

If the proportion of the rows to be deleted is tiny it may be worth your while creating an index on screatetime which you will drop afterwards. This may mean your entire operation takes longer, but crucially, it will reduce the time it takes for you to delete the rows.

On the other hand, if you are deleting a large chunk of rows you might find it better to

1. Create a copy of the table using 'create table t1_copy as select * from t1 where screatedate >= to_date('2009-06-01','yyyy-mm-dd')`
2. Swap the tables using the rename command.
3. Re-apply constraints, indexs to the new T1.

Another thing to bear in mind is that deletions eat more UNDO than other transactions, because they take more information to rollback. So if your records are long and/or numerous then your DBA may need to check the UNDO tablespace (or rollback segs if you're still using them).

Finally, have you done any investigation to see where the time is actually going? DELETE statements are just another query, and they can be tackled using the normal panoply of tuning tricks.