ETL 유틸리티 패키지

예전 프로젝트에서 간단한 ETL 유틸리티 패키지를 작성했다.

패키지 사용법은 아래와 같다. 적재 전에 prc_begin_etl 프로시저를 수행하고, 적재 후에 prc_end_etl 프로시저를 수행하면 된다. 병렬도는 세그먼트 사이즈에 따라 자동으로 설정된다.

-- 1
fnc_get_partition_name    (i_owner, i_table_name, i_partition_key_value);
fnc_get_subpartition_name (i_owner, i_table_name, i_subpartition_key_value);

-- 2
prc_truncate_table        (i_owner, i_table_name);
prc_truncate_partition    (i_owner, i_table_name, i_partition_name);
prc_truncate_subpartition (i_owner, i_table_name, i_subpartition_name);

-- 3
prc_begin_etl (i_owner, i_table_name, i_partition_name);
    - prc_nologging_table    (i_owner, i_table_name);
    - prc_disable_constraint (i_owner, i_table_name);
    - prc_unusable_index     (i_owner, i_table_name, i_partition_name);

-- 4
INSERT

-- 5
prc_end_etl (i_owner, i_table_name, i_partition_name);
    - prc_rebuild_index     (i_owner, i_table_name, i_partition_name);
    - prc_enable_constraint (i_owner, i_table_name);
    - prc_logging_table     (i_owner, i_table_name);

-- 6
prc_gather_table_stats (i_owner, i_table_name, i_partition_name);

패키지 소스는 아래와 같다. 테스트 환경에서 검증 후 사용해야 한다.

CREATE OR REPLACE PACKAGE pkg_utl_etl
IS
    --------------------------------------------------
    -- pkg_utl_etl
    --------------------------------------------------
    FUNCTION fnc_get_partition_name (
        i_owner               IN VARCHAR2
      , i_table_name          IN VARCHAR2
      , i_partition_key_value IN VARCHAR2
    )
        RETURN VARCHAR2;

    FUNCTION fnc_get_subpartition_name (
        i_owner                  IN VARCHAR2
      , i_table_name             IN VARCHAR2
      , i_subpartition_key_value IN VARCHAR2
    )
        RETURN VARCHAR2;

    PROCEDURE prc_begin_etl (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
    );

    PROCEDURE prc_end_etl (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
    );

    PROCEDURE prc_nologging_table (
        i_owner      IN VARCHAR2
      , i_table_name IN VARCHAR2
    );

    PROCEDURE prc_logging_table (
        i_owner      IN VARCHAR2
      , i_table_name IN VARCHAR2
    );

    PROCEDURE prc_disable_constraint (
        i_owner      IN VARCHAR2
      , i_table_name IN VARCHAR2
    );

    PROCEDURE prc_enable_constraint (
        i_owner      IN VARCHAR2
      , i_table_name IN VARCHAR2
    );

    PROCEDURE prc_unusable_index (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
    );

    PROCEDURE prc_rebuild_index (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
    );

    PROCEDURE prc_truncate_table (
        i_owner      IN VARCHAR2
      , i_table_name IN VARCHAR2
    );

    PROCEDURE prc_truncate_partition (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2
    );

    PROCEDURE prc_truncate_subpartition (
        i_owner             IN VARCHAR2
      , i_table_name        IN VARCHAR2
      , i_subpartition_name IN VARCHAR2
    );

    PROCEDURE prc_gather_table_stats (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
      , i_granularity    IN VARCHAR2 DEFAULT DBMS_STATS.DEFAULT_GRANULARITY
    );
END pkg_utl_etl;
/

