暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

fast delete, Best practice on Very large table

原创 Anbob 2011-08-10
588
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.
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论