DATOR


전체 Table Row Count 를 빠르게 구하는 방법 Data Migration


H모사에서 데이터 이행 종료후에 전체 테이블의 Row Count를 구하여 이행 완료 Check List에 기록하는 절차가 있었다.

통계 정보를 다시 갱신하는 것보다 더 빠르게 Count하는 방법이 무엇일까 고민하다가, 찾은 방법이다.

 

※ 요약

- 테이블 전체를 적절한 그룹으로 나누어 Program parallel 방식으로 Segment 크기에 따라 PK Index를 Parallel degree를 조절하여 Fast full scan으로 읽어 Count하고 결과를 테이블에 기록

 

※ 필요한 객체

1. 전체 테이블을 크기별로 그룹핑한 목록 (홀수번 그룹은 테이블 크기의 Ascending 정렬, 짝수번 그룹은 Descending 정렬) - Balancing SQL 이용

2. MIG_TAB_CNT_VRF_LOG 테이블: Count 결과 기록 용도

3. MIG_TAB_CNT_PRC 프로시져: 테이블 이름을 parameter로 받아서 실제 처리를 담당

 

※ 전제조건

- 테이블의 PK Index는 반드시 존재하며,  'XPK_'로 이름이 시작됨

 

※ Note.

- V_SRC_TAR_GB 변수는 H사의 환경에 특화된 변수이므로 제거하고 사용할 것-

- SQL과 Procedure는 상황에 맞게 변경할 것.

 

* Balancing SQL

-- Parameter :GRP_NUM ==> 나누고자 하는 Group의 갯수
-- 홀수 Group은 작은 테이블 부터, 짝수 Group은 큰 테이블부터 순번 부여
 
WITH
T AS
(
    SELECT /*+ MATERIALIZE */ OWNER, TABLE_NAME
      FROM ALL_TABLES
     WHERE OWNER IN ('NISADM', 'NISCDM', 'OPUSADM', 'PRIADM')
),
S AS
(
    SELECT /*+ MATERIALIZE */
           OWNER, SEGMENT_NAME, ROUND(SUM(BYTES)/1024/1024,0) MB
      FROM DBA_SEGMENTS
     WHERE OWNER IN ('NISADM', 'NISCDM', 'OPUSADM', 'PRIADM')
     GROUP BY OWNER, SEGMENT_NAME
)
SELECT OWNER, TABLE_NAME, MB, GRP_NUM,
       ROW_NUMBER() OVER(PARTITION BY GRP_NUM ORDER BY MB * DECODE(MOD(GRP_NUM, 2), 1, 1, -1)) GRP_NUM_SEQ
  FROM (
        SELECT /*+ ORDERED USE_HASH(T S) */
               T.OWNER, T.TABLE_NAME, S.SEGMENT_NAME PK_NAME, MB,
               MOD( ROW_NUMBER() OVER (ORDER BY MB NULLS LAST) , :GRP_NUM ) + 1 GRP_NUM
          FROM T LEFT OUTER JOIN S
                 ON (  T.OWNER = S.OWNER
                   --AND ( T.TABLE_NAME = REPLACE(S.SEGMENT_NAME, 'XPK', '') ) )
                   --AND ('XPK' || T.TABLE_NAME = S.SEGMENT_NAME OR T.TABLE_NAME = S.SEGMENT_NAME)  )
                   AND 'XPK' || T.TABLE_NAME = S.SEGMENT_NAME )
       )
 ORDER BY GRP_NUM, GRP_NUM_SEQ
;

 

*. MIG_TAB_CNT_PRC 프로시져

CREATE OR REPLACE PROCEDURE MIG_TAB_CNT_PRC
    ( I_WORK_SEQ    NUMBER,
      I_SYS_NM      VARCHAR2,
      I_ENT_NM      VARCHAR2,
      I_OWN_NM      VARCHAR2,
      I_TAB_NM      VARCHAR2,
      I_WORK_GRP_ID VARCHAR2 )
AUTHID DEFINER
IS
    V_DB_NAME       VARCHAR2(10);
    V_CNT_STMT      VARCHAR2(4000);
    V_COUNT         NUMBER(20);
    V_ERRMSG        VARCHAR2(1000);
    V_STRT_DT       DATE;
    V_SRC_TAR_GB    VARCHAR2(1);
