目錄

• oracle刪除億級數據
• 1、drop table ，然后再create table，插入數據
• 2、使用delete批量刪除
• 3、使用truncate table ，然后再插入數據
• oracle數據庫億級數據量清理SQL優化常識
• 第一種方法就是DELETE
• 第二種方法就是truncate
• 刪除的第三種方式就是DROP
• 總結

oracle刪除億級數據

近期遇到一個日志表記錄了幾年數據，數據量較大（幾億數據），導致查詢等操作較慢，為了提高效率，決定刪除部分歷史數據，想了三種方法如下：

通用步驟（每種方法之前先運行以下備份腳本）：

1、備份 2021年之前數據

create table table_log_b2020 as ?select * from table_log a where a.logtime<date"2021-1-1";

2、備份2021年數據

create table table_log_2021 as ?select * from table_log a where a.logtime>=date"2021-1-1";

1、drop table ，然后再create table，插入數據

1、drop table

drop table table_log?

2、create table table_log

3、插入2021年數據

insert into table_log select * from table_log_b2021;

2、使用delete批量刪除

1、先去掉表索引，因為索引可能會影響刪除效率

2、批量執行刪除語句，也可以以存過方式直接運行，按照數量刪除

delete ?from table_log a ?where a.logtime<date"2021-1-1" and rownum<5000000

每500萬一次，沒去索引之前大約耗時85秒，刪掉索引之后大約耗時50

3、使用truncate table ，然后再插入數據

1、truncate table

truncate ?table table_log ? 耗時0.5s

2、插入2021年數據

insert into table_log ? select * from table_log_b2021; ?耗時 0.095s

第一種涉及到刪表結構，公司相關規定不可這么做，第二種效率較為低

綜合考慮使用了第三種，效率較為可觀 ，秒秒鐘把數據干干凈。

oracle數據庫億級數據量清理SQL優化常識

#ORACLE #SQL #億級數據量清理優化

說到刪除數據，想到的

第一種方法就是DELETE

確實對于清理數據量比較少的表來說，DELETE是簡單直接的方法，但是對于上億條數據的表來說，這種方式肯定是行不通的，會造成undo表空間的不足。

第二種方法就是truncate

但是我們這里做的是刪除表中的部分數據，而不是清空表數據，所以也不能直接truncate，既然不能全部truncate，我們一開始想到的方式是先創建一個臨時表，把需要保留的數據拿出來，然后清空表數據只會，再把需要保留的數據放回去，最后刪除臨時表，這種方式就不會造成undo表空間不足了，但是這樣效率很慢，時間很長，從二十億的數據量中刪除一半左右的數據需要八個小時，當然這個和服務器的性能等硬件也有關系，如下如所示的SQL，雖然能夠刪除成功，但是速度很慢。

 CREATE TABLE SYSADM.PS_GPCN_PAYEE_TBL2022 AS  SELECT *   FROM SYSADM.PS_GPCN_PAYEE_TBL  WHERE EMPLID IN (  SELECT EMPLID3   FROM PS_C_PAY_TBL)/ COMMIT/ TRUNCATE TABLE SYSADM.PS_GPCN_PAYEE_TBL/  INSERT INTO SYSADM.PS_GPCN_PAYEE_TBL NOLOGGING  SELECT *   FROM SYSADM.PS_GPCN_PAYEE_TBL2022/ COMMIT/  DROP TABLE SYSADM.PS_GPCN_PAYEE_TBL2022/ COMMIT/ 

刪除的第三種方式就是DROP

直接把表干掉，drop的速度要比truncate更快，如果drop掉表之后再create，表還不會生成索引，所以如果采用drop的方式，就還要創建索引，這種方式只需要一個小時左右的時間

 CREATE TABLE SYSADM.PS_GP_ITER_TRGR2022 AS  SELECT *   FROM SYSADM.PS_GP_ITER_TRGR  WHERE EMPLID IN (  SELECT EMPLID3   FROM PS_C_PAY_TBL)/ COMMIT/  DROP TABLE SYSADM.PS_GP_ITER_TRGR/  CREATE TABLE SYSADM.PS_GP_ITER_TRGR AS  SELECT *   FROM SYSADM.PS_GP_ITER_TRGR2022 /  CREATE INDEX PSAGP_ITER_TRGR ON PS_GP_ITER_TRGR (CAL_RUN_ID  , EMPLID  , ITER_TRGR_STATUS) tablespace PSINDEX pctfree 10 initrans 2 maxtrans 255 storage ( initial 48K NEXT 112K minextents 1 maxextents unlimited )/  CREATE INDEX PS_GP_ITER_TRGR ON PS_GP_ITER_TRGR (EMPLID  , CAL_RUN_ID  , ITER_TRGR_STATUS) tablespace PSINDEX pctfree 10 initrans 2 maxtrans 255 storage ( initial 80K NEXT 112K minextents 1 maxextents unlimited )/  DROP TABLE SYSADM.PS_GP_ITER_TRGR2022/ COMMIT/

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持。