조인 튜닝의 방식을 소개
선분이력 관련 튜닝
먼저 고객테이블과 고객별연체이력 테이블을 생성한다.
- 고객 테이블 총 10건의 데이터
- 고객별 연체이력 (100만건)
고객별 연체이력 테이블은 Min 시작일은 2005년 Max 시작일은 2550년도로
100만개의 데이터를 넣었다.
create index 고객별연체이력_IDx01 on 고객별연체이력(고객번호, 종료일, 시작일);이때 인덱스로 고객번호, 종료일, 시작일로 결합인덱스 구성
select /*+ ordered use_nl(b) */
a.고객명, a.거주지역, a.주소 , a.연락처, b.연체금액
from 고객 a, 고객별연체이력 b
where a.가입회사 = 'C07'
and b.고객번호 = a.고객번호
and '20050131' between b.시작일 and b.종료일;로 조인 쿼리를 날린다.
고객은 10개 레코드이므로 가입회사가 index존재하더라도 테이블 풀스캔 하는것이 효율적이다.
그후 고객번호로 range scan한 Index를 NL조인 시도할때, 비효율이 발생한다.
바로 인덱스 순서가 종료일, 시작일 이므로
20050131 데이터를 종료일부터 조건에 맞는지 검사한다.
하지만 종료일 컬럼은 2005년부터 2550년까지 데이터가 분포하므로
모든 데이터가 20050131 조건에 부합하면서 비효율이 발생한다.
튜닝
튜닝 이라고 할것도 없지만.
인덱스 순서를
create index 고객별연체이력_IDx01 on 고객별연체이력(고객번호, 시작일, 종료일);로 바꿔준다면 시작일부터 조건을 검사하여 높은 효율로 검색할 수 있다.
이처럼 실제 데이터 분포도에 따른 인덱스 전략을 구성해야 하며 이는 언제든지 유동적으로 바뀔 수 있다.
또다른 조건
만약
select /*+ ordered use_nl(b) */
a.고객명, a.거주지역, a.주소 , a.연락처, b.연체금액
from 고객 a, 고객별연체이력 b
where a.가입회사 = 'C07'
and b.고객번호 = a.고객번호
and '20050131' between b.시작일 and b.종료일;
-> and a.서바스만료일 between b.시작일 and b.종료일;위처럼 서비스 만료일 조건 컬럼이 상수가 아닌 조회건마다 변동할때는
인덱스 구성과는 무관하게 랜덤한 값이 들어가며 선분이력의 비효율을 발생시킨다.
이럴때는 조인문을 스칼라 서브쿼리나 중첩된 서브쿼리로 각 고개별로 단 하나의 이력만 읽도록 rownum<=1 조건을 추가해 줄 수 있다.
다행히 위에서는 고객별연체이력 테이블로부터 연체금액 하나만 읽기때문에 아래와 같이 스칼라 서브쿼리로 간단하게 변경할 수 있다.
select a.고객명, a.거주지역, a.주소 , a.연락처,
(select /*+ index_desc (b 고객별연체이력_IDX01) */ 연체금액
from 고객별연체이력 b
where b.고객번호 - a.고객번호
and a.서비스만료일 between 시작일 and 종료일
and rownum <= 1) 연체금액
from 고객 a
where 가입회사 = 'C07'위 쿼리에서 rownum <=1을 사용했더니 고객별 연체이력 테이블에서 고객번호별로 조회하며, 시작일자와 종료일자가 맞는 첫 데이터를 가져온다.
이는 선분이력 특성상 뒤의 데이터는 확인하지 않아도 되므로 rownum이 효과적으로 사용된 쿼리이다.
조건 추가
만약 고객별연체이력 테이블에서 하나의 값이아닌 두개이상의 값을 원할땐 어떡할까?
바로 아래와 같은 스칼라 서브쿼리에서 rowid만 취하여 고객별 연체이력 테이블을 한번더 조인하는 방법을 쓸수 있다.
select /*+ ordered use_nl(b) rowid(b) */ a.*, b.연체금액, b.연체개월수
from (select a.고객명, a.거주지역, a.주소 , a.연락처,
(select /*+ index_desc (b 고객별연체이력_IDX01) */ rowid
from 고객별연체이력 b
where b.고객번호 - a.고객번호
and a.서비스만료일 between 시작일 and 종료일
and rownum <= 1) rid
from 고객 a
where 가입회사 = 'C07') a, 고객별연체이력 b
where b.rowid = a.rid인라인뷰 안에서 스칼라 서브쿼리가 중첩되었다.
인라인 뷰에서는 고객 테이블 조건과 스칼라 서브쿼리를 가져온다.
스칼라 서브쿼리는 고객별 연체이력 테이블에서 조건에 맞는 rowid를 찾는다.
최종 인라인뷰와 고객별연체이력 테이블을 rowid로 다시 조인하여 최종 결과를 가져온다.
