SELECT a.memcd, b.memnm, b.sex, b.memiden, REPLACE (b.handtel, ' ', '') handtel, SUM (a.sale_amt) amt0 FROM pntlog a INNER JOIN MEMBER b ON a.memcd = b.memcd WHERE a.memdate BETWEEN '20120623' AND '20150623' AND b.handtel > '0' AND b.updgb = '0' AND b.memnm NOT LIKE '*%' AND a.jumcd = '01' GROUP BY a.memcd, b.memnm, b.sex, b.memiden, b.handtel
이 쿼리 하나만 한다면 그냥 사용할텐데 동일한 쿼리를 여러번 union all 로 사용할 예정이라
튜닝이 필요할듯 싶습니다.
여기서 between, >, not like 이 조건이 풀테이블스캔하는 이유라고 생각됩니다.
어떤식으로 튜닝을 해야 할까요?
ELECT /*+ ORDERED USE_NL ( a b ) INDEX ( a 인덱스명(memdate) ) */ a.memcd, b.memnm, b.sex, b.memiden, REPLACE (b.handtel, ' ', '') handtel, SUM (a.sale_amt) amt0 FROM pntlog a INNER JOIN MEMBER b ON a.memcd = b.memcd WHERE a.memdate BETWEEN '20120623' AND '20150623' AND b.handtel > '0' AND b.updgb = '0' AND b.memnm NOT LIKE '*%' AND a.jumcd = '01' GROUP BY a.memcd, b.memnm, b.sex, b.memiden, b.handtel