[Oracle 튜닝] 서브쿼리 최적화 기법

서브쿼리란?

서브쿼리는 SQL 쿼리 내에 중첩된 또 다른 쿼리를 의미 합니다. 이는 복잡한 데이터 검색 작업을 수행할 때 매우 유용하지만, 잘못 사용하면 성능 저하의 원인이 될 수 있습니다. 따라서 서브쿼리를 최적화하는 것은 데이터 베이스 성능 향상에 중요한 역할을 합니다. 

서브쿼리 최적화 기법

1.서브쿼리 언네스트닝(Unnesting)

오라클 옵티마이저는 종종 서브쿼리를 "언네스트"하여 조인으로 변환합니다. 이 방식은 필터링 작업을 반복하여 실행하는 대신 조인을 사용하여 성능을 향상 시킵니다.

2.스칼라 서브쿼리 캐싱

스칼라 서브쿼리 캐싱은 상관 서브쿼리의 결과를 로컬 캐시에 저장하여 동일한 입력에 대해 서브쿼리를 반복 실행하지 않도록 합니다. 이는 특히 동일한 값이 여러 번 사용되는 경우에 효과적 입니다. 

3.서브쿼리 푸싱(Pushing)

서브쿼리 푸싱은 실행 계획에서 서브쿼리의 위치를 제어하는 기법입니다. 'push_subq' 힌트를 사용하여 서브쿼리를 가능한 빨리 실행하도록 할 수 있습니다. 이는 데이터를 더 일찍 필터링 하여 전체 작업량을 줄일 수 있습니다.

4.집계 서브쿼리 제거

옵티마이저는 때때로 분석 함수를 사용하여 자체 참조 서브쿼리를 내부적으로 재작성할 수 있습니다. 이 기법은 쿼리의 성능을 크게 향상시킬 수 있습니다.

5.서브쿼리 병합(Coalescing)

여러 개의 유사한 서브쿼리가 있는 경우, 옵티마이저는 이들을 하나의 서브쿼리로 병합할 수 있습니다. 'coalesce_sq' 힌트를 통해 제어할 수 있으며, 11.2.0.1 버전 이후부터 사용 가능합니다.

6.서브쿼리 구체화(Materialization)

서브쿼리 경과를 임시테이블에 저장하는 방식으로, 특히 서브쿼리가 여러 번 실행되어야 할 때 유용합니다.

7.EXISTS 전략 사용

IN 서브쿼리를 EXISTS로 변환하며 최적화 할 수 있습니다. 이 방식은 서브쿼리가 검사해야 할 행의 수를 제한하는 데 도움이 됩니다.

최적화 팁

1. 인덱스 활용 : 서브쿼리에서 사용되는 컬럼에 적절한 인덱스를 생성하여 성능을 향상 시킵니다.
2. 조인으로 변환 : 가능한 경우 서브쿼리를 조인으로 변환하여 성능을 개선합니다.
3. NOT NULL 선언 : 실제로 NULL이 아닌 컬럼은 NOT NULL로 선언하여 옵티마이저가 더 효율적인 실행 계획을 생성할 수 있도록 합니다.
4. 힌드 사용 : 'push_subq', 'no_push_subq','coalesce_sq','no_coalesce_sq' 등의 힌트를 사용하여 옵티마이저의 동작을 제어 합니다.
5. 실행 계획 분석 : EXPLAIN_PLAN을 사용하여 쿼리의 실행 계획을 분석하고, 비효율적인 부분을 식별 합니다.
6. 통계 정보 최신화 : 데이터 베이스 통계 정보를 주기적으로 업데이트하여 옵티마이저가 더 정확한 실행 계획을 생성할 수 있도록 합니다.

결론

서브쿼리 최적화는 오라클 데이터베이스 성능 향상의 핵심 요소입니다. 언네스팅, 캐싱, 푸싱, 병합 등의 기법을 적절히 활용하고, 실행 계획을 주의 깊게 분석하며, 필요한 경우 힌트를 사용하여 옵티마이저를 가이드하는 것이 중요합니다. 또한, 데이터베이스 구조와 쿼리 패턴을 지속적으로 모니터링하고 최적화하는 것이 장기적인 성능 향상의 열쇠입니다.
서브쿼리 최적화는 단순히 기술적인 측면뿐만 아니라 비즈니스 요구사항과 데이터의 특성을 깊이 이해하는 것이 필요한 복잡한 과정입니다. 따라서 지속적인 학습과 실험, 그리고 실제 환경에서의 테스트를 통해 최적의 접근 방식을 찾아나가는 것이 중요합니다.