oracle中如何刪除億級數(shù)據(jù)
oracle刪除億級數(shù)據(jù)
近期遇到一個日志表記錄了幾年數(shù)據(jù)，數(shù)據(jù)量較大（幾億數(shù)據(jù)），導致查詢等操作較慢，為了提高效率，決定刪除部分歷史數(shù)據(jù)，想了三種方法如下：
通用步驟（每種方法之前先運行以下備份腳本）：
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、先去掉表索引，因為索引可能會影響刪除效率
2、批量執(zhí)行刪除語句，也可以以存過方式直接運行，按照數(shù)量刪除
delete ?from table_log a ?where a.logtime<date'2021-1-1' and rownum<5000000
每500萬一次，沒去索引之前大約耗時85秒，刪掉索引之后大約耗時50
3、使用truncate table ，然后再插入數(shù)據(jù)
1、truncate table
truncate ?table table_log ? 耗時0.5s
2、插入2021年數(shù)據(jù)
insert into table_log ? select * from table_log_b2021; ?耗時 0.095s
第一種涉及到刪表結(jié)構(gòu)，公司相關(guān)規(guī)定不可這么做，第二種效率較為低
綜合考慮使用了第三種，效率較為可觀 ，秒秒鐘把數(shù)據(jù)干干凈。
oracle數(shù)據(jù)庫億級數(shù)據(jù)量清理SQL優(yōu)化常識
#ORACLE #SQL #億級數(shù)據(jù)量清理優(yōu)化
說到刪除數(shù)據(jù)，想到的
第一種方法就是DELETE
確實對于清理數(shù)據(jù)量比較少的表來說，DELETE是簡單直接的方法，但是對于上億條數(shù)據(jù)的表來說，這種方式肯定是行不通的，會造成undo表空間的不足。
第二種方法就是truncate
但是我們這里做的是刪除表中的部分數(shù)據(jù)，而不是清空表數(shù)據(jù)，所以也不能直接truncate，既然不能全部truncate，我們一開始想到的方式是先創(chuàng)建一個臨時表，把需要保留的數(shù)據(jù)拿出來，然后清空表數(shù)據(jù)只會，再把需要保留的數(shù)據(jù)放回去，最后刪除臨時表，這種方式就不會造成undo表空間不足了，但是這樣效率很慢，時間很長，從二十億的數(shù)據(jù)量中刪除一半左右的數(shù)據(jù)需要八個小時，當然這個和服務(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，表還不會生成索引，所以如果采用drop的方式，就還要創(chuàng)建索引，這種方式只需要一個小時左右的時間
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é)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
本篇文章是對解決報錯ora-32035的方法進行了詳細的分析介紹，需要的朋友參考下2013-05-05
這篇文章主要給大家介紹了關(guān)于oracle鎖表如何解決的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-11-11
這篇文章主要介紹了Oracle中如何創(chuàng)建用戶、表(1)問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
這篇文章主要介紹了Oracle中字符串截取常用方法總結(jié)，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下2018-11-11
這篇文章主要為大家詳細介紹了centos下Oracle11G圖文安裝教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-07-07
在Tomcat服務(wù)器下使用連接池連接Oracle數(shù)據(jù)庫
本文為大家介紹下在Tomcat服務(wù)器下使用連接池來連接數(shù)據(jù)庫的操作，下面有個不錯的示例，大家可以參考下2014-01-01
Oracle連續(xù)相同數(shù)據(jù)的統(tǒng)計
今天小編就為大家分享一篇關(guān)于Oracle連續(xù)相同數(shù)據(jù)的統(tǒng)計，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-01-01
解決Oracle刪除重復數(shù)據(jù)只留一條的方法詳解
本篇文章是對Oracle刪除重復數(shù)據(jù)只留一條的解決方法進行了詳細的分析介紹，需要的朋友參考下2013-05-05
Windows系統(tǒng)下Oracle數(shù)據(jù)庫每天自動備份
linux和unix下面使用shell可以很方便實現(xiàn)，如果windows環(huán)境下可以結(jié)合計劃任務(wù)實現(xiàn)自動備份，下面通過本文給大家介紹實現(xiàn)方法，需要的朋友參考下吧2016-12-12
最近在做一個樹狀編碼管理系統(tǒng),其中用到了oracle的樹狀遞歸查詢,下面這篇文章主要給大家介紹了關(guān)于Oracle遞歸查詢的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-11-11