튜닝 - exists, NL semi join

[데이터]

상품 : 1,000건

계약 : 5,000만 건

 

- 1년 간 계약 건수는 500만 건

 계약 row가 만 건으로 10년 치 데이터가 쌓여 있음을 알 수 있다. 

- 상품유형코드를 '=' 조건으로 검색할 때의 평균 카디널리티는 100

Cardinality 란 중복도가 낮으면 높고, 중복도가 높으면 낮다. (여자, 남자 Car-di- 는 2이다.)

즉, 100이란 숫자는 상품유형코드에 대한 중복도가 매우 낮음을 의미한다. 

카디널리티로 선택도(Selectivity)를 구할 수 있다. 

(Selectivity : 전체 레코드 중에서 조건절에 의해 선택될 것으로 예상되는 레코드의 비율% 이다.)

선택도 = 카디널리디 / 총 레코드 수, 100/1000 = 10%

500만 건의 10%, 50만 건 중 100(카디널리티)개의 상품 집합을 쿼리에서 가져오려고 한다.

 

 

[인덱스 구성]

상품_PK : 상품번호

상품_X1 : 상품유형코드

계약_PK : 계약번호

계약_X1 : 계약일 

계약_X2 : 상품번호

 

select distinct p.상품번호, p.상품명, p.상품가격, p.상품분류코드

from 상품 p, 계약 c

where p.상품유형코드 = :pclscd

and c.상품번호 = p.상품번호

and c.계약일자 >= trunc(add_months(susdate, -12))

 

[실행계획] 

상품_X1 > 상품 테이블 엑세스 > 계약_X2 > NL > 계약 테이블 엑세스 > NL > 필터 > Hash

 

실행 계획대로 수행되도록 인덱스 재구성과 힌트를 작성해야 한다.

/*ordered nl(c) */

 

인덱스 재구성 :

상품_X1 : 상품유형코드 + 계약일자

 

<유형1>

select p.상품번호, p.상품명, p.상품가격, p.상품분류코드
from 상품 p
where p.상품유형코드 = :pclscd
and exists ( 
                  select /* unnest nl_sj */ 'X'
                  from 계약 c                   
                  where c.상품번호 = p.상품번호
                  and c.계약일자 >= trunc(add_months(susdate, -12))                    
);

</유형1>

 

<유형2>

select p.상품번호, p.상품명, p.상품가격, p.상품분류코드
from 상품 p
where p.상품유형코드 = :pclscd
and exists ( 
                  select /* no_unnest */ 'X'
                  from 계약 c                   
                  where c.상품번호 = p.상품번호
                  and c.계약일자 >= trunc(add_months(susdate, -12))                    
);

</유형2>

 

개념

• exists를 사용하면 실행 계획 상에서 nested loops semi join(nl semi join)이라는 조인 방식이 자주 사용된다. 존재 여부만 확인(매칭 여부만 필요할 때)하여 첫 매칭 후 중단한다. 빠른 필터링, 적은 메모리 사용이 특징이다.
• unnest ? 메인 쿼리와 조인 방식으로 병합(unnest)하도록 강제한다. 기본적으로 필터 방식일 수도 있는 서브쿼리를 조인으로 변환하여 성능 향상을 유도 한다. 
• no_unnest ? 해당 서브쿼리를 unnest하지 않고 필터 방식으로 유지하라는 의미이다.