서브쿼리 조인

• 앞선 쿼리 튜닝들의 경우 두세 개 테이블을 단순한 형태로 조인하는 예제만 다루었다.
• 실무에서는 복잡한 서브쿼리와 조인하는 형태를 흔히 보게 된다.
• 옵티마이저가 서브쿼리 조인을 어떻게 처리하는 지 이해할 필요가 있고, 이를 통해 튜닝을 진행해야 한다.

서브쿼리 변환이 필요한 이유

• 쿼리 변환이란, 의미상으로는 동일하지만 실행계획을 변경해서 가장 빠르고 효율적으로 결과를 반환하는 것을 말한다.

• 서브 쿼리는 sql 문에서 별도의 쿼리 블록을 말한다.

  • 인라인 뷰 : from 절에 사용한 서브쿼리 블록을 말한다.
  • 중첩된 서브쿼리 : 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리를 말한다. 특히, 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 Correlated Sub Query 라고 부르기도 한다.
  • 스칼라 서브 쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리를 말한다. 즉 select 문에 하나의 값으로 할당되는 쿼리를 말한다.

• 일반적으로 위 서브쿼리들도 하나의 실행계획 쿼리블록으로 취급한다. 즉 독단적으로 실행계획을 작성한다(따로).

• 책에서는 비유적으로 인테리어를 하는데 시공업자가 2명인 상황이라고 비유하고 있다. 즉 항상 효율적인 것은 아니라는 것을 의미하는 것 같다.

서브쿼리와 조인

• 메인쿼리와 서브쿼리는 계층적이다. 즉 각자 독단적으로 진행할 수 없다는 것을 의미한다.
• 조건절에 exist와 함께 사용하는 nested 서브쿼리를 예시로 들고 아래 오퍼레이션들을 고려해보자.

필터 오퍼레이션

• 쿼리에서 힌트로 no_unnest 를 사용하는 경우가 있다. 옵티마이저에게 힌트를 주는 것이다.
• 실행계획에 Filter 라는 키워드가 뜰 것이다.
• 기본적으로 NL 조인과 처리 루틴이 같다. 조건절에 사용하는 만큼 NESTED LOOP 형태로 진행한다는 것을 의미한다. 즉 조건절에 사용하는 컬럼에 인덱스를 두는 것을 고려해야 할 것이다.
• 그럼에도 NL 조인과 차이점이 존재한다.

  1. 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간, 서브쿼리 진행을 멈추고 메인쿼리로 돌아간다.
  2. 캐싱 기능이 존재한다. NL 조인처럼 매번 인덱스 트리에 접근하는 것이 아니고, 먼저 캐시에 존재하는지 확인하고, 만약 존재한다면 바로 캐치해낸다. 캐시는 PGA 영역에 저장한다는 것이 특징이다.
  3. 서브쿼리는 일반 NL 조인과 달리, 메인쿼리에 종속되기 때문에 조인 순서가 고정된다.

서브쿼리 Unnesting 오퍼레이션

• 쿼리에서 힌트로 unnesting 을 사용하는 경우가 있다. 이것 또한 옵티마이저에게 힌트를 주는 것이다.
• 실행계획에 NESTTED LOOPS(SEMI) 키워드가 발생한다.
• 의미적으로는 중첩된 상태를 풀고, 동일선상에 두고 실행해라 (Flattening 한다라고도 표현) 라는 의미를 갖는다.
• 필터 오퍼레이션과 마찬가지로 메인쿼리를 중심으로 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 처리한다.
• Flattening 된 만큼, 메인쿼리보다 서브쿼리가 먼저 실행될 수 있다는 점이 차이점이다.

서브쿼리 Pushing

• 서브쿼리 필터링을 가능한 앞 단계에서 처리하도록 강제하는 기능은 push_subq/no_push_subq 힌트로 제어한다.
• 이 기능은 no_unnest 서브쿼리에서만 작동한다.
• 개인적으로는, 결국 그럼 Unnesting Operation과 동일한거 아닌가? 하는 생각이 든다.

뷰(View)와 조인

스칼라 서브쿼리 조인