최근 데이타 가져오는 쿼리에 대해서 1 16 19,388

by 야신 [Oracle 기초] SQL [2013.12.12 11:04:30]


현재 매출 테이블에 (마감일, 지역) 순으로 인덱스가 걸려 있습니다.
지역별로 마감일자가 달라 가장 최신의 마감일 가진 데이타를 사용하여야 하는데요
예) 한국은 201202 , 미국은 20121201 

1) SQL => 제가 사용했던 방법
SELECT *
    FROM 매출
              , (
SELECT 지역
           , MAX(마감일) MAX_WORK_DATE
   FROM 매출
 GROUP BY 지역)   AS 매출요약
WHERE 매출.WORK_DATE = 매출요약.MAX_WORK_DATE
     AND 매출.지역 = 매출요약.지역

2) SQL => GROUP BY 를 통한 IO 과다가 발생한다고 아래와 같이 사용하라고 권장함
SELECT *
    FROM 매출
              , (
SELECT 지역
           , 마감일 MAX_WORK_DATE
           , ROW_NUMBER() OVER( PARTITION BY 지역 ORDER BY 마감일 DESC ) RN
   FROM 매출)  매출요약
WHERE 매출.WORK_DATE = 매출요약.MAX_WORK_DATE
     AND 매출.지역 = 매출요약.지역
     AND 매출요약.RN = 1

여기서는 2번 SQL 을 권장하는데
여러분은 어떻게 쓰시나요?

ps ) 마농님이 저적해 주신 SQL 오류 수정했습니다. ^^;
by 우리집아찌 [2013.12.12 11:12:11]

전 테이블에서 MAX/MIN 컬럼값 한개정도 가져오면 MAX
반대로 MAX/MIN 컬럼값이 많이 필요하면 ROW_NUMBER


   

by 야신 [2013.12.12 11:33:45]
DB 부하관계가 문제네요. ㅡ_ㅡ;;

by 용근님 [2013.12.12 11:13:08]
테이블 하나만 써도 가능할꺼 같은데 두개를 쓰신 이유가?

by 야신 [2013.12.12 11:33:11]
하나로 어떻게 만들수 있을까요? ^^;;
지역별로 마감일자가 달라요.

by 용근님 [2013.12.12 11:35:40]
SELECT *
     FROM (
SELECT 지역

           , 마감일 MAX_WORK_DATE
           , ROW_NUMBER() OVER( PARTITION BY 지역 ORDER BY 마감일 DESC ) RN
   FROM 매출
 GROUP BY 지역

WHERE RN = 1

이거랑 틀리나요? 결과 데이터가..

by 야신 [2013.12.12 14:13:20]
지역의 같은 마감일을 가진데이타가 여러건 있을 수 있는데
용근님의 SQL 로 적용하면 
마감일 별로 한건씩 밖에 안 나와요
예를 들어 
seq_id/ 지역/ 마감일/ 판매수량
1, 한국, 20131202  , 10개 
2, 한국, 20131202 , 15개
3, 미국, 20131201, 13개

이런데이타가 있다고 하면 3건이 다 나와야 하는데
용근님 쿼리로 한다면 2건 밖에 안 나와요. ㅡ_ㅡ;;

by 임상준 [2013.12.12 16:53:35]

마감일 별 한건씩 나오는건 row_number 가 아니라 rank 로 쓰시면 될거같구요...


by 야신 [2013.12.12 17:04:23]
감사합니다.
rank 를 쓰면 되네요 ^^;;
그런데 전체테이블을 읽기전에는 sort 을 할수 없기 때문에 오히려 비효율적인것 같습니다.
실행계획도 비용이 높네요.

by 마농 [2013.12.12 13:48:06]

1번 방법은 인덱스만 풀스캔 후 그룹바이 하고 결과건수만큼만 인덱스 스캔하여 테이블 읽습니다.
==> 그닥 나쁜 방법이 아닙니다.

2번 방법은 쿼리가 좀 틀렸네요. 그룹바이가 없어야 에러 안나겠지요.
==> 성능은 유사합니다. 어차피 인덱스 풀스캔후 rn=1로 걸러낸 자료만큼 인덱스 타지요.

1번보다 2번이 나을것은 없어 보입니다. 유사하게 동작합니다.

가장 확실한 성능개선 방법은 인덱스의 순서가 바뀌어야 합니다.(지역, 일자)
지역테이블을 따로 두고 지역테이블을 읽으면서 그 건수만큼 인덱스 Desc 스캔하면서 rownum= 1 하는 것입니다.
이렇게 되면 인덱스 풀스캔을 안해도 되지요.


by 야신 [2013.12.12 14:18:21]
답변 감사드립니다. 
그냥 예제로 SQL 적다보니 오류가 있었네요. 
Planing 을 보면 1 이나 2나 Plan 상 큰 차이가 없어 보이는데 
2) 를 권장해서 어떤 차이점이 있나 궁금했습니다.





