oracle数据库亿级数据量清理SQL优化经验分享

#ORACLE #SQL #亿级数据量清理优化

1. 说到删除数据，想到的第一种方法就是DELETE，确实对于清理数据量比较少的表来说，DELETE是简单直接的方法，但是对于上亿条数据的表来说，这种方式肯定是行不通的，会造成undo表空间的不足。

2. 第二种方法就是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/ 

3. 删除的第三种方式就是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/

4. 如果大家还有速度更快，效率更高的方式，欢迎共同探讨