有效率刪除大量rows with SQL

最直觀的方法就是`delete`，但這種方法效率不好，而且可能data被其它session擋住，解法為(oracle db適用)： :bulb::bulb::bulb: **將DML操作改為DDL操作** 1. 移除全部內容:`truncate` 2. 使用`create-table-as-select` 移除大部分的data 3. `drop`或`truncate` patition 4. 使用filtered table move --- ### DML statement 刪除rows ```sql delete from table where status = "expired" ``` ### 1. 移除全部內容:`truncate` 即時的metadata操作，預設會在最小區段(minextents)下重置space，可以透過storage clause改變行為 - truncate table ... reuse storage leaves all the space allocated to the table - truncate table ... drop storage(the default) deallocates all the space above the minextents for the table - truncate table ... drop all storage deallocates all space from the table ```sql -- 使用前需要確認無FK關聯的table，即使關聯table為空仍會拋錯 truncate table tableTable; -- ORA-02266: unique/primary keys in table referenced by enabled foreign keys -- 修正方案, 關聯table須為空, 否則無法重驗證FK alter table to_empty_it_child modify constraint fk disable; truncate table to_empty_it; alter table to_empty_it_child modify constraint fk enable; -- 關聯table也要一併刪除 truncate table to_empty_it cascade; ``` ### 2. 使用create -table-as-select(CTAS)法移除rows  :zany_face: insert資料比刪除還要快速，在刪除大量records時可以使用: - 建立新的table, 將欲保留的records存在這裡 - truncate原先table - 回存資料至原先table :zany_face: 類似方式，可以刪除舊表再直接更名新表 - 建立新的table, 將欲保留的records存在這裡 - drop or rename 舊表 - rename新table成原本的table name ```sql -- 回存資料 create table rows_to_keep select * from massive_table where save_these = 'Y'; truncate table massive_table; insert into massive_table select * from rows_to_keep; -- 換表更名, 只load data一次, 所以更快一些 create table rows_to_keep select * from massive_table where save_these = 'Y'; rename massive_table to massive_archived; rename rows_to_keep to massive_table; ``` ※ indexes, constraint, grant等都要記得複製過去, 此類步驟較花費時間, 要測試哪種ctas方法整體較有效率 ※ 各種相依性使其有點fiddly, 最好離線狀態處理 ### 3. 移除table的特定partition(sub-table) - table已事先進行partition - 目的是移除特定partition的**全部**內容 ```sql -- 注意要先關閉關聯table的fk alter table to_archive_from drop partition to_remove_it; alter table to_archive_from truncate partition to_empty_it; ``` ※ 此移除為例行性事務，否則做partion可能不符成本效益 ※ partition影響各式操作，像key queries會變慢 ### 4. 使用filter-table move 移除rows 通常`alter table … move`用來調整儲存的tablespace或是一些table的實體properties，如compression，屬於DDL操作，也能用來提升效能，移動重排處理records位置瑣碎分散的問題，沒被移動就是刪除 (oracle12.2+) :telescope: 建議操作前離線備份: expdp ```sql alter table to_delete_from move including rows where rows_to_keep = 'Y'; -- 如同truncate, 需注意 ORA-02266: unique/primary keys in table referenced by enabled foreign keys ``` --- 綜合來說 -- delete DML做絕對是最慢的， CTAS跟filtered move表現佳，truncate partition則是在移除量少時表現較佳 :bookmark_tabs: REF --- - [How to Delete Millions of Rows Fast with SQL](https://blogs.oracle.com/sql/post/how-to-delete-millions-of-rows-fast-with-sql) - [Remove Billions of Rows Really Fast...](https://www.linkedin.com/pulse/remove-billions-rows-really-fast-johny-perapras/)