by 임상준 [2013.12.12 16:34:03]

원본 쿼리에도 매출 테이블에 다른 조건이 없나요?
결과집합 줄여줄 수 있는...


by 야신 [2013.12.12 17:02:25]
다른 곳에서 저런 형식으로 된 뷰를 조회합니다.
항상 마지막 마감일자가 이전것을 다 포함하고 있는 형태라서요.
매일매일 마감일로 스냅삿을 뜨는 형태지요.

예를 들면 그날 그날의 살아있는 PO 같은거요.
일자별로 매일매일 쌓지만 마지막 날짜의 것이 살아있는 PO지요.

by 마농 [2013.12.12 17:29:07]
-- 조건이 없기 때문에 전체 테이블을 읽을수밖에 없습니다.
-- 조건이 없는 한 전체데이터를 읽는 것은 어쩔수 없습니다.
-- 이 때 위 두가지 쿼리는 테이블 대신 인덱스를 전체 읽습니다.
-- 전체 데이터를 읽은 후 Max만 추려내는 과정에서 비용을 줄여야 하는데요.
-- 위 두가지 방법 모두 효율적인 방법입니다.
-- 그룹바이나 정열을 위한 버퍼가 전체 데티터 만큼 잡히는게 아니라
-- 필요한 결과수 만큼만 잡히는 거죠.

-- 테이블풀스캔보다는 인덱스 풀스캔이 그나마 비용이 적겠지요. 테이블 컬럼들이 아주 많다면요.
-- 테이블 크기보다 인덱스 크기가 작으니까요.
-- 그러나 컬럼들이 몇개 안된다면 차라리 테이블 풀스캔이 나을 수도 있습니다.
-- 인덱스풀스캔은 싱글블럭I/O 이지만 테이블풀스캔은 멀티블럭I/O 이지요.
-- 멀티블럭 I/O 로 인덱스를 읽게 하려면 INDEX_FFS 힌트를 사용하셔야 하구요.
-- 인덱스 페스트 풀스캔도 시도해 보시구요.
-- 다음과 같이 인덱스를 경유하지 않고 차라리 테이블 풀스캔하는 방법도 시도해 보세요.
SELECT *
  FROM (SELECT 매출.*
             , RANK() OVER(PARTITION BY 지역 ORDER BY 마감일 DESC) rk
          FROM 매출
        )
 WHERE rk = 1
;

-- 그리고 뷰인 경우 뷰를 조회할때 혹시 지역조건을 주는 경우가 있지 않나요?
-- 만약 뷰 조회시 지역조건을 주고 조회한다면?
-- rank 보다는 Group By 가 나을 수도 있습니다.
-- 조건이 뷰 안으로 침투할 수 있기 때문이죠.

by 야신 [2013.12.12 18:35:03]
감사합니다. 마농님 ^^
주신 답변을 참고하여 지역, 일자로 된 인덱스를 추가로 만들었습니다.
그리고 나서 1번 SQL 을 적용했더니 많이 좋아졌습니다.
rank 도 적용해서 테스트 해보았는데 오히려 1),2)  보다 응답속도가 늦더라고요.
데이타가 하루 하루 쌓이면 테이블 FULL 스캔이 비효율적인것 같네요.




by 마농 [2013.12.13 09:53:20]
-- (지역, 일자) 인덱스를 만드셨다면?
-- 다음과 같이 지역테이블을 이용한다면. 엄청난 성능개선이 있을 것으로 판단됩니다.
SELECT * 
  FROM 매출
 WHERE (지역, 일자)
    IN (SELECT 지역
             , (SELECT MAX(일자) FROM 매출 WHERE 지역 = a.지역) 일자
          FROM 지역 a
        )
;

by 야신 [2013.12.13 17:43:44]
우와! 이렇게도 사용하니 굉장하네요.
새로운 방법에 눈을 크게 뜨게 되네요.

감사합니다. +_+
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입