CREATE OR REPLACE PACKAGE BODY pkg_utl_etl
IS
    --------------------------------------------------
    -- fnc_get_partition_name
    --------------------------------------------------
    FUNCTION fnc_get_partition_name (
        i_owner               IN VARCHAR2
      , i_table_name          IN VARCHAR2
      , i_partition_key_value IN VARCHAR2
    )
        RETURN VARCHAR2
    IS
        l_sql_text           VARCHAR2(32767);
        l_partition_position dba_tab_partitions.partition_position%TYPE;
        l_partition_name     dba_tab_partitions.partition_name%TYPE;
    BEGIN
        l_sql_text := 'SELECT TBL$OR$IDX$PART$NUM ({OWNER}.{TABLE_NAME}, 0, 1, 0, {PARTITION_KEY_VALUE}) FROM DUAL';
        l_sql_text := REPLACE (l_sql_text, '{OWNER}', i_owner);
        l_sql_text := REPLACE (l_sql_text, '{TABLE_NAME}', i_table_name);
        l_sql_text := REPLACE (l_sql_text, '{PARTITION_KEY_VALUE}', i_partition_key_value);

        EXECUTE IMMEDIATE l_sql_text INTO l_partition_position;

        SELECT partition_name
          INTO l_partition_name
          FROM dba_tab_partitions
         WHERE table_owner = i_owner
           AND table_name = i_table_name
           AND partition_position = l_partition_position;

        RETURN l_partition_name;
    EXCEPTION
        WHEN OTHERS THEN RETURN 'NONE';
    END fnc_get_partition_name;

    --------------------------------------------------
    -- fnc_get_subpartition_name
    --------------------------------------------------
    FUNCTION fnc_get_subpartition_name (
        i_owner                  IN VARCHAR2
      , i_table_name             IN VARCHAR2
      , i_subpartition_key_value IN VARCHAR2
    )
        RETURN VARCHAR2
    IS
        l_sql_text       VARCHAR2(32767);
        l_object_id      dba_objects.object_id%TYPE;
        l_subobject_name dba_objects.subobject_name%TYPE;
    BEGIN
        l_sql_text := 'SELECT TBL$OR$IDX$PART$NUM ({OWNER}.{TABLE_NAME}, 0, 3, 0, {SUBPARTITION_KEY_VALUE}) FROM DUAL';
        l_sql_text := REPLACE (l_sql_text, '{OWNER}', i_owner);
        l_sql_text := REPLACE (l_sql_text, '{TABLE_NAME}', i_table_name);
        l_sql_text := REPLACE (l_sql_text, '{SUBPARTITION_KEY_VALUE}', i_subpartition_key_value);

        EXECUTE IMMEDIATE l_sql_text INTO l_object_id;

        SELECT subobject_name
          INTO l_subobject_name
          FROM dba_objects
         WHERE object_id = l_object_id;

        RETURN l_subobject_name;
    EXCEPTION
        WHEN OTHERS THEN RETURN 'NONE';
    END fnc_get_subpartition_name;

    --------------------------------------------------
    -- fnc_get_degree
    --------------------------------------------------
    FUNCTION fnc_get_degree (
        i_owner          IN VARCHAR2
      , i_segment_name   IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
    )
        RETURN NUMBER
    IS
        l_bytes dba_segments.bytes%TYPE;
    BEGIN
        IF i_partition_name IS NULL
        THEN
            SELECT SUM (bytes)
              INTO l_bytes
              FROM dba_segments
             WHERE owner = i_owner
               AND segment_name = i_segment_name;
        ELSE
            SELECT bytes
              INTO l_bytes
              FROM dba_segments
             WHERE owner = i_owner
               AND segment_name = i_segment_name
               AND partition_name = i_partition_name;
        END IF;

        RETURN GREATEST (LEAST (CEIL (l_bytes / POWER (1024, 3)), 32), 4); -- !
    EXCEPTION
        WHEN OTHERS THEN RETURN 2;
    END fnc_get_degree;

    --------------------------------------------------
    -- prc_begin_etl
    --------------------------------------------------
    PROCEDURE prc_begin_etl (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
    )
    IS
    BEGIN
        prc_nologging_table    (i_owner, i_table_name);
        prc_disable_constraint (i_owner, i_table_name);
        prc_unusable_index     (i_owner, i_table_name, i_partition_name);
    END prc_begin_etl;

    --------------------------------------------------
    -- prc_end_etl
    --------------------------------------------------
    PROCEDURE prc_end_etl (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
    )
    IS
    BEGIN
        prc_rebuild_index     (i_owner, i_table_name, i_partition_name);
        prc_enable_constraint (i_owner, i_table_name);
        prc_logging_table     (i_owner, i_table_name);
    END prc_end_etl;

    --------------------------------------------------
    -- prc_nologging_table
    --------------------------------------------------
    PROCEDURE prc_nologging_table (
        i_owner      IN VARCHAR2
      , i_table_name IN VARCHAR2
    )
    IS
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || i_owner || '.' || i_table_name || ' NOLOGGING';
    END prc_nologging_table;

    --------------------------------------------------
    -- prc_logging_table
    --------------------------------------------------
    PROCEDURE prc_logging_table (
        i_owner      IN VARCHAR2
      , i_table_name IN VARCHAR2
    )
    IS
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || i_owner || '.' || i_table_name || ' LOGGING';
    END prc_logging_table;

    --------------------------------------------------
    -- prc_disable_constraint
    --------------------------------------------------
    PROCEDURE prc_disable_constraint (
        i_owner      IN VARCHAR2
      , i_table_name IN VARCHAR2
    )
    IS
    BEGIN
        FOR f IN (SELECT constraint_name
                    FROM dba_constraints
                   WHERE owner = i_owner
                     AND table_name = i_table_name
                     AND constraint_type IN ('P', 'U')
                     AND status = 'ENABLED')
        LOOP
            EXECUTE IMMEDIATE 'ALTER TABLE ' || i_owner || '.' || i_table_name || ' MODIFY CONSTRAINT ' || f.constraint_name || ' DISABLE KEEP INDEX';
        END LOOP;
    END prc_disable_constraint;

    --------------------------------------------------
    -- prc_enable_constraint
    --------------------------------------------------
    PROCEDURE prc_enable_constraint (
        i_owner      IN VARCHAR2
      , i_table_name IN VARCHAR2
    )
    IS
    BEGIN
        FOR f IN (SELECT constraint_name
                    FROM dba_constraints
                   WHERE owner = i_owner
                     AND table_name = i_table_name
                     AND constraint_type IN ('P', 'U')
                     AND status = 'DISABLED')
        LOOP
            EXECUTE IMMEDIATE 'ALTER TABLE ' || i_owner || '.' || i_table_name || ' MODIFY CONSTRAINT ' || f.constraint_name || ' ENABLE';
        END LOOP;
    END prc_enable_constraint;

    --------------------------------------------------
    -- prc_unusable_index
    --------------------------------------------------
    PROCEDURE prc_unusable_index (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
    )
    IS
    BEGIN
        FOR f IN (SELECT owner
                       , index_name
                    FROM dba_indexes
                   WHERE table_owner = i_owner
                     AND table_name = i_table_name
                     AND status = 'VALID')
        LOOP
            EXECUTE IMMEDIATE 'ALTER INDEX ' || f.owner || '.' || f.index_name || ' UNUSABLE';
        END LOOP;

        FOR f IN (SELECT a.owner
                       , a.index_name
                       , b.partition_name
                    FROM dba_part_indexes a
                       , dba_ind_partitions b
                   WHERE a.owner = i_owner
                     AND a.table_name = i_table_name
                     AND a.subpartitioning_type = 'NONE'
                     AND a.locality = 'LOCAL'
                     AND b.index_owner = a.owner
                     AND b.index_name = a.index_name
                     AND b.partition_name = NVL (i_partition_name, partition_name)
                     AND b.status = 'USABLE')
        LOOP
            EXECUTE IMMEDIATE 'ALTER INDEX ' || f.owner || '.' || f.index_name || ' MODIFY PARTITION ' || f.partition_name || ' UNUSABLE';
        END LOOP;

        FOR f IN (SELECT a.owner
                       , a.index_name
                       , b.subpartition_name
                    FROM dba_part_indexes a
                       , dba_ind_subpartitions b
                   WHERE a.owner = i_owner
                     AND a.table_name = i_table_name
                     AND a.subpartitioning_type <> 'NONE'
                     AND a.locality = 'LOCAL'
                     AND b.index_owner = a.owner
                     AND b.index_name = a.index_name
                     AND b.subpartition_name = NVL (i_partition_name, subpartition_name)
                     AND b.status = 'USABLE')
        LOOP
            EXECUTE IMMEDIATE 'ALTER INDEX ' || f.owner || '.' || f.index_name || ' MODIFY SUBPARTITION ' || f.subpartition_name || ' UNUSABLE';
        END LOOP;
    END prc_unusable_index;

    --------------------------------------------------
    -- prc_rebuild_index
    --------------------------------------------------
    PROCEDURE prc_rebuild_index (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
    )
    IS
        l_degree NUMBER;
    BEGIN
        l_degree := fnc_get_degree (i_owner, i_table_name);

        FOR f IN (SELECT owner
                       , index_name
                    FROM dba_indexes
                   WHERE table_owner = i_owner
                     AND table_name = i_table_name
                     AND status = 'UNUSABLE')
        LOOP
            EXECUTE IMMEDIATE 'ALTER INDEX ' || f.owner || '.' || f.index_name || ' REBUILD NOLOGGING PARALLEL ' || l_degree;
            EXECUTE IMMEDIATE 'ALTER INDEX ' || f.owner || '.' || f.index_name || ' LOGGING NOPARALLEL';
        END LOOP;

        FOR f IN (SELECT a.owner
                       , a.index_name
                       , b.partition_name
                    FROM dba_part_indexes a
                       , dba_ind_partitions b
                   WHERE a.owner = i_owner
                     AND a.table_name = i_table_name
                     AND a.subpartitioning_type = 'NONE'
                     AND a.locality = 'LOCAL'
                     AND b.index_owner = a.owner
                     AND b.index_name = a.index_name
                     AND b.partition_name = NVL (i_partition_name, partition_name)
                     AND b.status = 'UNUSABLE')
        LOOP
            l_degree := fnc_get_degree (i_owner, i_table_name, f.partition_name);
            EXECUTE IMMEDIATE 'ALTER INDEX ' || f.owner || '.' || f.index_name || ' REBUILD PARTITION ' || f.partition_name || ' NOLOGGING PARALLEL ' || l_degree;
            EXECUTE IMMEDIATE 'ALTER INDEX ' || f.owner || '.' || f.index_name || ' MODIFY  PARTITION ' || f.partition_name || ' LOGGING';
        END LOOP;

        FOR f IN (SELECT a.owner
                       , a.index_name
                       , b.subpartition_name
                    FROM dba_part_indexes a
                       , dba_ind_subpartitions b
                   WHERE a.owner = i_owner
                     AND a.table_name = i_table_name
                     AND a.subpartitioning_type <> 'NONE'
                     AND a.locality = 'LOCAL'
                     AND b.index_owner = a.owner
                     AND b.index_name = a.index_name
                     AND b.subpartition_name = NVL (i_partition_name, subpartition_name)
                     AND b.status = 'UNUSABLE')
        LOOP
            l_degree := fnc_get_degree (i_owner, i_table_name, f.subpartition_name);
            EXECUTE IMMEDIATE 'ALTER INDEX ' || f.owner || '.' || f.index_name || ' REBUILD SUBPARTITION ' || f.subpartition_name || ' NOLOGGING PARALLEL ' || l_degree;
            EXECUTE IMMEDIATE 'ALTER INDEX ' || f.owner || '.' || f.index_name || ' MODIFY  SUBPARTITION ' || f.subpartition_name || ' LOGGING';
        END LOOP;
    END prc_rebuild_index;

    --------------------------------------------------
    -- prc_truncate_table
    --------------------------------------------------
    PROCEDURE prc_truncate_table (
        i_owner             IN VARCHAR2
      , i_table_name        IN VARCHAR2
    )
    IS
    BEGIN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i_owner || '.' || i_table_name || ' DROP ALL STORAGE';
    END prc_truncate_table;

    --------------------------------------------------
    -- prc_truncate_partition
    --------------------------------------------------
    PROCEDURE prc_truncate_partition (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2
    )
    IS
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || i_owner || '.' || i_table_name || ' TRUNCATE PARTITION ' || i_partition_name || ' DROP ALL STORAGE';
    END prc_truncate_partition;

    --------------------------------------------------
    -- prc_truncate_subpartition
    --------------------------------------------------
    PROCEDURE prc_truncate_subpartition (
        i_owner             IN VARCHAR2
      , i_table_name        IN VARCHAR2
      , i_subpartition_name IN VARCHAR2
    )
    IS
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || i_owner || '.' || i_table_name || ' TRUNCATE SUBPARTITION ' || i_subpartition_name || ' DROP ALL STORAGE';
    END prc_truncate_subpartition;

    --------------------------------------------------
    -- prc_gather_table_stats
    --------------------------------------------------
    PROCEDURE prc_gather_table_stats (
        i_owner          IN VARCHAR2
      , i_table_name     IN VARCHAR2
      , i_partition_name IN VARCHAR2 DEFAULT NULL
      , i_granularity    IN VARCHAR2 DEFAULT DBMS_STATS.DEFAULT_GRANULARITY
    )
    IS
        l_degree NUMBER;
    BEGIN
        l_degree := fnc_get_degree (i_owner, i_table_name, i_partition_name);

        CASE
            WHEN i_partition_name IS NULL
            THEN DBMS_STATS.GATHER_TABLE_STATS (ownname => i_owner, tabname => i_table_name, degree => l_degree);
            ELSE DBMS_STATS.GATHER_TABLE_STATS (ownname => i_owner, tabname => i_table_name, partname => i_partition_name, degree => l_degree, granularity => i_granularity);
        END CASE;
    END prc_gather_table_stats;
END pkg_utl_etl;
/