oracle刪除億級(jí)數(shù)據(jù)

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

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

1、備份 2021年之前數(shù)據(jù)

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

2、備份2021年數(shù)據(jù)

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

1、drop table ，然后再create table，插入數(shù)據(jù)

1、drop table

drop table table_log?

2、create table table_log

3、插入2021年數(shù)據(jù)

insert into table_log select * from table_log_b2021;

2、使用delete批量刪除

1、先去掉表索引，因?yàn)樗饕赡軙?huì)影響刪除效率

2、批量執(zhí)行刪除語(yǔ)句，也可以以存過(guò)方式直接運(yùn)行，按照數(shù)量刪除

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

每500萬(wàn)一次，沒(méi)去索引之前大約耗時(shí)85秒，刪掉索引之后大約耗時(shí)50

3、使用truncate table ，然后再插入數(shù)據(jù)

1、truncate table

truncate ?table table_log ? 耗時(shí)0.5s

2、插入2021年數(shù)據(jù)

insert into table_log ? select * from table_log_b2021; ?耗時(shí) 0.095s

第一種涉及到刪表結(jié)構(gòu)，公司相關(guān)規(guī)定不可這么做，第二種效率較為低

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

oracle數(shù)據(jù)庫(kù)億級(jí)數(shù)據(jù)量清理SQL優(yōu)化常識(shí)

#ORACLE #SQL #億級(jí)數(shù)據(jù)量清理優(yōu)化

說(shuō)到刪除數(shù)據(jù)，想到的

第一種方法就是DELETE

確實(shí)對(duì)于清理數(shù)據(jù)量比較少的表來(lái)說(shuō)，DELETE是簡(jiǎn)單直接的方法，但是對(duì)于上億條數(shù)據(jù)的表來(lái)說(shuō)，這種方式肯定是行不通的，會(huì)造成undo表空間的不足。

第二種方法就是truncate

但是我們這里做的是刪除表中的部分?jǐn)?shù)據(jù)，而不是清空表數(shù)據(jù)，所以也不能直接truncate，既然不能全部truncate，我們一開(kāi)始想到的方式是先創(chuàng)建一個(gè)臨時(shí)表，把需要保留的數(shù)據(jù)拿出來(lái)，然后清空表數(shù)據(jù)只會(huì)，再把需要保留的數(shù)據(jù)放回去，最后刪除臨時(shí)表，這種方式就不會(huì)造成undo表空間不足了，但是這樣效率很慢，時(shí)間很長(zhǎng)，從二十億的數(shù)據(jù)量中刪除一半左右的數(shù)據(jù)需要八個(gè)小時(shí)，當(dāng)然這個(gè)和服務(wù)器的性能等硬件也有關(guān)系，如下如所示的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，表還不會(huì)生成索引，所以如果采用drop的方式，就還要?jiǎng)?chuàng)建索引，這種方式只需要一個(gè)小時(shí)左右的時(shí)間

 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/

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論