Oracle 洗掉大量表記錄操作總結-有解無憂

By：授客 QQ：1033553122

洗掉表資料操作

清空所有表記錄

TRUNCATE TABLE your_table_name;

或者批量洗掉滿足條件的表記錄

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

釋放表空間

存放大資料量的表，其表空間占用也比較大，洗掉資料后并不會自動釋放這些記錄占用的表空間，所以，即便表里面資料量很少，查詢效率依舊很慢，所以，需要釋放表空間，

-- 查詢資料表占用的表空間大小
SELECT sum(bytes)/(1024*1024) FROM user_segments WHERE segment_name='YOUR_TABLE_NAME'; --注意，表名必須大寫

說明：sum(bytes)/(1024*1024) 資料統計單位由Byte轉為GB

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

重建索引

釋放了表空間以后表的ROWID會發生變化，基于ROWID的索引會失效，此時就需要重建索引

--重建非磁區索引
ALTER TABLE your_table_index REBUILD [ONLINE] [NOLOGGING];

--重建磁區索引
--針對磁區索引-非組合索引
ALTER INDEX your_table_index REBUILD PARTITION your_partition_name [ONLINE] [NOLOGGING];

注意：

設定日志級別為NOLOGGING 意味盡量減少日志，可以加速索引重建
ONLINE，NOLOGGING 兩者位置順序可以對調，不影響
普通情況下建立索引或者REBUILD索引時，oracle會對基表加共享鎖，在這期間，無法對表進行DML操作，如果希望避免這種情況，需要加ONLINE選項
對索引進行REBUILD時，如果不加ONLINE選項，則Oracle直接讀取原索引的資料，否則直接掃描表中的資料，索引在重建時，查詢仍然可以使用舊索引，實際上，Oracle在REBUILD索引的程序中，并不會洗掉舊索引，直到新索引重建成功，這就是相對洗掉索引然后重建索引的一個好處：不會影響原有的SQL查詢，但也正由于此，用REBUILD方式建立索引需要相應表空間的空閑空間是洗掉重建方式的2倍，

不能直接REBUILD整個磁區索引

對于非組合索引，需要REBUILD每個磁區（partition）
對于組合索引，需要REBUILD每個子磁區（subpartition）

磁區、子磁區較多的情況下，可以使用下面的SQL可以生成相應的REBUILD陳述句

--重建磁區索引-非組合索引
SELECT 'ALTER INDEX ' || index_owner || '.' ||index_name ||' REBUILD PARTITION ' || partition_name || ' NOLOGGING;'  
FROM dba_ind_partitions  
WHERE index_owner = 'INDEX_OWNER_NAME'  --可選查詢條件，注意，如果指定該條件，索引擁有者必須大寫
AND index_name = 'YOUR_INDEX_NAME'; --注意，索引名稱必須大寫 

--重建磁區索引-組合索引   
SELECT 'ALTER INDEX ' || index_owner || '.' ||index_name ||' REBUILD SUBPARTITION ' || subpartition_name || ' NOLOGGING;'  
FROM dba_ind_subpartitions
WHERE index_owner = 'INDEX_OWNER_NAME' --可選查詢條件，注意，如果指定該條件，索引擁有者必須大寫
AND index_name = 'YOUR_INDEX_NAME'; --注意，索引名稱必須大寫

針對非磁區索引，如果清理的表比較多，或者不知道被清理的表擁有哪些索引，可以使用以下SQL查詢并生成對應的重建索引SQL

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

分析表

分析表，是為了使基于CBO的執行計劃更加準確，在一定程度上能帶來一些性能提升

ANALYZE TABLE table_name COMPUTE STATISTICS;

--等價于
ANALYZE TABLE table_name COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL COLUMNS;

說明：生成的統計資訊的存放位置：

FOR TABLE的統計資訊存在于視圖：USER_TABLES 、ALL_TABLES、DBA_TABLES
FOR ALL INDEXES的統計資訊存在于視圖： USER_INDEXES 、ALL_INDEXES、DBA_INDEXES
FOR ALL COLUMNS的統計資訊存在于試圖：USER_TAB_COLUMNS、ALL_TAB_COLUMNS、DBA_TAB_COLUMNS

作者：授客
微信/QQ：1033553122
全國軟體測驗QQ交流群：7156436
Git地址：https://gitee.com/ishouke
友情提示：限于時間倉促，文中可能存在錯誤，歡迎指正、評論！
作者五行缺錢，如果覺得文章對您有幫助，請掃描下邊的二維碼打賞作者，金額隨意，您的支持將是我繼續創作的源動力，打賞后如有任何疑問，請聯系我!!!
微信打賞支付寶打賞全國軟體測驗交流QQ群

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/509068.html

標籤：其他

上一篇：SQL Server -- 解決存盤程序傳入引數作為sql陳述句條件值時，執行阻塞問題

下一篇：實作一個簡單的Database1(譯文）