데이터베이스 질문과 답변
by 커피향 [2014.08.06 13:36:38]
WITH ATABLE AS
(
SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 1 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 2 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 3 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 4 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 5 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 6 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 7 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 8 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 9 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 10 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 11 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 12 SEQ, 1 HVAL from dual
--중략
UNION ALL SELECT '001' vessel,'100002' CREW, '20140131'YMD, '20140131-1' YMDSEQ, 1 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140131'YMD, '20140131-1' YMDSEQ, 2 SEQ, 0 HVAL from dual
)
-- index (vessel, crew, YMDSEQ , seq)
-- 20140101-1 행이 평균 48 행 * 30 (1개월) = 1440 (2014010 ~ 20140131 CREW당 한달간 누적되는 평균 레코드 )
-- 얼마전에 OUTER JOIN 을 해서 질문했는데요
-- OUTER JOIN 을 빼도 속도에 영향은 안 미치는 군요
-- GROUP BY , ORDER BY 하면 속도가 빡 떨어지네요
SELECT YMDSEQ
, MAX(REST6) REST6 ,
MAX(WORK14) WORK14
FROM (SELECT YMDSEQ
, YMD
, NVL(LENGTH(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
wm_concat(NVL(HVAL,0)) OVER(ORDER BY YMDSEQ DESC, SEQ DESC
ROWS BETWEEN CURRENT ROW AND 47 FOLLOWING)
, ',')
, '0{6,}', 2)
, '[^2]')
), 0) REST6
, NVL(LENGTH(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
wm_concat(NVL(HVAL,0)) OVER(ORDER BY YMDSEQ DESC, SEQ DESC
ROWS BETWEEN CURRENT ROW AND 47 FOLLOWING)
, ',')
, '1{14,}', 2)
, '[^2]')
), 0) work14
FROM ATABLE
WHERE vessel = '001'
and CREW = '100002'
AND YMD >= '20131227'
AND YMD <= '20140131'
) S
WHERE YMD >= '20140101'
AND YMD <= '20140131'
GROUP BY YMDSEQ
ORDER BY 1
아래 결과값이 나와야 합니다.
연속으로 일한 시간을 체크하려고합니다.
| YMDSEQ | REST6 | WORK14 |
| 20140101-1 | 1 | 0 |
| 20140131-1 | 1 | 0 |
답변을 부탁드립니다.
1. YMD 에는 인덱스가 없네요.
조건을 YMD 에 걸지 마시고 YMDSEQ 에 거세요.
AND YMDSEQ >= '20131227'
AND YMDSEQ <= '20140131'||'z'
2. NVL(HVAL,0) 에서 NVL 필요한건가요?
불필요한 거는 최대한 빼세요.
3. wm_concat 를 두번 하는데
한번 해서 가져온후 밖에서 Replace 하세요.
4. 지난번에 조인이 있었는데 이번엔 없네요?
조인이 없어도 결과가 동일한건가요?
5. 겨우 1440 건 Group By 하는데 속도가 확 떨어지는게 이해가 안가네요.
조회결과를 임시테이블로 만드신후 테스트해보세요.
- 빠르다면? ==> SQL 문제이구요
- 느리다면? ==> 그룹바이 자체 문제네요.
1. AND YMDSEQ >= '20131227'
AND YMDSEQ <= '20140131'||'z' 해도 별차이가 없네요
2. NVL(HVAL,0) 에서 NVL 빼습니다.
3. wm_concat 하나는 HVAL = 0 인거 연속 6개
또 다른 하나는 HVAL = 1 인거 연속 14개 인거 입니다.
4. 조인이 없어도 검색속도는 별차이 없습니다.
5. 하위질의 S 안에 검색된 레코드를 임시테이블로 만들어서
그 임시 테이블를 GROUP BY 하면 빠릅니다.
GROUP BY 하더라도 YMDSEQ 칼럼만 검색하고
MAX(REST6) REST6 , MAX(WORK14) WORK14 <---를 빼버리는 속도가 엄청 빠릅니다.
이유가 뭘까요.? table.xls 를 첨부합니다.
부탁드리겠습니다.
1. AND YMDSEQ >= '20131227'
AND YMDSEQ <= '20140131'||'z' 해도 별차이가 없네요
==> 실행계획을 확인하고 싶네요.
2. NVL(HVAL,0) 에서 NVL 빼습니다.
3. wm_concat 하나는 HVAL = 0 인거 연속 6개
또 다른 하나는 HVAL = 1 인거 연속 14개 인거 입니다.
==> 어차피 wm_concat 구문은 동일합니다.
==> 그 이후 구문만 달라지는 거죠.
==> 동일한 행위를 두번 할 필요는 없죠.
4. 조인이 없어도 검색속도는 별차이 없습니다.
==> 검색속도 차이질문이 아니었구요
==> 검색결과가 똑같은지에 대한 질문이었습니다.
==> 속도만 빨라진다고 다가 아니죠. 결과가 정확해야죠.
5. 하위질의 S 안에 검색된 레코드를 임시테이블로 만들어서
그 임시 테이블를 GROUP BY 하면 빠릅니다.
==> 단순 그룹바이 문제가 아니네요. 전반적으로 SQL 을 검토해야죠.
==> 실행계획을 확인하고 싶네요.
SELECT ymdseq
, NVL(MAX(LENGTH(REPLACE(x, 'W'))), 0) r6
, NVL(MAX(LENGTH(REPLACE(x, 'R'))), 0) w14
FROM (SELECT ymdseq
--, vessel, crew, ymd, seq
, REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
wm_concat(hval) OVER(ORDER BY ymdseq, seq
ROWS BETWEEN 47 PRECEDING AND CURRENT ROW
), ',')
, '0{6,}' , 'R')
, '1{14,}', 'W')
, '[^RW]')
AS x
FROM atable
WHERE vessel = '001'
AND crew = '100002'
AND ymdseq >= '20131227'
AND ymdseq <= '20140131'||'z'
)
WHERE ymdseq >= '20140101'
AND ymdseq <= '20140131'||'z'
GROUP BY ymdseq
ORDER BY ymdseq
;
table.xls 을 첨부하였습니다. 데이터 파일
부탁드리겠습니다.
데이터는 의미 없구요.
실행계획을 주세요.
그룹바이 전, 후
실행계획 그룹자이 전, 후 올렸습니다.
1. 인덱스 안타네요? ==> 이건 왜죠?
2. byte 가 갑짜기 훅 뛰네요. ==> 이해가 안가네요..
3. Group By 결과 건수가 줄지를 않네요? ==> 31건으로 줄어야 맞는데요?
비슷하게 테스트 해봐도 느릴 이유가 전혀 없어 보이네요.
글쵸
, NVL(MAX(LENGTH(REPLACE(x, 'W'))), 0) r6 <--- 제외시키면, NVL(MAX(LENGTH(REPLACE(x, 'R'))), 0) w14 <--- 제외시키면위를 검색하지 않으면 bYTES : 24,514 이 나옵니다.
Card 는 31로 줄어드나요?
WITH t AS
(
SELECT /*+ materialize */
ymdseq
--, vessel, crew, ymd, seq
, REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
wm_concat(hval) OVER(ORDER BY ymdseq, seq
ROWS BETWEEN 47 PRECEDING AND CURRENT ROW
), ',')
, '0{6,}' , 'R')
, '1{14,}', 'W')
, '[^RW]')
AS x
FROM atable
WHERE vessel = '001'
AND crew = '100002'
AND ymdseq >= '20131227'
AND ymdseq <= '20140131'||'z'
)
SELECT ymdseq
, NVL(LENGTH(MAX(REPLACE(x, 'W'))), 0) r6
, NVL(LENGTH(MAX(REPLACE(x, 'R'))), 0) w14
FROM t
WHERE ymdseq >= '20140101'
AND ymdseq <= '20140131'||'z'
GROUP BY ymdseq
ORDER BY ymdseq
;
이유는 잘 모르겟으나 위와 같이 한번 시도해 보세요.
인라인뷰를 WITH 로 끌어 올리고 materialize 힌트를 주어
템프테이블로 동작하게끔 하는 것입니다.
, NVL(MAX(LENGTH(REPLACE(x, 'W'))), 0) r6 <--- 제외시키면
, NVL(MAX(LENGTH(REPLACE(x, 'R'))), 0) w14 <--- 제외시키면
실행계획 파일 올렸습니다.
1. 오히려 Byte 가 줄었네요?
2. Card 가 31 이 아닌 1442 가 나오네요?
둘 다 이해가 안가네요.
--말씀중에 제가 끼어드는것 같네요.
--원인은 group by가 아니라 wm_concat의 window절내의 범위처리 때문이 아닐까 생각되네요.
--그냥 window 범위 없이 구한 다음 잘라서 처리 하게 해봤습니다
--마농님 sql 도용
select ymdseq
, nvl(max(length(replace(x, 'w'))), 0) r6
, nvl(max(length(replace(x, 'r'))), 0) w14
from (select ymdseq
--, vessel, crew, ymd, seq
, regexp_replace(
regexp_replace(
regexp_replace(
substr(replace(wm_concat(hval) over(order by ymdseq, seq), ','),case when rnum > 47 then rnum - 47 else 0 end) -->수정부분(맞게 한지는 ^^;;)
, '0{6,}' , 'r')
, '1{14,}', 'w')
, '[^rw]')
as x
from (select row_number() over(order by ymdseq, seq) rnum, a.*
from atable a
where vessel = '001'
and crew = '100002'
and ymdseq >= '20131227'
and ymdseq <= '20140131'||'z')
)
where ymdseq >= '20140101'
and ymdseq <= '20140131'||'z'
group by ymdseq
order by ymdseq
;
-- 백면서생님 좋은 생각인 듯 합니다.
-- 혹시라도 Window 절 때문에 느린거라면?
-- 다음과 같이 하면 되겠습니다.
-- 처음부터 이렇게 했으면 구문도 더 간단해지네요.
SELECT ymdseq
, NVL(MAX(LENGTH(REPLACE(x, 'W'))), 0) r6
, NVL(MAX(LENGTH(REPLACE(x, 'R'))), 0) w14
FROM (SELECT ymdseq
--, vessel, crew, ymd, seq
, REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
SUBSTR(REPLACE(wm_concat(hval) OVER(ORDER BY ymdseq, seq), ','), -48)
, '0{6,}' , 'R')
, '1{14,}', 'W')
, '[^RW]')
AS x
FROM atable
WHERE vessel = '001'
AND crew = '100002'
AND ymdseq >= '20131227'
AND ymdseq <= '20140131'||'z'
)
WHERE ymdseq >= '20140101'
AND ymdseq <= '20140131'||'z'
GROUP BY ymdseq
ORDER BY ymdseq
;
-- 정리를 해주시네요.^^ -- substr에 - 쓰는거 오랜만에 리마인드 합니다.~
진짜 너~~무 감사합니다.
킹왕짱이십니다.
--다행입니다.~
좋은 정보 감사드립니다..^^
) 버튼을 클릭하여 작성 하시면 됩니다.