SQLP 19회차 실습 1번 문제

 SQLP 19회차 실습 1번 문제 

 

  문제를 참고한 블로그 주소 

   - 꿈을 펼처라 님의 블로그    

  simverse.tistory.com/85 

1) 내가 생각하는 답 

--- query 

select /*+ leading(b a) use_nl(a) index(a 고객_pk)  index(b 계약_x02) */ a.고객번호, a.고객이름, a.주민번호, b.계약일, b.계약번호, b.서비스시작일, b.서비스완료일 

  from 고객 a , 계약 b 

  where a.고객번호 = b.고객번호 

   and b.계약일 between :C and :D 

   and not exists ( select /*+ index(a 고객_x01) */ 'x' 

                     from 고객 c

                    where (a.고객이름, a.주민번호) in (:A, :B)

                   )

  union all 

select /*+ ordered use_nl(b) index(b 계약_x01) */ a.고객번호, a.고객이름, a.주민번호, b.계약일, b.계약번호, b.서비스시작일 b.서비스완료일 

  from 고객 a , 계약 b 

  where a.고객번호 = b.고객번호 

   and b.계약일 between :C and :D 

   and exists ( select /*+ index(a 고객_x01) */ 'x' 

                  from 고객 c 

                 where (a.고객이름, a.주민번호) in (:A, :B)

                   and a.rowid = c.rowid

              )

;

-- index 

고객_pk = 고객번호 

고객_x01 = 고객이름 + 주민번호 --- 가능하면 이 컬럼들에 not null 제약이 있으면 더 좋음 

계약_pk = 고객번호 + 계약번호 

계약_x01 = 고객번호 + 계약일 

계약_x02 = 계약일  

2) 이에 대한 나의 해설 

 - 이 문제를 풀어보면서 가장 고민 했던 것은 where 절에 exists 서브쿼리 작성이었다. 

   위의 쿼리를 보면 exists 일때는 where 절에 a.rowid = b.rowid라고 조건을 명시한다. 

   그러나 not exists 일때는 a.rowid = b.rowid를 넣어주지 않았다. 

   여기서 exists 일때 해당 조건을 넣어 준것은 서브쿼리 캐싱 기능을 사용하기 위해서이다. 

   조건이 exists 인 경우 해당 조건을 인덱스를 통해서 부분 범위 처리를 하게 되면 

   random access 양을 크게 줄일 수 있다. 

   그러나 이때 a.rowid = b.rowid 조건을 사용하지 않으면 서브쿼리 캐싱 기능을 사용할 수 없어서 

   인덱스가 조인 후 fiter 조건으로만 사용 될 것이다. 

   그런 비효율을 줄이기 위해서 a.rowid = b.rowid 조건을 넣어준다. 

   그런데 이때 not exists 에서는 a.rowid = b.rowid 를 넣어준 것은 비효율에 문제와 함께 잘못 된 값을 출력하기 때문이다. 

   not exists 시에는 고객이름과 주민번호가 모두 테이블에 존재할 시  'no rows selected' 가 되어야 하는데 

   a.rowid = b.rowid 조건을 추가하게 되면 조인 시 결과 값이 출력 될 것이다. 

   ( 이 부분은 설명이 끝나고 간단한 예시로 증명해볼 수 있다. )  

   그리하여 not exists 부분에는 a.rowid = b.rowid 조건을 빼버렸다. 

   그리고 추가로 고객 테이블의 where 절에 조건을 저런식을 넣어준 것은 인덱스를 사용할 수 있게 함이다.

   그리고 union all 을 기준으로 exists 부분 쿼리와 not exists 부분 쿼리에 힌트가 달라졌는데 이는 driving table 순서가 바뀜을 의미한다. 

   exists 의 경우 이름과 주민번호의 조건으로 소수의 값만 출력 될 것으로 생각 된다. 

   그렇게 되면 nl 조인 시 계약 테이블에 조인 되는 양을 줄일 수 있다. 

   그러나 해당 조건이 없다면 고객 테이블에서는 부분 범위 처리를 할 수 있는 값이 없다. 

   그래서 해당 순서와 같이 한다면 100만 건을 조인 시도 할 것이다. 

   그래서 not exists 부분에서는 조인 순서를 바꿔주었다. 

   7일 이기 때문에 최대 7000 건이다. 100만 건 보다는 훨씬 줄일 수 있을 것으로 생각하였다. 

   또한 index 부분을 보면 '고객번호 + 계약일' 인덱스가 있음에도 불구하고 따로 '계약일' 인덱스를 따로 생성하여 사용하였다. 

   이 쿼리에서는 계약일을 between 범위 처리를 해야 한다. 

   해당 쿼리에 내용상 in 스타일로 변경할 수 없다. 

   그렇다고 skip scan을 써도 제대로 효과가 없을 것으로 생각했는데 그 이유는 고객번호의 변별력이 좋기 때문에디 고객번호는 pk 이기 때문이다. 

   고객번호는 변별력이 매우 큰 컬럼임이다. 

   이처럼 선두 컬럼이 변별력이 클때는 skip scan은 크 효과를 거둘 수 없다. 

   그래서 계약일 인덱스를 생성하였다. 고객이름과 주민번호가 입력되지 않을 시에는 범위 처리가 가능하게 하는 컬럼은 계약일 밖에 없기 때문이다. 

   그래서 인덱스가 2개 생성됨에도 불구하고 자주 사용되는 쿼리라면 '계약일' 인덱스가 필요하다고 생각하였다. 

3) not exists 에 대한 내용에 대한 참고 쿼리 

    - 해당 emp 테이블은 오라클 샘플 emp table 이다.   

select * 

 from emp a 

 where not exists ( select 'x' 

                      from emp b

                     where ename = 'KING'

                      and a.rowid = b.rowid 

                   )

 ;

--- 14 rows 를 출력 

select * 

 from emp a 

 where not exists ( select 'x' 

                      from emp b

                     where ename = 'KING' 

                   )

 ;

  

--- no rows selected 

   

 * 하지만 이부분에서 a.rowid = b.rowid 조건을 넣었을 때 14 rows 가 출력되는 이유는 잘 모르겠다. 

   ename = 'KING'  값이 있는 ' no rows selected '가 되어야 한다고 생각하는데 말이다.