analytic-sql과 상관 서브쿼리

안녕하세요 강의 열심히 듣고 있습니다. 감사드립니다.

[서브쿼리 실습-02] 수업에 대해 질문이 있습니다.

-- 상관 서브쿼리

select * from hr.emp_dept_hist_01 a where todate = (select max(todate) from hr.emp_dept_hist_01 x where x.empno=a.empno);

-- Analytic SQL

select * from ( select *, row_number() over (partition by empno order by todate desc) as rnum

from hr.emp_dept_hist_01 )a where rnum = 1;

위 두 가지 방법의 성능 차이에 대해 궁금합니다. (고객 데이터 분석 업무를 수행하고 있는데, 데이터가 커서 성능이 항상 이슈더라구요)

강의에서 말씀해주신대로, online 처리를 해야한다면 analytic-sql은 sorting을 해야하기 때문에 상관 서브쿼리가 더 성능이 좋을 것 같습니다.

강의에서 분석을 할 때는(배치로 처리할 때) analytic sql이 더 성능이 좋다고 하셨는데 그 이유는 무엇인가요??

안녕하십니까,

배치 처리의 경우 보통은 서브쿼리보다 analytic SQL이 성능이 더 좋습니다만, 반드시 그런 경우는 아닐 수 있습니다.

먼저 위의 서브쿼리는 emp_dept_hist_01 테이블을 두번 액세스하고(메인 쿼리에서, 그리고 서브쿼리에서) 연결 시켜야 합니다. 반면에 analytic SQL은 emp_dept_hist_01 테이블을 한번만 읽어도 됩니다.

물론 analytic SQL은 partition by와 order by에 따른 정렬(Sorting)의 부하가 발생합니다. 반면에 위의 서브쿼리는 emp_dept_hist_01 테이블을 셀프 Hash Join으로 연결해 줘야 합니다.

어떤게 더 빠를지는 실제 돌려봐야 하지만, 데이터가 대용량이라면 analytic SQL이 더 빠를 겁니다. 다만 처리해야할 데이터가 너~무 크다면(예를 들어 10억건 이상) analytic SQL의 정렬의 부하가 테이블 Hash 조인의 부하보다 더 커질 수 있습니다.

온라인이라고 analytic SQL이 무조건 느리지 않습니다. 정렬해야할 데이터가 적다면 Analytic SQL이 서브쿼리 보다 더 빠를 수 있습니다. 다만 큰 차이는 없을 것 같습니다.

요약 드리자면 서브쿼리는 테이블간의 연결작업을 수행해야 합니다. 또한 SQL 실행 계획이 잘못 풀리면 대용량 데이터 연결인데도 Hash 조인이 아니라 Nested Loop 조인으로 풀리거나, buffer 등을 과도하게 사용하여 성능을 해칠 수 있습니다. 때문에 대용량 데이터의 분석 작업은 Analytic SQL이 더 선호됩니다.

감사합니다.

인프런 커뮤니티 질문&답변