꿈꾸는 개발자, DBA 커뮤니티 구루비

용량 분할 SQL 조언 구합니다.

by 사랑초 [SQL Query] [2020.03.09 14:14:14]

안녕하세요.
DBA_SEGMENTS 목록에서 조회되는 오브젝트들에 대해 
계정별 오브젝트별로 특정 용량 그룹별로 분할하고자 합니다.

* 아래 2개 SQL 외 더 좋은 방법이 있을지요?

* 첫번째 SQL의 MODEL 절 내 "31015" 값을
백분율에 해당하는 SQL의 "SIZE_MB" 결과값으로 적용이 가능할까요?
(만약 가능하다면 가이드 부탁 드립니다.)

WITH TMP AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY OWNER, SIZE_MB DESC) SEQ
    , OWNER
    , TBL_NM
    , SIZE_MB
    FROM
    (
              SELECT 'TEST'  AS OWNER, 'IF_TMP_VOC_DATA'   AS TBL_NM, 126144 AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST'  AS OWNER, 'CRWL_DOC'          AS TBL_NM, 31872  AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST'  AS OWNER, 'IF_TEST_VOC_DATA'  AS TBL_NM, 27392  AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST'  AS OWNER, 'IF_ONLY_RECEIPT'   AS TBL_NM, 19520  AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST'  AS OWNER, 'ASDF_MMEDIA'       AS TBL_NM, 6848   AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST'  AS OWNER, 'ASDF_MMEDIA123'    AS TBL_NM, 77102  AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST1' AS OWNER, 'DOC_ASDF'          AS TBL_NM, 28147  AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST1' AS OWNER, 'IF_TEST_VOC_DATA'  AS TBL_NM, 27329  AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST1' AS OWNER, 'IF_DATA_ONLY'      AS TBL_NM, 15520  AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST1' AS OWNER, 'QWER_MMEDIA'       AS TBL_NM, 3848   AS SIZE_MB  FROM DUAL
    UNION ALL SELECT 'TEST1' AS OWNER, 'QWER_MMEDIA123'    AS TBL_NM, 72102  AS SIZE_MB  FROM DUAL
    )
)
SELECT OWNER, TBL_NM, SIZE_MB
, RANK() OVER(PARTITION BY OWNER ORDER BY RS2 ASC) AS RN
FROM
(
    SELECT  OWNER, TBL_NM, SEQ, SIZE_MB, RS2
    FROM    TMP
    MODEL
    DIMENSION BY (SEQ)
    MEASURES(OWNER, TBL_NM, SEQ ASEQ, 0 RSEQ, SIZE_MB, 0 RS, 0 RS2)
    IGNORE NAV
    RULES AUTOMATIC ORDER
    (
        RS[SEQ]     = CASE WHEN RS[CV()-1]+SIZE_MB[CV()] > 31015 THEN SIZE_MB[CV()]       ELSE RS[CV()-1]+SIZE_MB[CV()] END
      , RSEQ[SEQ]   = CASE WHEN RS[CV()-1]+SIZE_MB[CV()] > 31015 THEN ASEQ[CV()]          ELSE RSEQ[CV()-1]             END
      , RS2[SEQ]    = CASE WHEN RS[CV()-1]+SIZE_MB[CV()] > 31015 THEN RSEQ[CV()]          ELSE RSEQ[CV()-1]             END
    )
);

SELECT
    OWNER,
    MAX(SIZE_MB) AS SIZE_MB
FROM
(
	SELECT
		  OWNER
		, ROUND(SUM(BYTES)/1024/1024) AS SIZE_MB
		, SEGMENT_NAME AS TBL_NM
		, ROUND(RATIO_TO_REPORT(SUM(BYTES)/1024/1024) OVER(PARTITION BY OWNER),2)*100 AS PER
	FROM DBA_SEGMENTS
	WHERE OWNER IN ('TEST','TEST1')
	  AND SEGMENT_TYPE LIKE 'TABLE%'
	GROUP BY OWNER, SEGMENT_NAME
	HAVING SUM(BYTES)/1024/1024 >= 1
	ORDER BY 2 DESC
)
WHERE PER BETWEEN 7 AND 11
GROUP BY OWNER;

