데이터베이스 질문과 답변
안녕하세요.
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로 추가 공부해 보겠습니다...
) 버튼을 클릭하여 작성 하시면 됩니다.