아래 검색시간 1초 미만 (2000행 정도 검색)----------
SELECT B.YMDSEQ, B.YMD
, NVL(LENGTH(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
wm_concat(NVL(A.HVAL,0)) OVER(ORDER BY B.YMDSEQ DESC, B.SEQ DESC
ROWS BETWEEN CURRENT ROW AND 47 FOLLOWING)
, ',')
, '0{6,}', 2)
, '[^2]')
), 0) REST6
FROM CREW_RT10 A,
CREW_RT04 B
WHERE B.VESSEL = A.VESSEL(+)
AND B.YMDSEQ = A.YMDSEQ(+)
AND B.SEQ = A.SEQ(+)
AND B.VESSEL = '0001'
AND A.CREW(+) = '100002'
아래 검색시간 2초 -----------
SELECT YMDSEQ, REST6
FROM (SELECT B.YMDSEQ, B.YMD
, NVL(LENGTH(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
wm_concat(NVL(A.HVAL,0)) OVER(ORDER BY B.YMDSEQ DESC, B.SEQ DESC
ROWS BETWEEN CURRENT ROW AND 47 FOLLOWING)
, ',')
, '0{6,}', 2)
, '[^2]')
), 0) REST6
FROM CREW_RT10 A,
CREW_RT04 B
WHERE B.VESSEL = A.VESSEL(+)
AND B.YMDSEQ = A.YMDSEQ(+)
AND B.SEQ = A.SEQ(+)
AND B.VESSEL = '0001'
AND A.CREW(+) = '100002'
) S
ORDER BY 1
아래 검색시간 4초 -----------
SELECT YMDSEQ, min(REST6)
FROM (SELECT B.YMDSEQ, B.YMD
, NVL(LENGTH(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
wm_concat(NVL(A.HVAL,0)) OVER(ORDER BY B.YMDSEQ DESC, B.SEQ DESC
ROWS BETWEEN CURRENT ROW AND 47 FOLLOWING)
, ',')
, '0{6,}', 2)
, '[^2]')
), 0) REST6
FROM CREW_RT10 A,
CREW_RT04 B
WHERE B.VESSEL = A.VESSEL(+)
AND B.YMDSEQ = A.YMDSEQ(+)
AND B.SEQ = A.SEQ(+)
AND B.VESSEL = '0001'
AND A.CREW(+) = '100002'
) S
group by ymdseq
ORDER BY 1
테이블를 감싸고 group by 하니깐 상당히 검색속도가 느린데요
s 테이블 안에서 바로 group by 하는 방법이 있을까요
답변부탁드립니다.