SQL優化--刪除表的資料來加速

今天上午檢視em工具，在9點半的時候applications又升

到了60去，雖然很快釋放，沒有引起資料庫慢，但是這

裡還是重視一下。用上午8點到10的快照做成一個awr報告，

通過檢視 SQL Statistice-SQL ordered by elapsed time，

找出排名第一的SQL,Sql如下：

SELECT message_id, reciver_employee_id, msg_type, msg_title, msg_content,

       operator_time, linkurl, TIMEOUT, position_id, awake_time

  FROM t_s_message

 WHERE (show_times > 0 AND awake_time <= SYSDATE)

    OR (show_times > 0 AND awake_time IS NULL)

檢視這個SQL，發現執行計劃很糟糕，其實就是一個全表掃描，t_s_message這張

表應該也清理一下了，但是整理資料庫表空間的時候看這個表小就暫時沒有做處

理，現在處理一下當時說的t_s_message這張表留半年的

刪出表內容的時候要注意的一個地方

select   a.owner 主鍵擁有者,

         a.table_name 主鍵表,

         b.column_name 主鍵列,

         C.OWNER 外來鍵擁有者,

         c.table_name 外來鍵表,

         d.column_name 外來鍵列

from user_constraints a,

     user_cons_columns b,

     user_constraints C,

     user_cons_columns d

where  a.constraint_name=b.constraint_name 

and C.R_CONSTRAINT_NAME=a.constraint_name 

and c.constraint_name=d.constraint_name 

and a.constraint_type='P' 

and a.table_name='T_S_MESSAGE' --需要檢視主外來鍵關係的表

order by a.table_name

結果為零行，說明這個表裡面的資料沒有被其他表的外來鍵所參考，

這樣的話，這個表就可以刪除，還是用移表的方式，直接刪除肯

定還是會報外來鍵約束刪不掉的錯誤，但是如果用rename的方式移

除表的話，資料庫就會報錯了

Create Table t_s_Message_bak_2011 As Select * From t_s_message

保險期間還是分時間段刪除，這個很重要，有次在測試機上面直接

刪100W資料就出現了效能問題，整個資料庫效能急劇下滑，當我要

停掉會話的時候發現還停不掉，我手工從系統層面將會話結束，不

過資料庫仍然很慢，我知道據庫還在做回滾，一次刪除太多，回滾

的時間也會很長，而且在這期間仍然是影響效能的，所以正式庫上

刪除資料一定要小心再小心

Delete From t_s_message a Where a.operator_time

 Between to_date('2010-03-01 00:00:00','yyyy/MM/DD/ HH24:MI:SS') and to_date('2011-01-01 00:00:00','yyyy/MM/DD/ HH24:MI:SS')  

Delete From t_s_message a Where a.operator_time

 Between to_date('2011-01-01 00:00:00','yyyy/MM/DD/ HH24:MI:SS') and to_date('2011-06-01 00:00:00','yyyy/MM/DD/ HH24:MI:SS')  

刪除完後，檢視執行計劃仍然沒有改變，收縮一下表

SQL> alter table t_s_message shrink space;

alter index MESSAGE_RECIVER_POSTION_FK rebuild

alter index PK_T_S_MESSAGE rebuild

alter index CASE_MESSAGE_FK rebuild

alter index MESSAGE_SENDER_FK rebuild

alter index MESSAGE_RECIVER_FK rebuild

這裡直接用的rebuild，沒有用online,速度比較快，他們的具體差異

大致說就是有一張臨時表的過渡，讓rebuild online的時候索引仍然

能正常使用，不過這種操作還是比較消耗系統資源，應當在系統較為

空閒的時候再做次操作

收集一下統計資訊

exec dbms_stats.gather_table_stats('GC','T_S_MESSAGE');

整個完成後，上面那條SQL執行就很快了，我漸漸意識

到有的SQL並不是說要如何如何優化，一個合理的業務

需求也是很重要的，線上交易系統裡面的表是不是應該

控制一下大小，不能無限制的增大