Jonathan Lewis 의 퀴즈 접근법

Jonathan Lewis 의 퀴즈 접근법

Jonathan Lewis 의 Cost-Based Oracle Fundamental 이라는 책을보면
퀴즈에 대한 Jonathan의 재미있는 접근법과 사고방식을 엿볼수 있다.
물론 SQL을 이용한 퀴즈문제 이다.

퀴즈는 두 수학자의 대화로 부터 시작하며 수학자2의 세딸아이의 나이를 맞추는 것이다.
Jonathan은 본퀴즈에 대한 자신의 접근법이 성능과는 무관하다는 것을 먼저 이야기 하였다.
퀴즈의 제약조건은 다른 언어(3GL 또는 4GL)의 도움없이 순수한 select 쿼리 하나로만 이루어져야 한다.
수학자1이 나이를 맞추어야 하며 수학자1이 힌트를 요구할때마다 수학자2가 적절한
힌트를 주는 방식으로 대화가 진행된다.

수학자1 : Do you have any children ?
수학자2 : Yes, three.
수학자1 : How old are they ?
수학자2 : Multiply their ages together and you get 36 --> 세딸의 나이를 곱하면 36임을 알수있다.
수학자1 : That's not enough information to work out the answer -->위의정보로는 부족하므로 힌트를 더 요구한다.
수학자2 :Add their ages together and the answer is the same as the number of people in this room --> 세딸의 나이를 더하면 이방의 사람수와 같음을 알수 있다.
수학자1 :That's still not enough information to work out the answer --> 그것만으론 부족해서 힌트를 더 요구한다.
수학자2 :The oldest daughter has a pet hamster with a wooden leg. -->가장 나이가 많은 딸이 햄스터를 키운다.
수학자1 :Thank you. I've got the answer. --> 수학자1이 드디어 정답을 맞추었다.

위의 대화를 종합하여 필자는 다음과 같은 결론을 내렸다.

1.세딸의 나이의 곱이 36 이다. 따라서 1*1*36 일수도 있고 1* 2* 18 일수 도 있고 나머지조합도 여럿일수 있다.
2.세딸의 나이의 합은 그방의 사람의 수와 일치하나 세딸의 나이의곱 = 36 and 세딸의 나이의 합이 같은 조합 >= 2
위의 조건으로도 조합이 여러개 생겨서 정답을 구할수 없으므로 수학자 1이 힌트를 더요구하고 있다.
3.가장 나이가 많은 딸이 한명임을 알수있다. 즉 막내와 둘째는 나이가 같을수 있지만 장녀는 둘보다 나이가 많다는 것이다.

select 문 한방으로 퀴즈를 풀수 있어야 한다.
이제부터 Jonathan의 방식대로 한줄 한줄씩 풀어보자.

먼저1~36 까지의 나이 LIST 를 만든다.
with age_list as ( select rownum age from all_objects where rownum <= 36),

1)위에서만든 LIST 로 곱이 36인 막내, 둘째, 첫째아이의 나이조합을 만들고
나이의 합과 곱도 만든다.
product_check as (
select age1.age as youngest,
age2.age as middle,
age3.age as oldest,
age1.age + age2.age + age3.age as summed,
age1.age * age2.age * age3.age as product
from age_list age1,
age_list age2,
age_list age3
where age2.age >= age1.age
and age3.age >= age2.age
and age1.age * age2.age * age3.age = 36 ),


2)세딸의 나이를 더하면 이방의 사람수와 같으나 그조합이 여러개임
summed_check as
(
select youngest, middle, oldest, summed, product
from ( select youngest, middle, oldest, summed, product,
count(*) over (partition by summed) ct
from product_check )
where ct > 1 --> 나이의 합의 조합이 2개 이상 나와야 한다.
)

3)가장 나이가 많은 딸이 한명임을 이용한 조건
select *
from summed_check
where oldest > middle;

With 문의 특징과 분석함수를 이용하여 단계적(절차적)으로 퀴즈를 풀고 있다.
유용하고 수학적인 접근법이다.

여러분이라면 더멋지게 퀴즈를 풀수 있지 않을까?

서브쿼리 팩토링(with 서브쿼리) 의 한가지 주의사항은 인라인뷰와는 다르게 no_merge 힌트등을 쓸필요가 없고 (꼭써야 한다면 서브쿼리에 Merterialize 힌트를 사용한다) 서브쿼리에 inline힌트를 써서 옵티마이져 자신이 서브쿼리를 최적화 한다는데 묘미가 있다고 Jonathan은 밝히고 있다.
아래 압축파일중에 9장 with_subq_01~02 부분을 보면된다. 꼭 테스트 해보기 바란다.
관련 SQL:http://www.apress.com/book/downloadfile/2450