감사합니다.

by 마농 [2020.03.09 14:52:11]

1. 두 결과를 연결시키려면 -> 2번 결과를 1번에 조인하면 됩니다.
2. 버전이 11G 라면? -> Model 보다는 Recursive SQL 을 사용하시는 것이 편할 듯 합니다.
3. 기존 쿼리가 좀 틀린 듯 합니다. -> 유저별 개별 처리를 해야 맞을 듯 합니다.

by 사랑초 [2020.03.09 15:40:20]

시간내서 답변해주셔서 감사드립니다~

DB 버전은 oracle 11gR2 입니다~

Recursive SQL 관련하여 혹시 조금 더 상세하게 안내 가능하실까요...?

(만약 시간이 되신다면 재귀SQL 또는 Recursive SQL 에 대해 대략적인 가이드에 대해 알려주실수 있으실까요....

조인으로 풀어봤는데 에러가 나서 막혔던 터라...)

by 마농 [2020.03.09 16:32:49]

WITH t1 AS
(
-- 테이블별 사이즈
SELECT 'TEST' owner, 'IF_TMP_VOC_DATA' tbl_nm, 126144 size_mb FROM dual
UNION ALL SELECT 'TEST' , 'CRWL_DOC'        , 31872 FROM dual
UNION ALL SELECT 'TEST' , 'IF_TEST_VOC_DATA', 27392 FROM dual
UNION ALL SELECT 'TEST' , 'IF_ONLY_RECEIPT' , 19520 FROM dual
UNION ALL SELECT 'TEST' , 'ASDF_MMEDIA'     ,  6848 FROM dual
UNION ALL SELECT 'TEST' , 'ASDF_MMEDIA123'  , 77102 FROM dual
UNION ALL SELECT 'TEST1', 'DOC_ASDF'        , 28147 FROM dual
UNION ALL SELECT 'TEST1', 'IF_TEST_VOC_DATA', 27329 FROM dual
UNION ALL SELECT 'TEST1', 'IF_DATA_ONLY'    , 15520 FROM dual
UNION ALL SELECT 'TEST1', 'QWER_MMEDIA'     ,  3848 FROM dual
UNION ALL SELECT 'TEST1', 'QWER_MMEDIA123'  , 72102 FROM dual
)
, t2 AS
(
-- 오너별 기준 사이즈
SELECT 'TEST' owner, 31015 size_base FROM dual
UNION ALL SELECT 'TEST1', 65432 FROM dual
)
, t3 AS
(
-- 1. 조인 및 순번 부여
SELECT b.owner, b.size_base
     , ROW_NUMBER() OVER(PARTITION BY a.owner ORDER BY a.size_mb DESC, a.tbl_nm) rn
     , a.tbl_nm, a.size_mb
  FROM t1 a
     , t2 b
 WHERE a.owner = b.owner
)
, t4(owner, size_base, rn, tbl_nm, size_mb, size_rem, rk) AS
(
-- 2. 재귀 쿼리
SELECT owner, size_base, rn, tbl_nm, size_mb
     , CASE WHEN size_mb < size_base THEN size_base - size_mb ELSE 0 END size_rem
     , 1 rk
  FROM t3
 WHERE rn = 1
 UNION ALL
SELECT a.owner, a.size_base, b.rn, b.tbl_nm, b.size_mb
     , CASE WHEN b.size_mb < a.size_rem  THEN a.size_rem  - b.size_mb
            WHEN b.size_mb < a.size_base THEN a.size_base - b.size_mb
            ELSE 0 END size_rem
     , a.rk + CASE WHEN b.size_mb < a.size_rem THEN 0 ELSE 1 END rk
  FROM t4 a
     , t3 b
 WHERE b.owner = a.owner
   AND b.rn = a.rn + 1
)
SELECT *
  FROM t4
 ORDER BY owner, rn
;

by 사랑초 [2020.03.09 17:26:59]

바쁘실텐데 불구하고 상세 SQL로 답글 달아주셔서 감사합니다...
작성해 주신 SQL로 추가 공부해 보겠습니다...

데이터베이스 질문과 답변