BEGIN
    V_DB_NAME := SYS_CONTEXT ('USERENV', 'DB_NAME');
    V_COUNT := NULL;
    V_STRT_DT := SYSDATE;
 
    V_SRC_TAR_GB := 'T';
    IF I_OWN_NM = 'NISCDM' THEN
        V_SRC_TAR_GB := 'C';
    END IF;
 
    -- COUNT sql 생성. PK가 존재하면 INDEX_FFS 지정, Segment Size에 따라서 PARALLEL_INDEX degree 조정
    SELECT 'SELECT /*+ ' ||
           CASE WHEN SEGMENT_NAME LIKE 'XPK%' THEN 'INDEX_FFS(A ' || SEGMENT_NAME || ') '
                ELSE ''
           END ||
           CASE WHEN MB BETWEEN 0 AND 50 THEN ''
                WHEN MB BETWEEN 50 AND 500 THEN 'PARALLEL_INDEX(A ' || SEGMENT_NAME || ' 2)'
                WHEN MB > 10240 THEN 'PARALLEL_INDEX(A ' || SEGMENT_NAME || ' 8)'
                ELSE 'PARALLEL_INDEX(A ' || SEGMENT_NAME || ' 4)'
           END ||
           ' */ COUNT(*) CNT FROM ' || I_OWN_NM || '.' || I_TAB_NM || ' A' AS CNT_SCR
      INTO V_CNT_STMT
      FROM (
            SELECT SEGMENT_NAME, ROUND(BYTES/1024/1024, 0) MB,
                   ROW_NUMBER() OVER(ORDER BY DECODE(SUBSTR(SEGMENT_NAME, 1, 3), 'XPK', 0, 1) ) SEG_FILTER
              FROM (
                       SELECT OWNER, SEGMENT_NAME,
                              BYTES / CASE
                                          WHEN V_DB_NAME IN ('HJSALPSB', 'HJSDOM') THEN 1.75
                                          WHEN V_DB_NAME IN ('NIS2010T') THEN 4
                                          WHEN V_DB_NAME IN ('HJSBAT_T', 'HJSDOM_T') THEN 8
                                          ELSE 1
                                        END AS BYTES
                         FROM DBA_SEGMENTS S
                   )
             WHERE OWNER = UPPER(I_OWN_NM)
               AND ( SEGMENT_NAME = UPPER(I_TAB_NM)
                  OR SEGMENT_NAME = 'XPK' || UPPER(I_TAB_NM) )
           )
     WHERE SEG_FILTER = 1;
 
    DBMS_OUTPUT.PUT_LINE(V_CNT_STMT);
    EXECUTE IMMEDIATE V_CNT_STMT INTO V_COUNT;
 
    INSERT INTO MIG_TAB_CNT_VRF_LOG
            ( MIG_TAB_CNT_VRF_LOG_SEQ, WORK_SEQ, SYS_NM, TAR_ENT_NM, TAR_TAB_NM, SRC_TAR_GB,
              DB_NM, OWN_NM, TAB_NM, ROW_CNT, STRT_DT, FIN_DT, RSLT_GB, WORK_GRP_ID, LOG_MSG )
     VALUES ( MIG_TAB_CNT_VRF_LOG_SEQ.NEXTVAL, I_WORK_SEQ, I_SYS_NM, I_ENT_NM, I_TAB_NM, V_SRC_TAR_GB,
              V_DB_NAME, I_OWN_NM, I_TAB_NM, V_COUNT, V_STRT_DT, SYSDATE, 'C', I_WORK_GRP_ID, NULL);
 
    COMMIT;
 
    DBMS_OUTPUT.PUT_LINE(I_TAB_NM || ' : ' || TO_CHAR(V_COUNT));
EXCEPTION
    WHEN OTHERS THEN
        V_ERRMSG := SUBSTR(SQLERRM, 1, 1000);
        DBMS_OUTPUT.PUT_LINE(CHR(10) || V_ERRMSG);
 
        INSERT INTO MIG_TAB_CNT_VRF_LOG
                ( MIG_TAB_CNT_VRF_LOG_SEQ, WORK_SEQ, SYS_NM, TAR_ENT_NM, TAR_TAB_NM, SRC_TAR_GB,
                  DB_NM, OWN_NM, TAB_NM, ROW_CNT, STRT_DT, FIN_DT, RSLT_GB, WORK_GRP_ID, LOG_MSG )
         VALUES ( MIG_TAB_CNT_VRF_LOG_SEQ.NEXTVAL, I_WORK_SEQ, I_SYS_NM, I_ENT_NM, I_TAB_NM, V_SRC_TAR_GB,
                  V_DB_NAME, I_OWN_NM, I_TAB_NM, V_COUNT, V_STRT_DT, SYSDATE, 'E', I_WORK_GRP_ID, V_ERRMSG);
 
        COMMIT;
END;

 

TAG

Leave Comments


profile기술 너머의 통찰 

Recent Trackback