大數量的DML時對索引處理的技巧

追求完美9196發表於2024-07-24
索引

進行大資料量的DML時，資料處理會很慢，甚至資料處理直接卡死。對此最佳化的技巧之一就是先將表在DML中查詢用不到的索引置為無效以及nologging，DML處理完後再將這索引重建。

1 新建儲存要置為無效的索引的臨時表

create global temporary table TMP_VACHAR_LIST
(
  text VARCHAR2(4000)
)
on commit preserve rows;

2 將索引置為無效，資料處理後重建索引

begin
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_VACHAR_LIST';
  --記錄下來非唯一性約束,插入資料後要重建
  INSERT INTO TMP_VACHAR_LIST
    SELECT t.INDEX_NAME
      FROM user_indexes t
     where t.TABLE_NAME = 'TEST'
       and t.uniqueness = 'NONUNIQUE'
       and t.index_type = 'NORMAL';
  --先將原表索引 unusable 以及nologging,加快DML資料速度
  for ff in (SELECT t.INDEX_NAME, t.UNIQUENESS, t.index_type
               FROM user_indexes t
              where t.TABLE_NAME = 'TEST'
                and t.index_type = 'NORMAL') loop
    --將非唯一性約束索引 unusable 
    if ff.uniqueness = 'NONUNIQUE' then
      l_sqltext := 'alter index ' || ff.INDEX_NAME || ' unusable ';
    else
      --將唯一性約束索引 nologging
      --不能將唯一性約束unusable,因為會導致表插入不了資料
      l_sqltext := 'alter index ' || ff.INDEX_NAME || ' nologging';
    
    end if;
    execute immediate L_SQLTEXT;
  
  end loop;
  /*
  ---------------------------------
   此處省略對TEST表的DML處理SQL        
  
  ---------------------------------
  */
  --重建索引
  for ff in (SELECT text FROM TMP_VACHAR_LIST t) loop
    l_sqltext := 'alter index ' || ff.text ||
                 ' rebuild online parallel 8 nologging';
    execute immediate L_SQLTEXT;
  end loop;
end;

相關文章