데이터베이스 질문과 답변
오랫만에 쿼리 문의드리게 되었습니다.
기본적으로 게시판 목록을 읽을 때 해당 사용자의 정보를 트래킹하여 입력시키고 있습니다.
그래서 해당 게시물의 내용을 읽을 때
그 게시물을 많이 읽어본 사람이 자주 읽어보는 게시물을 5개 추출하도록 처리하고 있는데요.
해당 테이블과 인덱스는 아래와 같습니다.
테이블 : 트래킹로그
게시물 ID VARCHAR2(250)
컬렉션명 VARCHAR2(50)
트래킹ID VARCHAR2(500)
인덱스 : 트래킹로그인덱스01(게시물ID, 컬렉션명)
해당 쿼리는 아래와 같습니다.
SELECT 게시물ID, 컬렉션명, RNUM FROM (SELECT 게시물ID, 컬렉션명, ROWNUM AS RNUM FROM (SELECT 게시물ID, 컬렉션명 FROM (SELECT 게시물ID, 컬렉션명, 트래킹ID, Count(*) FROM 트래킹로그 WHERE 1=1 AND 게시물ID IS NOT NULL AND 컬렉션명 IS NOT NULL AND 게시물ID != '1215897' AND 트래킹ID IN (SELECT DISTINCT 트래킹ID FROM 트래킹로그 WHERE 게시물ID IS NOT NULL AND 게시물ID = '1215897' AND 컬렉션명 = 'td_fep' ) GROUP BY 게시물ID, 컬렉션명, 트래킹ID ) GROUP BY 게시물ID , 컬렉션명 ORDER BY Count(*) DESC ) ) WHERE RNUM <= 5;
10046 트레이스를 뜨면 아래와 같습니다.
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 1.93 5.88 1215 156123 0 5 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 1.93 5.89 1215 156123 0 5 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 72 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 5 5 5 VIEW (cr=156123 pr=1215 pw=1215 time=5872803 us cost=41855 size=1014 card=6) 31135 31135 31135 COUNT (cr=156123 pr=1215 pw=1215 time=5900837 us) 31135 31135 31135 VIEW (cr=156123 pr=1215 pw=1215 time=5892506 us cost=41855 size=936 card=6) 31135 31135 31135 SORT ORDER BY (cr=156123 pr=1215 pw=1215 time=5882638 us cost=41855 size=96 card=6) 31135 31135 31135 HASH GROUP BY (cr=156123 pr=1215 pw=1215 time=5867422 us cost=41855 size=96 card=6) 79675 79675 79675 VIEW (cr=156123 pr=1215 pw=1215 time=5827092 us cost=41854 size=96 card=6) 79675 79675 79675 HASH GROUP BY (cr=156123 pr=1215 pw=1215 time=5804967 us cost=41854 size=1284 card=6) 84133 84133 84133 HASH JOIN RIGHT SEMI (cr=156123 pr=0 pw=0 time=785733 us cost=41853 size=1284 card=6) 31 31 31 TABLE ACCESS BY INDEX ROWID 트래킹로그 (cr=34 pr=0 pw=0 time=375 us cost=8 size=535 card=5) 31 31 31 INDEX RANGE SCAN 트래킹로그인덱스01 (cr=3 pr=0 pw=0 time=64 us cost=3 size=0 card=5)(object id 173258) 2317984 2317984 2317984 TABLE ACCESS FULL 트래킹로그 (cr=156089 pr=0 pw=0 time=2088077 us cost=41834 size=176552354 card=1650022) ********************************************************************************
이백만건이 넘는 데이터를 스캔하고 있어서 튜닝 포인트를 어떻게 잡는 게 좋을지 모르겠네요.
트래킹ID를 인덱스로 잡아봤지만 그렇게 효과를 보지는 못했어요.
많은 고수님들의 고견 부탁드리겠습니다.
잘못 봐서 내용 수정합니다.
트래킹ID까지 GROUP BY 로 COUNT 하는 이유가 멀까요? 동일한 컬렉션명에 게시물id가 중복된것도 나올수 있을꺼 같네요
로그인을 한 사용자가 글을 읽을 경우는 ID와 트래킹ID가 남게 되고요.
로그인을 하지 않은 사용자가 글을 읽을 경우에는 트래킹ID만 남게 됩니다.
그래서 트래킹ID도 GROUP BY 대상에 들어가게 된 것이죠.
1. RNUM <= 5 조건이 너무 늦었습니다.
- 이전 단계(정렬직후)에서 ROWNUM <= 5 형태로 주셔야 합니다.
2. GROUP BY 를 두단계로 나누어 했네요
- 한번에 가능하구요.
SELECT 게시물ID, 컬렉션명 , ROWNUM AS RNUM FROM (SELECT 게시물ID, 컬렉션명 , COUNT(DISTINCT 트래킹ID) cnt FROM 트래킹로그 WHERE 1=1 AND NOT (게시물ID = '1215897' AND 컬렉션명 = 'td_fep') AND 트래킹ID IN (SELECT 트래킹ID FROM 트래킹로그 WHERE 게시물ID = '1215897' AND 컬렉션명 = 'td_fep' ) GROUP BY 게시물ID, 컬렉션명 ORDER BY cnt DESC ) WHERE ROWNUM <= 5 ;
DarkBee님, 마농님 답변 감사합니다.
알려주신 쿼리로 조회해서 10046 트레이스를 떴는데요.
SEMI JOIN에 대한 TABLE ACCESS FULL 트래킹로그의 건수는 줄지 않더라고요.
SEMI HASH RIGHT JOIN을 다른 방법으로 튜닝할 수는 없는 걸까요??
IN 절이 필터로 풀리지 않고
HASH JOIN RIGHT SEMI 로 풀린것은 성능에 좋은 것입니다.
어차피 풀스캔 해야 할 상황일 듯 한데요?
트래킹ID 에 대한 인덱스를 이용하고자 하신다면?
서브쿼리를 IN 절이 아닌 FROM 절로 올려서 조인형태로 바꾸어 보세요.
) 버튼을 클릭하여 작성 하시면 됩니다.