oracle中如何刪除億級資料

2023-02-16 06:02:28

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/

總結

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