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

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;

출처 :

http://www.dator.co.kr/?mid=textyle&category=2629&vid=zero&document_srl=43751

김상훈 - DBA & TUNING & Developer

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

티스토리툴바

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

'DB/__Oracle' Related Articles

티스토리툴바