【大话IT】求救！delete where 删除数据慢

swuswu

大过年的先给各位朋友拜年了！
情况是这样，我本人oracle水平一般，项目原为mysql开发，现转换到oracle，没想到出问题了，

描叙：测试库为oracle 10G默认安装，项目有一个表为新闻信息主表，排序啊这些字段都放在这里，索引比较多，真实生产库数据量应该只是几百W级别的，现加了有100W测试数据，有个功能为delete from mian where xxx=aa and id>12345

没想到在oracle下，这个delete操作执行非常慢！！！！删除1W数据都要很长时间，希望各位oracle高手帮分析下如何才能优化，无论是从来sql语句入手或者oracle调优本身都可以

btw:我之前拜读过论坛上不少帖子，好象有建议创建其他表后转移数据等方法，但我这个表是主表，有业务读写不能随便停，所以这个方法恐怕不好办。

sqysl

其实，就删除数据来说，和数据库没多大关系，起码mysql和oracle没那么大区别，具体楼主的问题出在哪里，这不好说，如果只有这个操作慢，还是怀疑sql级别的问题，比如：执行计划，可以贴出来大家一起看看。

lwfoon

如果你的表使用索引，你可以关闭索引或删除。删除后再重建。

另一个法子就是parallel DML

alter session enable parallel dml;, delete /*+ parallel */ ...;

如果可以根据索引rowid删除，会比你利用ID > xxxxx更快。

如果你有兴趣，也可以研究下FORALL及BULK COLLECT的功能。

http://www.toadworld.com/platfor ... t-with-queries.aspx

lfree

delete from mian where xxx=aa and id>12345
会删除多少，占整个表的百分比是多少。

一般很大的话，业务可以停，可以采用ctas模式。
不行，可能要考虑分区，按照xxx来分区。

taomct

TABLE 設計錯誤, 無關db 是Oracle or ..., 如果不知道要如何區分可以使用, 多翻翻 TABLE PARTITION 文章.
建議你不要使用 parallel DML 方式, 除非你很懂.
建議你不要亂建 index 方式刪除, 保證你可以刪除3小時都還刪除不完.

既使時體資料5億筆資料, 要處理幾萬筆 delete or insert or update  ... 都可以保證幾秒就可以完成.

範例:
CREATE TABLE mian_new1
(
xxx,
id,
...
)
PARTITION BY HASH  (xxx)
(
  PARTITION mian_new1
);
xxx 必須是 group 型態, 資料不可是超過 2 萬筆以上.

不知道要如何分, 可以使用 select xxx , count(*) from main group by xxx;
假設是 select xxx , id, count(*) from main group by xxx,id;  比較合理是1千筆或是1百筆, 這要看你的機器一次可以處理極限.

CREATE TABLE mian_new2
(
xxx,
id,
...
)
PARTITION BY HASH  (xxx,id)
(
  PARTITION mian_new2
);

你可以先簡單實驗就可以知道.
1. 先建立一個 new TABLE PARTITION , 範例 main_new
2. 在空檔時間使用 insert into main_new select * from main; 先了解insert 會花掉多少時間.
3. 在使用 delete from main_new where xxx=aa and id>12345, 會花掉多少時間.
4. 確定以上方法可以, 再找時間停機制換. 如過年期間停機轉換.
5. 轉換方式很多, 看你設計方式, 不能停機我們都是採用假日交易量少的時間.
    都是先將前一天資料轉換好, 切換時間都是晚間22:00pm - 23:00pm 將原本 main table rename  main_old
    再將新的 main_new reanme main
    再將當天資料 insert into main select * from main_old where 今天資料 or 有異動資料, 這樣就可以轉換好.
    再 count main , main_old 是否一致
    再 使用小程式筆對 main , main_old 資料是否一致 (for loop ...)
    確定一週沒問題就會將 main_old  drop 掉.

建議你就先實驗機上玩一玩你就會了解 TABLE PARTITION 好處.
切錯 TABLE PARTITION 也會快很多, 但不能保證是最快.

你可以多建立幾個 TABLE PARTITION 實驗看看就會知道了.

CREATE TABLE mian_new3
(
xxx,
id,
group_id,
...
)
PARTITION BY HASH  (group_id)
(
  PARTITION mian_new3
);

我家裡實驗機有12台, 所以我可以很明確知道在不同等級機器上, 那種方式是最有效率.
如果你是用 VM 方式是驗不出什的效能.

祝你好運

bfc99

select count(id) from mian where xxx=aa and id>12345;
的结果是多少？

如果该数量占总量的比例较大，而且表上还有很多索引的情况下，删除会是比较慢的。个人建议的方法：
方法一、先失效表上的所有索引，然后执行删除，完成后，再重建索引。（会对业务有影响，主要是慢，但可用。）
方法二、把要保留的数据复制到一中间表，截断表，再失效表上的所有索引，然后把中间表上的数据插回，最后重建索引。（业务要停，但几百万的记录，应该不会太长时间。为保险起见，可在测试环境上先行演练。）
方法三：在以上两种方法的基础上，在各操作步骤上启用并行DML。通常情况下，会进一步缩短操作时间。

kelsoncong

先看执行计划啊，瞎猜没啥意思

bnso

taomct 发表于 2015-2-23 13:12
TABLE 設計錯誤, 無關db 是Oracle or ..., 如果不知道要如何區分可以使用, 多翻翻 TABLE PARTITION 文章.
...

我家裡實驗機有12台...

---------------------
牛人啊

swuswu

感谢各位朋友的回复，正学习各位的意见，有几点反馈下

1。查看了原mysql的删除业务java部分代码，发现不是我写的 >12333 这样的直接删除，而是每次删除500数据，然后立即提交删除操作事务，再如此继续，我现在在oracle版本的java部分也按分批一笔一小笔删除了这样做了，勉强能接受，所以我要把执行计划贴一下，有全表扫描。。。而由于这个表是核心业务表，索引停止来删除，恐怕不太好办

2。执行计划：目前单表测试只加到80万笔数据而已

    delete from CONTENT_MAIN_INFO where classId=34567 and contentId>1173374，删除了有2000行数据，用时10.157s
   

lwfoon

swuswu 发表于 2015-2-25 20:53
感谢各位朋友的回复，正学习各位的意见，有几点反馈下

1。查看了原mysql的删除业务java部分代码，发现不 ...

你这么做是对的。每500行commit一次减少对memory的需求也减少row lock等等的问题。如果你经常删除及增加数据，最好是线上重建索引。

DBMS_REDEFINITION.CAN_REDEF_TABLE能够让你知道有没有需要重建。

之前我检查了下分区表的影响。跟我的同事讨论后觉得你的coding会需要变化很多。比如需要PK及需要增加分区的分辨(partion)