oracle删除亿级数据
近期遇到一个日志表记录了几年数据，数据量较大（几亿数据），导致查询等操作较慢，为了提高效率，决定删除部分历史数据，想了三种方法如下：
通用步骤（每种方法之前先运行以下备份脚本）：
1、备份 2021年之前数据
|
1
|
create table table_log_b2020 as select * from table_log a where a.logtime<date'2021-1-1'; |
2、备份2021年数据
|
1
|
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
|
1
|
drop table table_log |
2、create table table_log
3、插入2021年数据
|
1
|
insert into table_log select * from table_log_b2021; |
2、使用delete批量删除
1、先去掉表索引，因为索引可能会影响删除效率
2、批量执行删除语句，也可以以存过方式直接运行，按照数量删除
|
1
|
delete from table_log a where a.logtime<date'2021-1-1' and rownum<5000000 |
每500万一次，没去索引之前大约耗时85秒，删掉索引之后大约耗时50
3、使用truncate table ，然后再插入数据
1、truncate table
|
1
|
truncate table table_log 耗时0.5s |
2、插入2021年数据
|
1
|
insert into table_log select * from table_log_b2021; 耗时 0.095s |
第一种涉及到删表结构，公司相关规定不可这么做，第二种效率较为低
综合考虑使用了第三种，效率较为可观 ，秒秒钟把数据干干净。
oracle数据库亿级数据量清理SQL优化常识
#ORACLE #SQL #亿级数据量清理优化
说到删除数据，想到的
第一种方法就是DELETE
确实对于清理数据量比较少的表来说，DELETE是简单直接的方法，但是对于上亿条数据的表来说，这种方式肯定是行不通的，会造成undo表空间的不足。
第二种方法就是truncate
但是我们这里做的是删除表中的部分数据，而不是清空表数据，所以也不能直接truncate，既然不能全部truncate，我们一开始想到的方式是先创建一个临时表，把需要保留的数据拿出来，然后清空表数据只会，再把需要保留的数据放回去，最后删除临时表，这种方式就不会造成undo表空间不足了，但是这样效率很慢，时间很长，从二十亿的数据量中删除一半左右的数据需要八个小时，当然这个和服务器的性能等硬件也有关系，如下如所示的SQL，虽然能够删除成功，但是速度很慢。
|
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE SYSADM.PS_GPCN_PAYEE_TBL2022 ASSELECT * 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的方式，就还要创建索引，这种方式只需要一个小时左右的时间
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE TABLE SYSADM.PS_GP_ITER_TRGR2022 ASSELECT * 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 ASSELECT * 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/ |
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/jungeCSND/article/details/119818022
