서브쿼리 ORDERBY_NUM() 최적화 문제

by asteroid posted Jan 29, 2010
서브쿼리에 정렬된 값의 순서에 맞춰 필터링된 결과를 바탕으로 전체 쿼리 결과를 생성하고자 할 경우, 의도하지 않은 방향으로 쿼리 최적화가 이루어지는 것 같습니다. 즉 다음과 같은 쿼리가 있을 때,

select x.* from (
    select t.* from
    (
        select 1 v, 1 c from db_root
        union all select 2, 1 from db_root
        union all select 3, 0 from db_root
        union all select 4, 1 from db_root
        union all select 5, 1 from db_root
    ) t
    order by t.v for orderby_num() between 2 and 4
) x
where x.c = 1

이 쿼리에서 의도한 결과는 다음과 같습니다.
v c
2 1
4 1
그러나 의도와는 다르게 다음의 결과가 도출됩니다.
v c
2 1
4 1
5 1

즉, 서브쿼리의 ORDER BY 구문이 최적화를 거치면서 서브쿼리에 귀속하지않고 최종 결과에 대한 정렬 동작으로 잘못 처리되는 것 같습니다.

이 문제를 해결하기 위한 한가지 우회방법으로 서브쿼리에 불필요한 컬럼 정의를 넣는 방법인데요, 다음처럼 쿼리를 변경하는 것 입니다.

select x.* from (
    select t.*, orderby_num() from
    (
        select 1 v, 1 c from db_root
        union all select 2, 1 from db_root
        union all select 3, 0 from db_root
        union all select 4, 1 from db_root
        union all select 5, 1 from db_root
    ) t
    order by t.v for orderby_num() between 2 and 4
) x
where x.c = 1


그리고 일부 DBMS의 경우 서브쿼리에 ORDER BY 구문을 사용하는데 있어 제약이 있는 경우가 있어서 해당 내용에 대해 온라인 도큐먼트를 참고하려 했는데요, SELECT 구문의 서브쿼리 정의에는 <query statement>라는 모호한 내용만 있습니다. 도큐먼트 어디에도 <query statement>에 대한 정의가 없는데요, 제 생각에는 <query state>로 변경하는 것이 정황상 맞는 것 같습니다.

고쳐주세요!