快捷導(dǎo)航

Oracle?刪除大量表記錄操作分析總結(jié)

更新時(shí)間：2022年09月26日 17:02:06 作者：授客

這篇文章主要介紹了Oracle刪除大量表記錄操作分析總結(jié)，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下

刪除表數(shù)據(jù)操作

清空所有表記錄：

TRUNCATE TABLE your_table_name;

或者批量刪除滿(mǎn)足條件的表記錄：

BEGIN
  LOOP
    DELETE FROM your_table_name WHERE rownum <= 50000;
      EXIT WHEN SQL%ROWCOUNT = 0;
   COMMIT;
 END LOOP;
END;

釋放表空間

存放大數(shù)據(jù)量的表，其表空間占用也比較大，刪除數(shù)據(jù)后并不會(huì)自動(dòng)釋放這些記錄占用的表空間，所以，即便表里面數(shù)據(jù)量很少，查詢(xún)效率依舊很慢，所以，需要釋放表空間。

-- 查詢(xún)數(shù)據(jù)表占用的表空間大小
SELECT sum(bytes)/(1024*1024) FROM user_segments WHERE segment_name='YOUR_TABLE_NAME'; --注意，表名必須大寫(xiě)

說(shuō)明：sum(bytes)/(1024*1024) 數(shù)據(jù)統(tǒng)計(jì)單位由Byte轉(zhuǎn)為GB

--整理碎片，釋放已刪除記錄占用的表空間
ALTER TABLE your_table_name MOVE;

重建索引

釋放了表空間以后表的ROWID會(huì)發(fā)生變化，基于ROWID的索引會(huì)失效，此時(shí)就需要重建索引

--重建非分區(qū)索引
ALTER TABLE your_table_index REBUILD [ONLINE] [NOLOGGING];

--重建分區(qū)索引
--針對(duì)分區(qū)索引-非組合索引
ALTER INDEX your_table_index REBUILD PARTITION your_partition_name [ONLINE] [NOLOGGING];

注意：

設(shè)置日志級(jí)別為NOLOGGING 意味盡量減少日志，可以加速索引重建
ONLINE，NOLOGGING 兩者位置順序可以對(duì)調(diào)，不影響
普通情況下建立索引或者REBUILD索引時(shí)，oracle會(huì)對(duì)基表加共享鎖，在這期間，無(wú)法對(duì)表進(jìn)行DML操作。如果希望避免這種情況，需要加ONLINE選項(xiàng)
對(duì)索引進(jìn)行REBUILD時(shí)，如果不加ONLINE選項(xiàng)，則Oracle直接讀取原索引的數(shù)據(jù)，否則直接掃描表中的數(shù)據(jù) ，索引在重建時(shí)，查詢(xún)?nèi)匀豢梢允褂门f索引。實(shí)際上，Oracle在REBUILD索引的過(guò)程中，并不會(huì)刪除舊索引，直到新索引重建成功，這就是相對(duì)刪除索引然后重建索引的一個(gè)好處：不會(huì)影響原有的SQL查詢(xún)。但也正由于此，用REBUILD方式建立索引需要相應(yīng)表空間的空閑空間是刪除重建方式的2倍。
不能直接REBUILD整個(gè)分區(qū)索引
- 對(duì)于非組合索引，需要REBUILD每個(gè)分區(qū)（partition）
- 對(duì)于組合索引，需要REBUILD每個(gè)子分區(qū)（subpartition）
- 分區(qū)、子分區(qū)較多的情況下，可以使用下面的SQL可以生成相應(yīng)的REBUILD語(yǔ)句

--重建分區(qū)索引-非組合索引
SELECT 'ALTER INDEX ' || index_owner || '.' ||index_name ||' REBUILD PARTITION ' || partition_name || ' NOLOGGING;'  
FROM dba_ind_partitions  
WHERE index_owner = 'INDEX_OWNER_NAME'  --可選查詢(xún)條件，注意，如果指定該條件，索引擁有者必須大寫(xiě)
AND index_name = 'YOUR_INDEX_NAME'; --注意，索引名稱(chēng)必須大寫(xiě) 

--重建分區(qū)索引-組合索引   
SELECT 'ALTER INDEX ' || index_owner || '.' ||index_name ||' REBUILD SUBPARTITION ' || subpartition_name || ' NOLOGGING;'  
FROM dba_ind_subpartitions
WHERE index_owner = 'INDEX_OWNER_NAME' --可選查詢(xún)條件，注意，如果指定該條件，索引擁有者必須大寫(xiě)
AND index_name = 'YOUR_INDEX_NAME'; --注意，索引名稱(chēng)必須大寫(xiě)

針對(duì)非分區(qū)索引，如果清理的表比較多，或者不知道被清理的表?yè)碛心男┧饕梢允褂靡韵耂QL查詢(xún)并生成對(duì)應(yīng)的重建索引SQL

SELECT concat(concat('ALTER INDEX ', INDEX_NAME), ' REBUILD;') 
FROM all_indexes
WHERE owner='INDEX_OWNER_NAME' --可選查詢(xún)條件，注意，如果指定該條件，索引擁有者必須大寫(xiě)
AND table_name IN('TABLE_NAME1 ','TABLE_NAME2', '...', 'TABLE_NAMEN'）--注意，表名必須大寫(xiě)

分析表

分析表，是為了使基于CBO的執(zhí)行計(jì)劃更加準(zhǔn)確，在一定程度上能帶來(lái)一些性能提升

ANALYZE TABLE table_name COMPUTE STATISTICS;

--等價(jià)于
ANALYZE TABLE table_name COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL COLUMNS;

說(shuō)明：生成的統(tǒng)計(jì)信息的存放位置：

FOR TABLE的統(tǒng)計(jì)信息存在于視圖：USER_TABLES 、ALL_TABLES、DBA_TABLES
FOR ALL INDEXES的統(tǒng)計(jì)信息存在于視圖： USER_INDEXES 、ALL_INDEXES、DBA_INDEXES
FOR ALL COLUMNS的統(tǒng)計(jì)信息存在于試圖：USER_TAB_COLUMNS、ALL_TAB_COLUMNS、DBA_TAB_COLUMNS