SQL Tuning 31
SUM 집계를 SUM OVER 분석함수로 변환
메인쿼리와 inline view 가 같은 테이블을 같은 구간으로 두 번 ACCESS 하는 패턴은 분석함수 SUM(SUM(...)) OVER (PARTITION BY ...) 로 1회 액세스로 통합할 수 있습니다. 옵티마이저가 자동으로 해 주는 변환이 아니라 SQL 을 다시 써야 하는 패턴입니다. 핵심 정리 같은 데이터를 두 번 ACCESS ...
분석함수 — MAX 집계를 RANK OVER 함수로 변환
같은 테이블을 MAX 집계용으로 한 번, 상세 조회용으로 한 번 두 번 access 하는 패턴은 분석함수 RANK() OVER 로 한 번의 access 로 풀 수 있습니다. 사용하기에 앞서 PGA Sorting 부하 등 Trade off 에 대한 검토가 필요합니다. 핵심 정리 변환의 본질: WHERE (key, max_col) IN (SEL...
View Merging 으로 얻는 이익
View Merging 은 inline view / view 를 메인 쿼리와 한 쿼리블록으로 통합하는 옵티마이저 변환입니다. View 가 사라지면 옵티마이저는 더 풍부한 후속 변환 (조건절 전이, JPPD, view merging 자체로 가능해진 join 제거 등) 을 적용할 수 있어, 동일한 쿼리도 훨씬 효율적인 plan 으로 풀립니다. 핵심 정...
UNION ALL 을 PARTITION BY 로 변환 (Partition Outer Join)
같은 데이터에 대해 여러 키 (예: 부서별) 의 outer join 결과를 UNION ALL 로 합치던 패턴은 PARTITION BY 절을 OUTER JOIN 에 결합 하여 한 번의 쿼리로 풀 수 있습니다. plan 에는 MERGE JOIN PARTITION OUTER 가 등장하며 dimension 테이블 access 가 1 회로 줄어듭니다. 핵심...
UNION ALL 을 GROUPING SETS 로 변환
같은 테이블에 대해 여러 그룹화 수준 (예: 월별 + 일별) 을 UNION ALL 로 표현하면 같은 테이블을 N 번 풀 스캔합니다. GROUPING SETS 로 묶으면 옵티마이저가 TEMP TABLE TRANSFORMATION 으로 1 번 스캔 결과를 재사용하여 Cost 절감의 이점을 발생시킬 수 있습니다. 핵심 정리 변환의 목적: UNIO...
UNION ALL 사용시 공통으로 사용하는 테이블을 분리
Join Factorization (JF) 은 UNION ALL 의 위 / 아래 분기에서 공통으로 쓰이는 테이블 을 view 밖으로 끌어내어 한 번만 access 하도록 변환하는 옵티마이저 변환입니다. 본 글의 시나리오에서 SALES 풀 스캔이 2 회에서 1 회로 줄어들어 비용 또한 절반으로 떨어집니다. 핵심 정리 UNION ALL 양쪽 분...
SubQuery Unnesting
Subquery Unnesting 은 IN / EXISTS 형태의 서브쿼리를 FROM 절의 일반 join 으로 끌어올려 변환 하는 옵티마이저 변환입니다. 변환 후에는 결과집합이 작은 쪽이 driving 이 되어 메인 테이블의 인덱스를 정확히 lookup 합니다. 핵심 정리 (개념) 변환의 정의: 서브쿼리가 FROM 절 위로 올라가 일반 jo...
Star Transformation 을 이용해 Fact 테이블과 Dimension 테이블 Join
Star Transformation 은 OLAP / 데이터 웨어하우스 환경에서 대용량 Fact 테이블과 소용량 Dimension 테이블의 join 을 Bitmap Key Iteration 기반으로 풀어내는 옵티마이저 변환입니다. Fact 테이블의 dimension FK 컬럼에 Bitmap Index 가 필수이며, 본 글의 시나리오에서 Buffers 1...
SEMI JOIN 시 Driving 테이블로 변경 (Driving Semi Join)
일반적으로 Semi Join 의 driving 집합은 메인 쿼리 쪽으로 고정되지만, SEMIJOIN_DRIVER 힌트를 쓰면 서브쿼리 쪽을 driving 으로 둘 수 있습니다. 이 변환의 plan 은 Bitmap Key Iteration 으로 풀리는데, 이는 join 을 IN-list 형태로 변환하여 서브쿼리 결과를 인덱스 access 의 키로 사용하...
Scalar Subquery
Scalar Subquery 는 같은 입력 값에 대한 결과를 PGA 에 캐시합니다. 후행 테이블의 distinct 입력 값 종류가 적을수록 캐시 hit 가 늘어 후행 access 횟수가 줄어듭니다. 본 글은 NL OUTER JOIN 의 1,370 회 access 가 Scalar Subquery 변환으로 736 회로 줄어드는 사례입니다. 핵심 정리 ...
Scalar Subquery 와 일반 Join 간의 변환 시 1대M 관계 정의 (일반 조인을 Scalar Subquery 로)
일반 Join 을 Scalar Subquery 로 재작성할 때 1:1 관계는 단순 SELECT 로, 1:M 관계는 집계 함수 또는 ROWNUM <= 1 으로 처리해야 합니다. Scalar Subquery 는 메인 row 당 정확히 1 row 만 반환해야 하기 때문입니다. 핵심 정리 일반 Join → Scalar Subquery 변환 시...
Scalar Subquery Filter Push Down 으로 풀어내기
인라인 뷰 안에 scalar subquery 가 있고 그 결과 컬럼을 인라인 뷰 바깥에서 filter 조건으로 사용하면, 옵티마이저는 View Merging 으로 인라인 뷰를 메인쿼리에 통합한 뒤 FPD (Filter Push Down) 로 outer 컬럼을 새 subquery 안으로 밀어 넣어 scalar subquery 를 일반 subquery f...
PLAN Right Deep Tree VS Left Deep Tree (공식화)
동일한 4 테이블 hash join 도 build / probe 순서 (LEADING) 에 따라 PGA 사용량이 6 배 이상 차이 납니다. Right Deep Tree 는 큰 hash table 을 동시에 여러 개 메모리에 유지해야 해서 대부분 악성 plan 입니다. Left Deep Tree 로 유도하면 작은 결과만 점진적으로 hash table 로...
Paging 처리할 때 Join 전 먼저 Paging 처리하여 Join 부하 감소
페이징 쿼리(ROWNUM <= N) 에서 후행 테이블이 결과 건수에 영향을 미치지 않는다면, 선행 테이블에서 페이징을 먼저 수행한 뒤 후행 테이블을 join 하는 것이 압도적으로 빠릅니다. 핵심 정리 원리: 후행 테이블과 join 해도 결과 건수에 차이가 없다면, 선행 테이블에서 페이징(ROWNUM <= N + STOPKEY) 을...
Join Predicate Push Down(JPPD)
JPPD (Join Predicate Push-Down) 는 옵티마이저가 선행 테이블의 join predicate 를 후행 VIEW 안쪽으로 밀어 넣어, VIEW 가 결과집합을 만드는 시점에 미리 좁히는 변환입니다. 본 글은 자동 JPPD 와 수동 LATERAL 재작성을 같은 쿼리에서 비교하며, 결과적으로 VIEW 가 LATERAL view 처럼 동작...
Join Predicate Push Down(JPPD) 는 UNION ALL & UNION VIEW 가 있을 때 가능
선행 테이블과 후행 집합이 NESTED LOOPS JOIN 으로 수행될 때, 후행 집합이 Inline View / View 면 옵티마이저는 join predicate 를 그 안으로 push down 하여(JPPD) 인덱스 액세스 경로를 만듭니다. UNION ALL VIEW 는 그 6가지 발생 조건 중 하나입니다. 핵심 정리 JPPD 발생 조...
Join Predicate Push Down (JPPD) 는 Semi Join 상황에도 SubQuery 에 침투 가능
EXISTS 서브쿼리가 UNNEST 로 평탄화되어 SEMI JOIN 으로 풀릴 때도 JPPD 가 동작합니다. Unnest → Semi 변환 → JPPD 의 3단 변환 순서, 그리고 Predicate Information 위치로 push 여부를 판별하는 법을 정리합니다.
Join Predicate Push Down (JPPD) 는 Group By 가 있는 View 도 침투 가능 (2)
외부 GROUP BY + 인라인 뷰 GROUP BY 의 2단 집계 패턴에서도 JPPD 가 동작합니다. ORDERS 의 20 행이 인라인 뷰 안으로 한 행씩 밀려 들어가 SORT GROUP BY 가 부서당 124 행만 다루도록 압축되는 예시입니다.
Join Predicate Push Down (JPPD) 는 Group By 가 있는 View 도 침투 가능 (1)
GROUP BY 가 있는 인라인 뷰에도 외부 조인 술어를 뷰 안으로 밀어 넣을 수 있습니다. LATERAL 서브쿼리로 재작성하면 옵티마이저가 d.department_id 단위로 서브쿼리를 실행하므로, 원본의 GROUP BY 키 e.department_id 가 통째로 사라집니다.
HASH Semi Join
1:N 조인 후 1쪽 컬럼만 DISTINCT 로 추리는 패턴은 HASH JOIN + HASH UNIQUE 두 단계로 분해됩니다. EXISTS + UNNEST + HASH_SJ 로 HASH JOIN SEMI 를 유도하면 두 단계가 하나로 합쳐져 PGA 사용량과 처리 행 수가 모두 감소합니다.