데이터베이스 질문과 답변
굿모닝요~^^
아침부터 한 가지 궁금한 점이 생겨서 여쭤봅니다.
SELECT
A.CODE, CNT
, (SELECT /*+INDEX_DESC(CODEMST CODEMST_PK)*/
CODENAME
FROM CODEMST
WHERE CODE = A.CODE
AND ROWNUM = 1) CODENAME
FROM (SELECT CODE, COUNT(1) CNT
FROM ORDERLT A
WHERE A.ORDERDT BETWEEN '2017-02-01' AND '2017-02-28'
GROUP BY CODE) A
ORDER BY CNT DESC
문제의 쿼리입니다.
테이블 설명부터 드리면
ORDERLT 는 내역 테이블이고요 ORDERDT(처방일), CODE(처방코드)
등으로 구성이 되어있습니다.
CODEMST 는 처방마스터 테이블로 CODE(처방) FROMDATE(시작일), TODATE(종료일), CODENAME(처방명)
등으로 구성된 이력 테이블입니다.
위의 쿼리는 2017년 2월에 많이 처방된 처방을 순서대로 보여주는 쿼리인데요.
처방명을 가지고 오기 위해 서브쿼리 형태로 썼습니다.
보통 이력테이블에서 특정시점의 마스터를 가지고 오기 위해서는
처방일 BETWEEN 마스터.FROMDATE AND 마스터.TODATE
형태로 조인하여 가지고 오면 되지만 처방마스터에서 제일 마지막 이력을 가지고
오려면 어떻게 조인해야할까 생각하다가 조인으로는 좀 어려울 것 같아서
위와 같은 형태로 일단 만들어본 거구요.
굳이 조인형태로 만들어본다면
SELECT
A.CODE, CNT, CODENAME
FROM (SELECT CODE, COUNT(1) CNT
FROM ORDERLT A
WHERE A.ORDERDT BETWEEN '2017-02-01' AND '2017-02-28'
GROUP BY CODE) A
, (SELECT CODE, CODENAME, ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY FROMDATE DESC) RN
FROM CODEMST) B
WHERE A.CODE = B.CODE
AND B.RN = 1
ORDER BY CNT DESC
이와 같은 형태로 만들어볼 수도 있을 것 같은데 결론적으로 비효율이 조금 있습니다.
마스터에서 4354(252건에 대한 모든 이력)건을 읽고 난 후 최종적으로 252건만 사용하게 되더라구요.
효율적으로 조인하는 방법이 있을까요?
답변과는 별개의 질문이지만,
혹 맨 아래 첨부사진
열정열을 직접 하셔서 출력하신 데이터인가요?
만약 그러시다면 저렇게
| 0| 이런식으로 깔끔하게 떨어지게 어떻게하신건가요..쿼리 첨부가능하시다면..참고하게 부탁드리겠습니다.
일단 실행계획 가지고 올 때 저는 dbms_xplan 사용합니다.
사용 방법은 아래 링크 참고하세요.
http://wiki.gurubee.net/pages/viewpage.action?pageId=30966079
그리드에 나오는 결과를 복사해서 토드나 편집기에 붙여넣으면
해당 툴이 굴림체처럼 정렬을 해주는 경우 저렇게 정렬되어서 나옵니다.
1. 두 쿼리의 조건이 다르네요?
- 어떤 조건이 맞는지?
2. 기간 검색으로 조인이 가능할 것 같네요.
- a.orderdt BETWEEN b.fromdate AND b.todate
3. 2번에서 언급한것처럼 조인하는게 아니라 최종건이 목적이라면?
- row_number 사용시 codename 을 제거하고 사용해야 인덱스만 스캔하게 됩니다.
- 대신 codename 을 가져오기 위해 테이블을 한번더 사용해야 하겠지요.
- 테이블을 두번 사용하여 셀프 조인하지만
- 실제로는 인덱스 한번 테이블 한번이므로 테이블 한번 사용한 것과 마찬가지입니다.
- 대신 테이블 렌덤엑세스량만 줄어들죠.
4. 기타 방법으로
- 인덱스 자체에 codename 을 추가하는 방법도 있습니다.
- 인덱스 스캔만으로 원하는 결과를 얻을 수 잇구요.
- 최종건을 분별할수 있도록 하는 방법도 있습니다.
- 최종건의 todate 는 99991231 로 통일시킨다거나
- 최종건에 대한 flag 컬럼을 두는 방법 등
1.조회 조건은 제가 원본 쿼리를 그대로 올리기 그래서 편집하다가 실수했네요^^;(조건 수정했습니다.)
2.최종건이 목적이라서 해당 조건으로 조인이 어렵습니다~
3.codename을 제거하면 테이블 데이터에 접근하지 않을 뿐 최초 인덱스 접근시 4354건을 읽고
최종 252건을 필터하는 것은 동일해보입니다.
서브쿼리로 작성했을 때는 딱 252건만 접근하고 멈출 수 있어서 혹시 조인으로도 비슷하게 구현이
가능할까하여 질문 드렸어요.
4.todate는 29991231로 사용중이나
종료된 마스터에 대해서는 종료일자가 들어가는 바람에 사용할 수가 없고요.
아무래도 이렇게 조인하려고 하면 마스터에 최종건이라는 flag를 두는 수밖에 없을 것 같네요.
뭔가 테크닉적으로 가능하지 않을까 싶었는데 어려운 것 같습니다.
답변 감사드립니다^^
추가) 마스터 하나 더 두고 셀프조인 하는 방법은 랜덤액세스 줄일 수 있어서 참 좋은 방법인 것 같습니다!
4354건을 읽고 최종 252건을 필터하는 것은 동일해보입니다.
라고 하셨습니다만. 동일하지 않습니다.
- 기존 쿼리는 인덱스 스캔(4354) > 테이블스캔(4354) > 필터
- 변경 쿼리는 인덱스 스캔(4354) > 필터 > 테이블스캔(496)
- 테이블 스캔. 즉, 렌덤엑세스량이 줄어들죠.
여기서 코드테이블끼리의 셀프조인 뿐 아니라
코드(인덱스) 와 처방을 우선 조인하고
코트 테이블을 나중에 조인하는 방식으로 한다면?
코드테이블 렌덤엑세스량은 더 줄수도 있을 듯 하네요.(496 > 252)
네 제가 제 생각을 제대로 표현하지 못 한 것 같습니다.
마스터 하나 더 두고 조회하는 방법이 랜덤액세스 줄일 수 있는 것은 확실합니다.
랜덤액세스를 처리하는데 셀프조인이 요긴하다는 것을 다시 한번 일깨워주셨네용~
다만 서브쿼리는 인덱스조차도 252건만 읽는데 조인형태에서는 어쩔 수 없이 4354건을 읽고 있어서
처방코드마다 단 한건만 읽을 수는 없을까 하는 게 제 질문의 요지였습니다.
SELECT
A.CODE, CNT, C.CODENAME
FROM (SELECT CODE, COUNT(1) CNT
FROM ORDERLT A
WHERE A.ORDERDT BETWEEN '2017-02-01' AND '2017-02-28'
GROUP BY CODE) A
, (SELECT CODE, FROMDATE, ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY FROMDATE DESC) RN
FROM CODEMST) B
, CODEMST C
WHERE A.CODE = B.CODE
AND A.CODE = C.CODE
AND B.FROMDATE = C.FROMDATE
AND B.RN = 1
ORDER BY CNT DESC
코드끼리 셀프 조인을 저는 이렇게 이해했는데
뒤에 더 적어주신
"코드(인덱스) 와 처방을 우선 조인하고
코트 테이블을 나중에 조인하는 방식으로 한다면?"
와 같은 방법인 것 같네요?
처음에 말씀하신 코드 테이블끼리 셀프조인은 어떤 형식을 말씀하신 것인지요?
위처럼 code 와 fromdate 로 조인하면
(인덱스 + 테이블) 이 아닌
(인덱스 + 인덱스 + 테이블) 이 되어 버리죠.
인덱스 스캔을 두번 하게 됩니다.
ROWID 를 이용해 조인해야 합니다.
SELECT a.code
, c.codename
, a.cnt
FROM (SELECT code
, COUNT(*) cnt
FROM orderlt
WHERE orderdt BETWEEN '2017-02-01' AND '2017-02-28'
GROUP BY code
) a
, (SELECT code
, ROWID rid
, ROW_NUMBER() OVER(PARTITION BY code ORDER BY fromdate DESC) rn
FROM codemst
) b
, codemst c
WHERE a.code = b.code
AND b.rid = c.ROWID
AND b.rn = 1
ORDER BY cnt DESC
;
아 그렇군요!!
SELECT
A.CODE, CNT, C.CODENAME
FROM (SELECT CODE, COUNT(1) CNT
FROM ORDERLT A
WHERE A.ORDERDT BETWEEN '2017-02-01' AND '2017-02-28'
GROUP BY CODE) A
, (SELECT CODE, ROWID RID, ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY FROMDATE DESC) RN
FROM CODEMST) B
, CODEMST C
WHERE A.CODE = B.CODE
AND B.RN = 1
AND B.RID = C.ROWID
ORDER BY CNT DESC
이렇게 하면 되는 건가요?
역시 걸어다니는 오라클 백과사전♥♥
엉뚱한 질문 올렸다가 좋은 지식을 얻어갑니다. 감사합니당~~
신기 신기 나도 배워감 ^^*
) 버튼을 클릭하여 작성 하시면 됩니다.