데이터베이스 질문과 답변
안녕하세요, 구루비에서 많은 도움을 받고 있는 개발자입니다
고민 끝에 문의 드립니다
A = CUS_MGR (고객관리)
- CUM_ID
- CUM_SEQ
B = CUS_DTL ( 품목내역)
- CUD_SEQ
- CUD_GRP_NO
- CUD_NO
- CUD_DTL_NO
- CUD_DATE
- CUD_DATE_SEQ
- CUD_VALUE
C = CUS_ITEM (품목명)
- CUI_ID
- CUI_NO
- CUI_NM
테이블(CUS_DTL)은 BigData(개인 10만 Row)입니다, RANK 함수 외에, 쿼리 속도 개선이 가능 할까요?
부탁드립니다
SELECT
TO_CHAR(T.CUD_DATE, 'YYYYMMDDHH24MISS')
, T.CUD_VALUE
FROM
(
SELECT B.CUD_DATE
, B.CUD_VALUE
, DENSE_RANK() OVER(PARTITION BY C.CUI_NM
ORDER BY B.CUD_DATE DESC) AS RNK
FROM CUS_MGR A
, CUS_DTL B
, CUS_ITEM C
WHERE A.CUM_ID = '11111'
AND B.CUD_SEQ = A.CUM_SEQ
AND B.CUD_DATE >= TRUNC(SYSDATE) - 365
AND B.CUD_DATE < TRUNC(SYSDATE) + 1
AND C.CUI_ID = 'A01'
AND C.CUI_NO = B.CUD_NO
AND C.CUI_NM IN ('AA', 'AB', 'AC')
) T
WHERE T.RNK = 1
1. 느리다면? 적절한 인덱스가 없는게 아닐까요?
- 인덱스 정보를 알려주세요.
2. Dense_rank 를 사용할 이유가 전혀 없습니다. 그냥 RANK 쓰세요.(성능과는 무관)
B = CUS_DTL ( 품목내역) 테이블 인텍스 입니다
CUS_DTL_I01( CUD_SEQ, CUD_GRP_NO, CUD_NO, CUD_LOC_CD, CUD_DATE)
1. 인덱스
- 인덱스 중간 컬럼들에 대한 조건이 없네요.(CUD_GRP_NO, CUD_LOC_CD)
- 해당 쿼리 필요 인덱스 (CUD_SEQ, CUD_NO, CUD_DATE)
2. 그룹 기준이 모호한데요?
- PARTITION BY c.cui_nm 을 하고 있는데?
- PARTITION BY b.cud_no 로 바꾸면 의미가 바뀌나요?
- b.cud_no 로 바꾸어도 의미가 바뀌지 않아야 개선이 가능할 듯 한데요?
중간 컬럼(CUD_GRP_NO, CUD_NO, CUD_LOC_CD)에 대한 조건이 없습니다, ㅠㅠ
B.CUD_NO 하면 결과 값이 달라져, C.CUI_NM으로 했습니다
우선은 적절한 인덱스가 필요한 상황으로보입니다.
그리고.
뭔가가 이상하네요?
PARTITION BY c.cui_nm 을
PARTITION BY b.cud_no 로 바꾸면 결과가 달라지는 것도 이상하고
그렇다면? 결과물에 c.cui_nm 이 표시가 되어야 의미가 있을 듯 한데, 그렇지도 않고?
다음 쿼리의 결과가 어떻게 나오나요?
SELECT * FROM cus_item WHERE cui_id = 'A01' AND cui_nm IN ('AA', 'AB', 'AC');
죄송합니다, select에서 항목이 누락되었네요
SELECT
T.CUD_NO
, TO_CHAR(T.CUD_DATE, 'YYYYMMDDHH24MISS')
, T.CUI_NM
, T.CUD_VALUE
FROM
(
SELECT B.CUD_NO as CUD_NO
, B.CUD_DATE as CUD_DATE
, C.CUI_NM as CUI_NM
, B.CUD_VALUE as CUD_VALUE
, DENSE_RANK() OVER(PARTITION BY C.CUI_NM
ORDER BY B.CUD_DATE DESC) AS RNK
FROM CUS_MGR A
, CUS_DTL B
, CUS_ITEM C
WHERE A.CUM_ID = '11111'
AND B.CUD_SEQ = A.CUM_SEQ
AND B.CUD_DATE >= TRUNC(SYSDATE) - 365
AND B.CUD_DATE < TRUNC(SYSDATE) + 1
AND C.CUI_ID = 'A01'
AND C.CUI_NO = B.CUD_NO
AND C.CUI_NM IN ('AA', 'AB', 'AC')
) T
WHERE T.RNK = 1
-------------------- Output
| CUD_NO | CUD_DATE | CUI_NM | CUD_VALUE | RNK |
| 120 | 2018-05-29 10:00:00 | AA | 100.25 | 1 |
| 120 | 2018-05-29 09:00:00 | AA | 95.75 | 2 |
| 120 | 2018-05-29 08:00:00 | AA | 99.17 | 3 |
| 120 | 2018-05-29 07:00:00 | AA | 102.05 | 4 |
| 131 | 2018-05-29 10:00:00 | AB | 170 | 1 |
| 132 | 2018-05-29 09:00:00 | AB | 150 | 2 |
| 132 | 2018-05-29 08:00:00 | AB | 180 | 3 |
| 129 | 2018-05-29 07:00:00 | AB | 160 | 4 |
| 151 | 2018-05-29 10:00:00 | AC | 100 | 1 |
| 162 | 2018-05-29 09:00:00 | AC | 95 | 2 |
| 162 | 2018-05-29 08:00:00 | AC | 120 | 3 |
|
149 |
2018-05-29 07:00:00 | AC | 110 | 4 |
------------------------------
SELECT * FROM cus_item WHERE cui_id = 'A01' AND cui_nm IN ('AA', 'AB', 'AC');
------------------------------ OutPut
| CUS_ID | CUS_NO | CUS_NM |
| A01 | 120 | AA |
| A01 | 129 | AB |
| A01 | 131 | AB |
| A01 | 132 | AB |
| A01 | 149 | AC |
| A01 | 151 | AC |
| A01 | 162 | AC |
입니다
SELECT *
FROM (SELECT a.cui_nm
, b.cud_no
, b.cud_date
, b.cud_value
, RANK() OVER(PARTITION BY a.cui_nm ORDER BY b.cud_date DESC) rnk
FROM (SELECT /*+ no_merge */
a.cum_seq
, c.cui_no
, c.cui_nm
FROM cus_mgr a
, cus_item c
WHERE a.cum_id = '11111'
AND c.cui_id = 'A01'
AND c.cui_nm IN ('AA', 'AB', 'AC')
) a
, cus_dtl b
WHERE a.cum_seq = b.cud_seq
AND a.cui_no = b.cud_no
AND b.cud_date >= TRUNC(sysdate) - 365
AND b.cud_date < TRUNC(sysdate) + 1
)
WHERE rnk = 1
;
역시, 명불허전(名不虛傳)이십니다.
정말 감사합니다 ~
추가 -
이전 쿼리와 이후 쿼리의 plan에서 cost는 이전 쿼리가 조금 낮게 나오지만, 반응은 이후 쿼리가 조금 나은것 같습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.