[SQL]Fast Update database with more than 10 million records

找資料後比較過好用的作法
1.先把要update的欄位取出來
2.用bulk collect的方式，取rowid去update

測試過，原本130萬左右的資料，用原本的update跑了一個晚上(12小時)還跑不完
後來改用這個方法後，跑了一個小時就跑完了!

DECLARE
    CURSOR rec_cur IS
    SELECT DATE_ORIGIN 
    FROM MAIN_TBL WHERE DATE_ORIGIN IS NULL;

    TYPE date_tab_t IS TABLE OF DATE;

    date_tab date_tab_t;

BEGIN
    OPEN rec_cur;
    LOOP
        FETCH rec_cur BULK COLLECT INTO date_tab LIMIT 1000;
        EXIT WHEN date_tab.COUNT() = 0;

        FORALL i IN 1 .. date_tab.COUNT
            UPDATE MAIN_TBL SET DATE_ORIGIN  = '23-JAN-2012' 
            WHERE DATE_ORIGIN IS NULL;

    END LOOP;
    CLOSE rec_cur;
END;

參考：
http://stackoverflow.com/questions/9283316/fast-update-database-with-more-than-10-million-records

http://codedmi.com/questions/2130346/tune-the-plsql-limit-parameter-in-bulk-update

http://stackoverflow.com/questions/17156137/update-million-rows-using-rowids-from-one-table-to-another-oracle