首页 » ORACLE » fast delete, Best practice on Very large table

fast delete, Best practice on Very large table

Today I read foreign blog,I think I learned the true knowledge

One of most operations we are performing is “Deleting many rows from oracle big tables” ;
Ths most common / easier query we always have in mind is :

BEGIN
DELETE FROM MY_BIG_TABLE WHERE MY_COLUMN
COMMIT;
END;
/
The problem the query can take hours to finish and may generate a huge amounts of archives logs;

3 solutions can be used here:

Solution1:
create table MY_NEW_BIG_TABLE NOLOGGING as select * from MY_BIG_TABLE where … ;
drop table MY_BIG_TABLE ;
rename MY_NEW_BIG_TABLE to MY_BIG_TABLE ;
create index old_table_idx1 on MY_BIG_TABLE (My_col1,My_col2) NOLOGGING parallel 2 tablespace INDX;
…..

1- A new table is created Without log generated during the operation.
2- Move the data to a new table, drop and then rename the old table by a new one.
3- Create indexes as fast as possible , without log during the creation of the index.

Solution 2:

Partition the data, do a parallel delete. Each partition will use its own rollback segment, each will run in parallel.

Solution 3:

Partition the data so that you can do a Truncate the partition instead of DELETE.

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Mohammed Konstantinidi | #1
    2011-12-21 at 06:18

    I discovered your blog web site on google and verify a number of of your early posts. Continue to keep up the superb operate. I simply additional up your RSS feed to my MSN News Reader. Searching for ahead to studying extra from you afterward!…