更新索引字段会触发索引重建,如果是要大批量修改大数据表的索引字段,速度会很慢,时间大多花在重建索引上了。
高效的办法是将索引先unusable,待批次更新修改后再重建(rebuild)索引。
整理語法如下,需要注意:

  1. 通过索引判断一个table是否大数据表(大于50M)。
  2. 对于分区表的索引,需用对不同分区进行重建。
declare
    CURSOR c1 IS
    SELECT index_name,PARTITIONED
    FROM user_indexes
    WHERE index_name in (
            SELECT DISTINCT INDEX_NAME
            FROM user_ind_columns
            WHERE table_name = 'TABLE_NAME' --表名
                AND column_name = 'COLUMN_NAME' --更新字段
                AND table_name IN (
                    SELECT segment_name
                    FROM (
                        SELECT segment_name
                            ,sum(siz_M) AS siz_M
                        FROM (
                            SELECT segment_name
                                ,segment_type
                                ,bytes / 1024 / 1024 AS siz_M
                            FROM user_segments
                            WHERE segment_type LIKE 'TABLE%'
                                AND segment_name = 'TABLE_NAME' --表名
                            )
                        GROUP BY segment_name
                        )
                    WHERE siz_M >= 50  --定义达到50M的表为大数据表
                    )
            );

    TYPE IDXINFOREC 
    IS TABLE OF c1%ROWTYPE INDEX BY BINARY_INTEGER; 

    idxinfo    c1%ROWTYPE; 
    idxinfolist IDXINFOREC; 
    counter INTEGER; 

BEGIN
    counter := 0; 

    OPEN c1;

    LOOP 
        FETCH c1 INTO idxinfo; 

        IF c1%FOUND THEN 
          counter := counter + 1; 
        END IF; 

        idxinfolist(counter) := idxinfo; 

        IF c1%NOTFOUND THEN 
          EXIT; 
        END IF; 
    END LOOP; 

    CLOSE c1; 

    --1.unusable索引
    FOR i IN 1..counter LOOP 
        execute immediate 'ALTER INDEX ' || idxinfolist(i).index_name || ' unusable';
    END LOOP;

    --2.中间执行更新任务
    -- update something
    COMMIT;

    --3.重建索引
    FOR i IN 1..counter LOOP 
        IF idxinfolist(i).PARTITIONED = 'NO' then
            execute immediate 'ALTER INDEX ' || idxinfolist(i).index_name || ' rebuild parallel nologging';

        ELSE 
            IF idxinfolist(i).PARTITIONED = 'YES' then
                FOR x IN (
                            SELECT PARTITION_NAME
                            FROM user_IND_PARTITIONS
                            WHERE index_name = idxinfolist(i).index_name
                            ORDER BY PARTITION_POSITION
                            ) LOOP

                        execute immediate 'ALTER INDEX ' || idxinfolist(i).index_name || ' rebuild PARTITION ' || x.PARTITION_NAME || ' parallel nologging';
                END LOOP;
            END IF;
        END IF;
        execute immediate 'ALTER INDEX ' || idxinfolist(i).index_name || ' noparallel';
    END LOOP;
END;