delete 資料很慢調優解決

delete  from tb_test partition (TB_test_P200711)
 where id<=15744829 and id>=14471371
 and  created_date between  to_date('2007-11-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
 and to_date('2007-11-07 05:59:59','yyyy-mm-dd hh24:mi:ss')
 and id not in (select max(ID) id
                    from tb_test partition (TB_test_P200711)
                    where created_date between  to_date('2007-11-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
                     and to_date('2007-11-07 05:59:59','yyyy-mm-dd hh24:mi:ss')
                   group by client_id, game_code_id, event_date);

commit;

 

以上sql需要刪除60多萬條資料,加上分割槽和時間條件後,速度還可以介紹,只要4-5分鐘

但在另外一臺機器測試時,很慢,而且結果都出不來,一直執行

透過和正常機器對比, 執行計劃都一樣 ,表結構和索引,資料都已分析

最後只能能資料庫層看了,看下alert_ora9i.log 日誌檔案

日誌報有壞快

Errors in file /orasys/oracle/admin/ora9i/bdump/ora9i_smon_3928.trc:
ORA-01578: ORACLE data block corrupted (file # 38, block # 502423)
ORA-01110: data file 38: '/oradata/ora9i/game_lagindex08.dbf'

 

用以下語句查出看是什麼物件型別:

 select segment_type,owner||'.'||segment_name
from dba_extents
where 38= file_id and 502423 between block_id and block_id+blocks -1;

 

查出來是

這個表的索引有損壞,

然後刪除重建,再執行那delete sql 正常

 

該表索引損壞導致delete不正常