수강 신청 대상자 전체 목록을 추출하는 최적 SQL을 작성하시오.
-- 서브쿼리
SELECT
FROM (
SELECT
A.회원ID,
A.회원명,
A.SQLP취득여부,
A.회원등급코드,
CASE WHEN EXISTS (SELECT /*+ */ 'x' FROM 수강생 WHERE 회원ID = A.회원ID AND 과목코드 IN ('01', '02', '04')) THEN 'Y'
ELSE 'N' 수강여부
FROM 디비안포럼회원 A
) B
WHERE CASE WHEN B.수강여부 = 'Y' THEN 1
WHEN B.회원등급코드 IN ('우수', '열정', '전문가') AND B.SQLP취득여부 = 'Y' THEN 1
ELSE 0 END = 1;
;
회원ID | 회원명 | 회원등급코드 | SQLP취득여부 | 수강여부
------------------------------------------------
m01 AAA '새싹' N Y ---> 결과집합(O)
m02 BBB '새싹' Y Y ---> 결과집합(O)
m03 CCC '전문가' N Y ---> 결과집합(O)
m04 DDD '전문가' Y Y ---> 결과집합(O)
------------------------------------------------
-> 수강만하면 [등급코드, 취득여부]와 무관하다
------------------------------------------------
m02 이순신 '새싹' N N ---> 결과집합(X)
m03 임꺽정 '새싹' Y N ---> 결과집합(X)
m04 유관순 '우수' N N ---> 결과집합(X)
m05 강감찬 '우수' Y N ---> 결과집합(O)
------------------------------------------------
-> 수강안했으면 [등급코드, 취득여부]를 동시 만족을 충족
모범답안 발송이 늦어졌습니다. 연말연초에 바쁜 일이 쌓이다 보니... 죄송합니다.
아래 해설과 모범답안 보시고, 오류나 의견 있으면 언제든 회신 부탁합니다.
수강 신청 대상자 전체 목록을 추출하는 최적 SQL을 작성하시오.
https://cafe.naver.com/dbian/7307
위 문제에 대해 일반 개발자라면 쿼리를 아래와 같이 작성할 것이다.
select *
from 디비안포럼회원 o
where (o.SQLP취득여부 = 'Y' and o.회원등급코드 in ('02', '03', '04') )
or exists (select 'x'
from 수강생 s
where s.회원ID = o.회원ID
and s.과목코드 in ('01', '02', '04')
)
위 쿼리는 성능상 두 가지 문제를 안고 있다. 첫째, 디비안포럼회원 테이블을 Full Scan 한다는 점, 둘째, 대다수 회원을 수강생 테이블과 NL 방식으로 서브쿼리 필터링한다는 점이다.
튜너라면, 성능 문제를 해결하기 위해 OR 조건에 대한 UNION ALL 분기를 고려할 텐데, UNION ALL 분기할 때는 집합적으로 오류가 발생하지 않도록 주의해야 한다.
< 집합적 오류 1 >
select *
from 디비안포럼회원 o
where SQLP취득여부 = 'Y'
and 회원등급코드 in ('02', '03', '04')
union all
select *
from 디비안포럼회원 o
where exists (
select 'x'
from 수강생 s
where s.회원ID = o.회원ID
and s.과목코드 in ('01', '02', '04')
)
à 브랜치 위쪽과 아래쪽 조건을 모두 만족하는 회원이 중복 출력되는 문제
< 집합적 오류 2 >
select *
from 디비안포럼회원 o
where SQLP취득여부 = 'Y'
and 회원등급코드 in ('02', '03', '04')
**union all**
select *
from 디비안포럼회원 o
where SQLP취득여부 = 'N'
and exists (select 'x'
from 수강생 s
where s.회원ID = o.회원ID
and s.과목코드 in ('01', '02', '04')
)
à SQLP를 취득했고 회원등급코드가 '00' 또는 '01' 또는 '05'인 회원 중에도 '친절한 SQL 튜닝' 또는 '오라클 성능 고도화' 또는 'SQLP' 과목을 수강한 회원이 있을 수 있다. 그런데 아래쪽 브랜치에 SQLP취득여부 = ‘N’ 조건을 추가하면, 그런 회원들이 제외된다.