RANGE 파티션 통계정보 관리 패키지

RANGE 파티션 통계정보 이슈가 있어 통계정보를 관리할 수 있는 간단한 패키지를 작성했다.

• fnc_get_high_value   : ALL_TAB_PARTITIONS.HIGH_VALUE 값을 VARCHAR2 타입으로 반환
• fnc_get_part_name    : HIGH_VALUE에 따라 원본 파티션 명과 대상 파티션 명을 반환
• prc_gather_part_stats: 파티션의 통계정보를 수집
• prc_copy_part_stats  : 파티션은 통계정보를 복제

-- pkg_stats spec
CREATE OR REPLACE PACKAGE pkg_stats AUTHID CURRENT_USER
IS
    PROCEDURE prc_gather_part_stats (
        i_table_owner IN VARCHAR2
      , i_table_name  IN VARCHAR2
      , i_high_value  IN VARCHAR2
    );

    PROCEDURE prc_copy_part_stats (
        i_table_owner IN VARCHAR2
      , i_table_name  IN VARCHAR2
      , i_high_value  IN VARCHAR2
    );

    FUNCTION fnc_get_high_value (
        i_table_owner    IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2
    )
        RETURN VARCHAR2;
END pkg_stats;
/

-- pkg_stats body
CREATE OR REPLACE PACKAGE BODY pkg_stats
IS
    -- fnc_get_high_value
    FUNCTION fnc_get_high_value (
        i_table_owner    IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2
    )
        RETURN VARCHAR2
    IS
        l_high_value VARCHAR2 (32767);
    BEGIN
        SELECT high_value
          INTO l_high_value
          FROM all_tab_partitions
         WHERE table_owner = i_table_owner
           AND table_name = i_table_name
           AND partition_name = i_partition_name;

        l_high_value := SUBSTR (REPLACE (l_high_value, CHR (39)), 1, 4000);

        RETURN l_high_value;
    END fnc_get_high_value;

    -- fnc_get_part_name
    FUNCTION fnc_get_part_name (
        i_table_owner IN VARCHAR2,
        i_table_name  IN VARCHAR2,
        i_high_value  IN VARCHAR2,
        i_type        IN VARCHAR2
    )
        RETURN VARCHAR2
    IS
        l_part_name VARCHAR2 (30);
    BEGIN
        CASE i_type
            WHEN 'SRC'
            THEN
                SELECT partition_name
                  INTO l_part_name
                  FROM (SELECT partition_name
                             , ROW_NUMBER () OVER (ORDER BY high_value DESC) AS rn
                          FROM (SELECT partition_name
                                     , pkg_stats.fnc_get_high_value (
                                           table_owner, table_name, partition_name
                                       ) AS high_value
                                  FROM all_tab_partitions
                                 WHERE table_owner = i_table_owner
                                   AND table_name = i_table_name
                                   AND sample_size IS NOT NULL)
                         WHERE high_value <= i_high_value)
                 WHERE rn = 1;
            WHEN 'DST'
            THEN
                SELECT partition_name
                  INTO l_part_name
                  FROM (SELECT partition_name
                             , ROW_NUMBER () OVER (ORDER BY high_value) AS rn
                          FROM (SELECT partition_name
                                     , pkg_stats.fnc_get_high_value (
                                           table_owner, table_name, partition_name
                                       ) AS high_value
                                  FROM all_tab_partitions
                                 WHERE table_owner = i_table_owner
                                   AND table_name = i_table_name)
                         WHERE high_value > i_high_value)
                 WHERE rn = 1;
            ELSE
                NULL;
        END CASE;

        RETURN l_part_name;
    END fnc_get_part_name;

    -- prc_gather_part_stats
    PROCEDURE prc_gather_part_stats (
        i_table_owner IN  VARCHAR2
      , i_table_name  IN VARCHAR2
      , i_high_value  IN VARCHAR2
    ) 
    IS
        l_part_name VARCHAR (30);
    BEGIN
        l_part_name := fnc_get_part_name (i_table_owner, i_table_name, i_high_value, 'DST');

        DBMS_STATS.gather_table_stats (
            ownname  => i_table_owner
          , tabname  => i_table_name
          , partname => l_part_name
          , force    => TRUE
        );
    EXCEPTION
        WHEN OTHERS
        THEN
            RAISE_APPLICATION_ERROR (-20000, 'ERROR');
    END prc_gather_part_stats;

    -- prc_copy_part_stats
    PROCEDURE prc_copy_part_stats (
        i_table_owner IN VARCHAR2
      , i_table_name  IN VARCHAR2
      , i_high_value  IN VARCHAR2
    )
    IS
        l_part_name_src VARCHAR (30);
        l_part_name_dst VARCHAR (30);
    BEGIN
        l_part_name_src := fnc_get_part_name (i_table_owner, i_table_name, i_high_value, 'SRC');
        l_part_name_dst := fnc_get_part_name (i_table_owner, i_table_name, i_high_value, 'DST');

        DBMS_STATS.COPY_TABLE_STATS (
            ownname     => i_table_owner
          , tabname     => i_table_name
          , srcpartname => l_part_name_src
          , dstpartname => l_part_name_dst
          , force       => TRUE
        );
    EXCEPTION
        WHEN OTHERS
        THEN
            RAISE_APPLICATION_ERROR (-20000, 'ERROR');
    END prc_copy_part_stats;
END pkg_stats;
/