SQL Tuning summary No.4
join
- join을 하는 이유는 원하는 데이터가 다른 table에 있어서다.
- join은 아래와 같이 4개의 방식이 있다.
- NL
- WAS는 대부분 이걸 채택한다.
- sort merge
- 거의 안쓰인다.
- hash
- 대용량 table batch 시 사용된다.
- subquery
- inline view 형태로 진행되는데, 피할 수 있으면 피해야한다.
- 다만 inline view로 해야 join하는 table의 양이 줄어든다면, 그렇게 해야 한다.
- NL
NL 조인
- NL은 중첩 루프문과 같은 수행 구조를 지닌다.
- 일반적으로 outer와 inner 모두 index를 사용하는 편이다.
- 다만 outer는 size가 크지 않으면 index를 하지 않기도 한다. 어차피 1번 scan이기 때문이다.
- 하지만 inner는 여러 번 반복 scan되기 때문에 반드시 join record column에 index를 사용하는 게 좋다.
SELECT e.사원명, c.고객명, c.전화번호
FROM 사원 e, 고객 c
WHERE e.입사일자 >= '19960101'
AND c.관리사원번호 = e.사원번호
- 위와 같은 경우, outer인 사원의 입사일자는 1번 조회다.
- 그렇게 scan해온(index이든 table이든) record를 inner인 고객의 관리사원번호 조건에 맞게 다시 record를 scan한다.
- 따라서 inner는 outer의 갯수만큼 반복 scan을 하기에 반드시 효율적이어야만 한다.
- 더 자세한 예시를 들어보자.
- 아래와 같은 예시가 있다.
SELECT /*+ ordered use_nl(c) index(e) index(c) */
e.사원번호, e.사원명, e.입사일자, c.고객번호, c.고객명, c.전화번호, c.최종주문금액
FROM 사원 e, 고객 c
WHERE c.관리사원번호 = e.사원번호
AND e.입사일자 >= '19960101'
AND e.부서코드 = 'Z123'
AND c.최종주문금액 >= 20000
--
사원_PK: 사원번호
사원_X1: 입사일자
고객_PK: 고객번호
고객_X1: 관리사원번호
고객_X2: 최종주문금액
|ID |Operation |Name |Rows |Bytes |Cost
------------------------------------------------------------------
0 |SELECT STATMEENT | |5 |58 |
1 | NESTED LOOPS | |5 |58
2 | TABLE ACCESS BY INDEX ROWID |사원 |3 |20
3 | INDEX RANGE SCAN |사원_X1 |5
4 | TALBE ACCESS BY INDEX ROWID |고객 |5 |76
5 | INDEX RANGE SCAN |고객_X1 |8
- 그럼 아래와 같은 과정을 거쳐 NL join이 일어난다.
1. 조건절 2번을 만족하는 index record를 찾기 위해 사원_X1 index scan
2. 사원_X1 index에서 읽은 ROWID로 사원 table access해 테이블 filter 조건으로 조건절 3번 사용
3. 조인 조건인 관리사원번호를 만족하는 index record를 찾기 위해 고객_X1 index를 range scan
4. 고객_X1 index에서 읽은 ROWID로 고객 테이블을 access해 테이블 filter 조건으로 조건절 4번 사용
- 그런데 1번부터 4번은 단계별로 완료한 뒤 진행이 아니라, 1번에서 찾아진 index record마다 반복되는 형태다.
- 아래 이미지를 보면 이해하기 쉬울 것이다.
- 사원 테이블에서 얻은 record가 3개인데, 사원의 사원번호가 10, 12, 15라고 해보자.
- 그럼 c.관리사원번호가 10인 경우에 최종주문금액이 20,000이 넘는 고객의 record를 가져온다.
- c.관리사원번호가 12인 경우에 최종주문금액이 20,000이 넘는 고객의 record를 가져온다.
- c.관리사원번호가 15인 경우에 최종주문금액이 20,000이 넘는 고객의 record를 가져온다.
- 이렇게 outer에서 가져온 각 record에 맞는 고객의 record를 모두 종합하면 그게 NL join의 결과물이다.
- 튜닝포인트는 4가지다.
- 사원 index를 읽은 후 사원 table에 access하는 횟수
- 많은 random access가 발생했다면, 부서코드를 사원_X1 index에 추가하는 게 좋다.
- 사원 table에서 조건을 만족하는 filtering된 record와 결합하는 고객_X1 index와 결합하는 join횟수
- 부서코드 Z=123을 만족하는 건수가 3개였기에, 해당 갯수만큼 조인시도를 했다.
- 조인을 위해 고객_X1 index를 탐색하는 range scan 횟수가 많을 수록 성능이 안 좋아진다.
- 고객_X1 index를 읽고 고객 테이블 access하는 횟수
- 많은 random access가 발생했다면, 최종주문금액 컬럼을 추가하는 방안을 고려해야 한다.
- 맨 처음 access하는 사원_X1 index에서 얻은 range scan의 양
- 처음에 range가 넓으면 1-3번이 모두 늘어날수밖에 없다.
- 사원 index를 읽은 후 사원 table에 access하는 횟수
- 이제 그럼 실제 튜닝을 해보자.
- 만약 위의 sql이 아래와 같은 row를 뱉었다면?
- 사원에서 index로 가져온 record가 2780개이므로, random access도 똑같다.
- 그런데 가져온 row의 갯수가 3개밖에 없다. 너무 많이 필터링된 것이다.
- 이 경우 table filter 조건을 index에 포함해 random access를 줄이는 걸 고민해야 한다.
- 그래서 부서코드를 추가했다.
|ID |Operation |Name |Rows |Bytes |Cost
------------------------------------------------------------------
0 |SELECT STATMEENT | |5 |58 |
1 | NESTED LOOPS | |5 |58
2 | TABLE ACCESS BY INDEX ROWID |사원 |3 |20
3 | INDEX RANGE SCAN |사원_X1 |2780
4 | TALBE ACCESS BY INDEX ROWID |고객 |5 |76
5 | INDEX RANGE SCAN |고객_X1 |8
- 그랬더니 아래와 같이 바뀌었다.
- trace가 바뀐건 오라클 9iR2버전부터다.
- cr은 논리 block 요청(버퍼에서 읽은 block)
- pr은 디스크에서 읽은 block
- pw는 디스크에다가 쓴 블록 수
- 사원_X1 index에서 읽은 index record가 table filtering 없이 그대로 읽은 셈이니 range scan의 비효율은 없었다.
- 물론 이게 실제로 유효한 random access였는 지는 면밀히 살펴봐야 할 것이다.
- 그러나 2780개를 가지고 가서 고객 table과 join한 결과는 겨우 5 row다. 매우 비효율적인 join인 셈이다.
- 이럴 때는 join 순서를 바꾸는 것을 고려해야 한다.
|ROW |Operation
------------------------------------------------------------------
5 | NESTED LOOPS(cr=112 pr=34 pw=0 time==122 us)
2780 | TABLE ACCESS BY INDEX ROWID OF 사원(cr=105 pr=32 pw=0 time=118 us)
2780 | INDEX RANGE SCAN OF 사원_X1(cr=102 pr=31 pw=0 time=16)
5 | TALBE ACCESS BY INDEX ROWID OF 고객(cr=7 pr=2 pw=0 time=4 us)
8 | INDEX RANGE SCAN OF 고객_X1(cr=5 pr=1 pw=0 time=0 us)
NL 조인에서 조건절 잘 쓰기
- 아래 sql을 보자.
- 해당 sql을 보고 index를 어떻게 만들어야할까 생각했는가?
- 만약 index를 PRA_HST_STC_N1 table에 (SALE_ORG_ID, STRD_GRP_ID, STRD_ID, STC_DT)로 만들려 했다면 실수한 것이다.
- 일단 전부 a를 왼쪽에 배치한거부터 매우 잘못되었다. 이러면 가시성이 매우 떨어질 수 밖에 없다.
SELECT *
FROM PRA_HST_STC a, ODM_TRMS b
WHERE a.SALE_ORG_ID =:sale_org_id
AND a.STRD_GRP_ID = b.STRD_GRP_ID
AND a.STRD_ID = b.STRD_ID
ORDER BY a.STC_DT desc
- index가 걸리는 join 조건절임을 나타내기 위해 b를 왼쪽으로 옮겨줘야 한다.
- 그럼 index를 어떻게 만들어야할 지 확실하게 보인다.
- PRA_HST_STC table에 (SALE_ORG_ID, STC_DT)로 index를 만들어줘야 한다.
- ODM_TRMS table에 (STRD_GRD_ID,STRD_ID)로 index를 만들어줘야 한다.
SELECT *
FROM PRA_HST_STC a, ODM_TRMS b
WHERE a.SALE_ORG_ID = :sale_org_id
AND b.STRD_GRP_ID = a.STRD_GRP_ID
AND b.STRD_GRP_ID = a.STRD_ID
ORDER BY a.STC_DT desc
sort merge 조인
- 해쉬 조인, NL 조인을 못 쓸 떄 쓰는 찌꺼기 조인이다.
- 각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA라고 한다.
- 만약 PGA 공간이 작다면 Temp 테이블스페이스(디스크)를 이용한다.
- PGA는 SGA와 다르게 공유되지 않는 독립적인 메모리 공간이라 래치가 불필요하다.
- 따라서 같은 양의 데이터를 읽어도 SGA 버퍼캐시에서 읽는 것보다 훨씬 빠르다.
- 머지 조인은 아래와 같은 순서로 이뤄진다.
- 소트
- 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
- 머지
- 정렬한 양쪽 집합을 서로 머지한다.
- 소트
- 예시를 살펴보자.
SELECT /*+ ordered use_merge(c) */
e.사원번호, e.사원명, e.입사일자
, c.고객번호, c.고객명, c.전화번호, c.최종주문금액
FROM 사원 e, 고객 c
WHERE c.관리사원번호 = e.사원번호
AND e.입사일자 >= '19960101'
AND e.부서코드 = 'Z123'
AND c.최종주문금액 >= 20000
- 위의 sql은 풀면 아래와 같다.
- 쿼리로 가져온 PGA 영역에 할당된 Sort Area에 저장한다.
- 상대 table과 join되는 column인 사원번호로 정렬된다.
SELECT 사원번호, 사원명, 입사일자
FROM 사원
WHERE 입사일자 >= '19960101'
AND 부서코드 = 'Z123'
ORDER BY 사원번호
- 상대방 table에서도 결과집합을 가져온다.
- 마찬가지로 Sort Area에 저장한다.
- 상대 table과 join되는 column인 관리사원번호로 정렬된다.
SELECT 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
FROM 고객 c
WHERE 최종주문금액 >= 20000
ORDER BY 관리사원번호
- PGA에 저장한 사원 데이터를 스캔하면서 PGA에 저장한 고객 데이터와 join한다.
- 사실 join자체는 NL join의 process와 다른 것이 없다.
begin
for outer in (select * from PGA_SORTED_사원)
loop
for inner in (SELECT * FROM PGA_SORTED_고객
WHERE 관리사원번호 = outer.사원번호)
loop
dbms_output.put_line(....);
end loop;
end loop;
end;
- 위와 같이 sort merge join은 사원 데이터를 기준으로 매번 고객 데이터를 full scan하진 않는다.
- 고객 데이터가 정렬돼있으므로 join record가 시작되는 지점을 쉽게 찾을 수 있다.
- 조인에 실패하는 record를 만나면 정렬돼있으므로 바로 멈추면 된다.
- sort area에 저장한 데이터 자체가 index이므로 join컬럼에 index가 없어도 사용 가능하다.
- sort merge가 index scan을 하지 않아도 빠른 이유는 PGA에 저장되어 래치 획득 과정이 없기 때문이다.
- 거기다가 건건이 전부 순회하지도 않는다. 데이터를 가져올 때 index scan을 하게 되면?
- random access에 따른 부하는 피할 수 없다.
- 현재는 자주 쓰이지 않는데, hash join이 성능이 더 좋기 때문이다. 아래 같은 상황에서만 한정적으로 쓰인다.
- 조인 조건식이 =가 아닌 대량 데이터 조인
- 조인 조건식이 없는 크로스 조인
해시 조인
- 해시 조인은 sort meger join과 달리 sort by에 따른 부하가 없다.
- 또한 sort merge join과 달리 disk I/O에 대한 염려도 거의 없다.
- 양쪽을 모두 PGA에 담지 않고 한쪽만 bulid input으로 PGA 메모리에 담기 때문이다.
- 해시 조인도 두 단계로 진행된다.
- build: 작은 쪽 table을 읽어(build input) 해시 테이블(해시맵)을 생성한다.
- 해시 테이블에는 join key와 sql에 사용된 컬럼이 모두 저장된다.
- 사원번호만 저장하면 table access를 다시 해야하기 때문에 래치 획득 과정이 필요해진다.
- 해쉬 조인의 장점을 날려먹는 것이기 때문에 join key만 저장되지 않는 것이다.
- 보통 테이블 조건절 컬럼에 대한 카디널리티가 작은 테이블을 선택한다.
- probe: 큰 쪽 테이블(probe input)을 읽어 해시 테이블을 탐색하면서 조인한다.
- build: 작은 쪽 table을 읽어(build input) 해시 테이블(해시맵)을 생성한다.
- 아래 sql 예시를 살펴보자.
SELECT /*+ ordered use_hash(c) */
e.사원번호, e.사원명, e.입사일자
c.고객번호, c.고객명, c.전화번호, c.최종주문금액
FROM 사원 e, 고객 c
WHERE c.관리사원번호 = e.사원번호
AND e.입사일자 >= '19960101'
AND e.부서코드 = 'Z123'
AND c.최종주문금액 >=20000
- 첫 단계인 build에;서 join컬럼인 사원번호를 해시 테이블 키값으로 사용한다.
SELECT 사원번호, 사원명, 입사일자
FROM 사원
WHERE 입사일자 >= '19960101'
AND 부서코드 ='Z123'
- 아래 조건에 해당하는 고객에티러를 읽어 앞서 생성한 해시 테이블을 탐색한다.
SELECT 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호 FROM 고객 WHERE 최종주문금액 >= 20000
- hash join이 빠른 이유는 sort merge join과 동일하다.
- 해시 테이블을 PGA 영역에 생성하기 떄문이다.
- 래치 획득 과정이 없다는 의미다.
- 거기다 disk I/O도 없으니 sort merge join보다 당연히 빠르다.
subquery
- 서브쿼리는 3가지가 있다.
- nested subquery
- where절
- inline view
- from 절
- scalar subquery
- select 절
- nested subquery
nested subquery -unnest
- 최근의 optimizer는 쿼리변환부터 진행한다.
- 문제는 서브쿼리를 모듈별로 나눠 각각 최적화가 진행된다는 점이다.
- 따라서 효율적이지 못한 sql로 변환될 수도 있다. 그래서 서브쿼리를 unnest하는 것이다.
- nested subquery가 unnest 방식으로 처리되어야 optimizer가 더 효율적인 최적화 경로를 찾을 가능성이 높아진다.
- unnest하는 순간 join으로 풀리는데, optimizer는 join 관련해서 최적화가 잘 이뤄졌기 때문이다.
- 그럼 unnest 방식일 때의 실행계획을 살펴보자.
SELECT c.고객번호, c.고객명
FROM 고객 c
WHERE c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
AND EXIST (
SELECT /*+ unnest nl_sj */ 'x'
FROM 거래
WHERE 고객번호 = c.고객번호
AND 거래일시 >= trunc(sysdate, 'mm')
)
Execution plan
------------------------------------------
0 SELECT STATEMENT
1 NESTED LOOPS (SEMI)
2 TABLE ACCESS (BY INDEX ROWID) OF '고객'
3 INDEX (RANGE SCAN) OF '고객_X01'
4 INDEX (RANGE SCAN) OF '거래_X01'
- EXIST를 쓰게 되면 SEMI 조인으로 작동하며 사실상 NL join과 다르지 않다.
- 그리고 unnest라도 캐싱 기능이 적용되기 때문에 filter operation과 동일하다.
- 다른 점은 바로 driving 집합이 서브쿼리가 될 수 있다는 사실이다.
SELECT /*+ leading(거래@subq) use_nl(c) */ c.고객번호, c.고객명
FROM 고객 c
WHERE c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
AND EXISTS (
SELECT /*+ qb_name(subq) unnest */ 'x'
FROM 거래
WHERE 고객번호 = c.고객번호
AND 거래일시 >= trunc(sysdate, 'mm')
)
0 SELECT STATEMNT
1 NESTED LOOPS
2 NESTED LOOPS
3 SORT
4 TABLE ACCESS
5 INDEX
6 INDEX
7 TABLE ACCESS
- 위의 실행계획에서 SORT UNIQUE가 뜬 이유는 서브쿼리를 unnest했기 때문이다.
- unnest하면서 서브쿼리쪽 집합에서도 메인쿼리 집합과 똑같은 고객번호가 생길 수 있다.
- 그래서 서브쿼리에 대해 sort unique를 수행하는 것이다.
- 참고로 unnest를 하려고 하는 경우, subquery 안에 rownum은 쓰면 안 된다.
- rownum을 쓰게 되면 서브쿼리가 unnest되지 않는다.
- unnest를 하게 되면 optimizer가 좀더 좋은 경로를 선택할 가능성이 높아지기 때문에 하는 것이다.
- 그런데 rownum을 써서 unnest를 막아버렸으니 hint를 줘도 소용이 없는 것이다.
SELECT 글번호, 제목, 작성자, 등록일시
FROM 게시판 b
WHERE 게시판구분 = '공지'
AND 등록일시 >= trunc(sysdatet -1)
AND EXISTS (
SELECT /*+ unnest nl_sj */ 'x'
FROM 수신대상자
WHERE 글번호 = b.글번호
AND 수신자 = :memb_no
AND ROWNUM <=1
)
nested subquery -filter
- unnest만 있는 건 아니고, filter방식도 존재한다.
- 필터 오퍼레이션은 기본적으로 NL조인과 처리 루틴이 같다.
- NL join과 Filter 자체가 다른 점은, Filter는 캐싱 기능이 있다는 점이다.
- 또한 필터 서브쿼리는 join순서가 고정이다. 늘 메인쿼리가 driving table이다.
- 필터로 작동하는 서브쿼리의 경우, 서브쿼리 필터링을 먼저 처리하면 성능이 좋아지는 경우가 많다.
- 이같이 서브쿼리의 필터링을 먼저 처리하는 기술을 subquery pushing이라고 한다.
- 먼저 서브쿼리 필터링을 맨 마지막에 처리하는 쿼리를 보자.
SELECT /*+ leading(p) use_nl(t) */ count(distinct p.상품번호), sum(t.주문금액)
FROM 상품 p, 주문 t
WHERE p.상품번호 = t.상품번호
AND p.등록일시 >= trunc(add_months(sysdate, -3), 'mm')
AND t.주문일시 >= trunc(sysdate - 7)
AND EXIST (
SELECT 'x'
FROM 상품분류
WHERE 상품분류코드 = p.상품분류코드
AND 상위분류코드 = 'AK'
)
- Filter Operation이기 때문에 main query가 먼저 실행된다.
- main query는 join문이고, driving table인 상품 table scan부터 시작한다.
- 우선 상품 table에 full scan으로 데이터를 가져온다. 그게 1000개다.
- table full scan이므로 where 조건문으로 filtering을 해도 별다른 실행계획 뜨지 않고 table access full 상품으로 나온다.
- 이제 driven table인 주문 table scan을 시작한다.
- join이 걸린 주문 table은 index scan을 진행한다.
- 위에서 얻은 1000개의 join record에 대해 index scan을 한 결과로 60,000 record가 뽑힌다.
- index range scan이 60,000개인데 table access by index rowid로 접근한 것도 60,000개니까 필터링은 없는 셈이다.
- 그 뒤에는 exists 절에 있는 내용을 실행한다.
- exists절은 index scan을 해서 가져오는데, 3개밖에 없어 보인다.
- table access 시에 1개로 줄어든 것을 보면, 상위분류코드는 적어도 index access조건은 아니다.
- index filter인지, table filter인지까지는 확인은 어렵다. index definition을 봐야 알 수 있다.
- exists를 통해 필터링을 마치게 되면 총 3000개의 record를 얻는다.
--------------------------------------------------------------
| ROW | Operation | Name
--------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | SORT AGGREGATE(cr=38103) |
| 3000 | FILTER(cr=38103) |
| 60000| NESTED LOOPS(cr=38097) |
| 1000 | TABLE ACCESS FULL(cr=95) | 상품
| 60000| TABLE ACCESS BY INDEX ROWID(cr=38002) | 주문
| 60000| INDEX RANGE SCAN(cr=2002) | 주문_PK
| 1 | TABLE ACCESS BY INDEX ROWID(cr=6) | 상품분류
| 3 | INDEX UNIQUE SCAN(cr=3) | 상품분류_PK
--------------------------------------------------------------
- 블록은 몇개나 읽었을까?
- 총 읽은 data block의 갯수는 38103개(join 38,097개 + exists 6개)다.
- join 과정에서 총 38,097개 data block을 읽었다.
- 처음 상품 table full scan을 하며 95개의 cr을 읽는다.
- 그 다음 join access를 한 뒤 주문 table을 index scan하며, index block을 2002개 읽는다.
- 이 과정을 index lookup이라고 하는데, index lookup은 loop 과정에서 읽은 cr 집계에서는 제외된다.
- index block에서 얻은 rowid를 통해 38002개의 data block을 읽는다.
- 그리고 마지막에 exists 절에서 index scan을 하면서 3개의 index leaf 블록을 읽는다.
- data block만 cr 집계에 포함되며, index leaf는 제외된다. 그러나 집계만 안 나온 거고 실제 index scan도 읽는 것에 포함된다.
- 읽은 leaf block에서 rowid를 가져와 6개의 data block을 읽었다.
- join 과정에서 총 38,097개 data block을 읽었다.
- 38103개 block이면 1개 block 당 500개의 table record로 잡아도 엄청나게 많은 양이다.
- 서브쿼리 필터링을 먼저 처리한다면 어떨까? 읽은 블록이 확 줄어든다.
- 그러한 기술을 Pushing subquery라고 한다.
- 이 기능은 unnest되지 않은 subquery에서만 작동한다.
- 따라서 push_subq hint는 no_unnest와 같이 사용해야 한다.
SELECT /*+ leading(p) use_nl(t) */ count(distinct p.상품번호), sum(t.주문금액)
FROM 상품 p, 주문 t
WHERE p.상품번호 = t.상품번호
AND p.등록일시 >= trunc(add_months(sysdate, -3), 'mm')
AND t.주문일시 >= trunc(sysdate - 7)
AND exists (SELECT /*+ NO_UNNEST PUSH_SUBQ*/ 'x'
FROM 상품분류
WHERE 상품분류코드 = p.상품분류코드
AND 상위분류코드 = 'AK'
)
- 서브쿼리 필터링을 먼저처리해서 driving table의 scan을 하고 filtering하는 조건으로 작동한다.
- 실제 아래 실행계획을 보면 FILTER가 사라지고 맨밑의 상품분류 Operation이 중간으로 갔다.
- 블록은 몇개나 읽었을까?
- 총 읽은 data block의 갯수는 1903개(join 1903개)다. exists는 join에 읽은 block 갯수에 포함된다.
- join 과정에서 총 1903개 data block을 읽었다.
- 처음 상품 table full scan을 하며 101개 cr을 읽는다.
- 그 다음 주문 table을 index scan하며 index block을 302개 읽는다. 이건 총 읽은 블록에서는 제외다.
- index block에서 얻은 rowid를 통해 1802개의 data block을 읽는다.
- join 과정에서 총 1903개 data block을 읽었다.
- 1903개 block이면 아까 38103개에 비하면 거의 1/14개로 줄어들은 것이다.
-------------------------------------------------------------- | ROW | Operation | Name -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | SORT AGGREGATE(cr=1903) | | 3000| NESTED LOOPS(cr=1903) | | 150 | TABLE ACCESS FULL 상품(cr=101) | 상품 | 1 | TABLE ACCESS BY INDEX ROWID 상품분류(cr=6) | 상품분류 | 3 | INDEX UNIQUE SCAN 상품분류_PK(cr=3) | 상품분류_PK | 3000| TABLE ACCESS BY INDEX ROWID 주문(cr=1802) | 주문 | 3000| INDEX RANGE SCAN 주문_PK(cr=302) | 주문_PK --------------------------------------------------------------
inline view -merge와 pushdown
- inline view에 쓸 때도 위와 같이 조건문이 아쉬울 때가 있다.
- 분명히 어차피 전월 이후 가입한 고객을 필터링할 것이다.
- 그런데도 inline view안에 해당 조건문이 없어 inline view는 그냥 scan을 진행한다.
SELECT c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거리
FROM 고객 c,
(SELECT 고객번호, avg(거래금액) 평균거래,
min(거래금액) 최소거래, max(거래금액) 최대거래
FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm') /**당월 발생한 거래 */
group by 고객번호) t
WHERE c.가입일시 >= trunc(add_months(sysdate, -1) ,'mm') /**전월 이후 가입 고객 */
AND t.고객번호 = c.고객번호
- 실행계획은 아래와 같다.
- no merge라서 inline view로 진행되고, WHERE 조건문은 맨마지막에 filter로 진행된다.
- 사실 그 전에 filter로 먼저 작동했다면 많은 record를 애초에 읽을 필요가 없었을 것이다.
- 그럴 떄 merge hint를 사용해준다.
SELECT c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
FROM 고객 c,
(SELECT /*+ merge */ 고객번호, avg(거래금액) 평균거래,
min(거래금액) 최소거래, max(거래금액) 최대거래
FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm') /**당월 발생한 거래 */
GROUP BY 고객번호) t
WHERE c.가입일시 >= trunc(add_months(sysdate , -1), 'mm') /**전월 이후 가입 고객 */
AND t.고객번호 = c.고객번호
- 그럼 실행계획이 아래와 같이 바뀐다.
- view가 사라진 걸 볼 수 있다.
- merge hint가 적용되어 WHERE 조건문의 필터링 조건이 inline view에 적용된다.
- 다만 위의 방식을 쓰면 group by를 마지막에 쓰기 때문에, 부분범위처리가 불가능하다.
- join에 성공한 resultSet을 가지고 group by를 진행해야 하기 때문이다.
- 또한 driving table의 resultSet(여기선 전월 이후 가입한 고객)이 클 때는?
- join record에 따른 상대 table access가 많아진다.
- 마찬가지로 driven table의 resultSet(여기선 당월 거래)가 클 때는?
- table access 이후 range scan양이 많아진다.
- 따라서 이러한 경우 hash join으로 table scan을 진행하는 게 좋다.
- 기본적으론 inline view보다는 일반 table을 통한 join 형태로 변환해주는 게 대개 더 좋다.
- 그러나 driving table이 커서 join record가 너무 많을 때는 subquery를 활용해 join record를 줄이는 것도 방법이다.
- 아래와 같이 scan을 한 뒤 grouping을 하는 sql이 있다고 해보자.
- grouping을 하여 table을 줄이고 scan을 하는 경우보다 join record가 많을 수밖에 없다.
SELECT C.co_cd
, MAX(C.district)
, SUM(emp_nbr) AS sum_emp
FROM Companies C
INNER JOIN Shops C
ON C.co_cd = S.co_cd
WHERE main_flg = 'Y'
GROUP BY C.co_cd;
- 이럴 떄 subquery를 이용해 아래와 같이 집약을 먼저 하고, scan을 하면 join record가 줄어들어 성능에 이득을 본다.
- 어차피 join을 하게 되면 scan을 최소 2번은 해야하기 때문에 scan을 더하게 되는 문제는 없다.
SELECT C.co_cd
, MAX(C.district)
, SUM(emp_nbr) AS sum_emp
FROM Companies C
INNER JOIN (
SELECT co_cd
, SUM(emp_nbr) AS sum_emp
FROM Shops
WHERE main_flg = 'Y'
GROUP BY co_cd CSUM
)
ON C.co_cd = CSUM.co_cd;
- 그 외 join record를 줄이는 방법으로는 join 조건 pushdown이 있다.
- merge랑 비슷하게 inline view로 조건절이 들어간다.
- pushdown을 이용하면 join record 건건이 group by를 수행하기에, 중간에 멈출 수 있다.
- 따라서 GROUP BY구가 있지만 부분범위처리가 가능하다.
- hint는 no_merge push_pred다.
SELECT c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
FROM 고객 c,
(SELECT /*+ no_merge push_pred */ 고객번호, avg(거래금액) 평균거래,
min(거래금액) 최소거래, max(거래금액) 최대거래
FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm') /**당월 발생한 거래 */
GROUP BY 고객번호) t
WHERE c.가입일시 >= trunc(add_months(sysdate , -1), 'mm') /**전월 이후 가입 고객. 이게 inline view로 들어간다. */
AND t.고객번호 = c.고객번호
- view pushed predicate라는 실행계획이 추가된걸 볼 수 있다.
- 그럼 잘 작동한 것이다.
scalar subquery
- 스칼라 서브쿼리를 쓰지 않고 함수를 사용하면 record마다 호출된다.
- 당연히 dept table을 record마다 scan한다.
- 더 큰 문제는 함수 호출 시 컨텍스트 스위칭이 일어난다는 점이다.
CREATE OR REPLACE function GET_DNAME(p_deptno number) return varchar2
is
l_dname dept.dname%TYPE;
begin
select dname into l_dname from dept where deptno = p_deptno;
return l_dname;
exception
when others then
return null;
end;
SELECT empno, ename, sal, hiredate,
GET_DNAME(e.deptno) as dname
FROM emp e
WHERE sal >= 2000
- 아래같이 스칼라 서브쿼리를 쓸 때도, record마다 dept table을 scan하는 건 같다.
- 하지만 적어도 context swithcing은 일어나지 않는다.
SELECT empno, ename, sal, hiredate,
(SELECT d.danme from dept d where d.deptno = e.deptno) as danme
FROM emp e
WHERE sal >= 2000
- 실제 스칼라 서브쿼리는 아래와 같이 NL join과 같이 이뤄진다고 보면 된다.
SELECT /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hidredate, d.dname
FROM emp e, dept d
WHERE d.deptno(+) = e.deptno /** +붙었으니 outer join */
and e.sal >= 2000
- 스칼라 서브쿼리는 강력한 캐싱 기능을 제공한다.
- 캐싱 효과를 사용하기 위해 함수를 스칼라 서브쿼리로 덮어버리기도 한다.
- 캐싱효과 덕분에 호출횟수를 최소화할 수 있기 때문이다.
SELECT empno, ename, sal, hiredate,
(SELECT GET_DNAME(e.deptno) FROM dual) dname
FROM emp e
WHERE sal >= 2000
- join에 쓰이는 데이터를 캐시에서 찾으면 join 성능이 매우 좋아진다.
- 첫 시도는 느리지만, 그 다음부터는 매우 빨라진다는 의미다.
- 하지만 PGA에 캐시를 할당하는 것이라, 캐시에 담을 join access에 쓰이는 record가 적어야 한다.
- 예를 들어, 거래구분코드가 20개라면 캐싱에 충분하고, 캐싱이 제대로 작동할 것이다.
- 만약 고객이 100만명인데, 고객ID로 join access를 쓴다면 100만개를 캐시에 저장해야 한다.
- 하지만 캐시는 작아서 그만큼 저장하지 못하기 때문에 캐시를 탐색해봐야 없을 것이다.
- 이러면 캐시 탐색 비용만 늘어나서 메모리/CPU 사용률만 높아진다.
SELECT 거래번호, 고객번호, 영업조직ID, 거래구분코드,
(SELECT 고객명 FROM 고객 WHERE 고객번호 = t.고객번호) 고객명
FROM 거래 t
WHERE 거래일자 >= to_char(add_months(sysdate, -3), 'yyyymmdd')
- 캐싱 효과가 부작용을 일으키는 경우가 하나 더 있다.
- 메인쿼리 집합이 작은 경우다. 스칼라 서브쿼리는 쿼리 단위로 캐싱이 이뤄진다.
- 메인쿼리 집합이 커야만 효과가 크다. 그래야 많이 재사용되기 때문이다.
- 재사용되지도 않는데 귀한 캐시 영역에 넣었다가 바로 버리면 비효율적인 것이다.
- 메인쿼리로 가져오는 record가 1개밖에 없다면 더더욱 그럴 것이다.
- 그런데 1개에 관해 사용자정의 함수를 호출해 scalar subquery로 덮으면 오히려 성능이 떨어진다.
SELECT 계좌번호, 계좌명, 고객번호, 개설일자, 계좌종류구분코드, 은행계설여부, 은행연계여부
,(SELECT brch_nm(관리지점코드) from dual) 관리지점명
,(SELECT brch_nm(개설지점코드) from dual) 개설지점명
FROM 계좌
WHERE 고객번호 =:고객번호
- 아래는 스칼라 서브쿼리를 사용할 때의 실행계획이다.
SELECT c.고객번호, c.고객명
,(SELECT ROUND(avg(거래금액), 2) 평균거래금액
FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm')
AND 고객번호 = c.고객번호)
FROM 고객 c
WHERE c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
- 실행계획에서는 후행 step이 먼저 발동하는 쿼리다.
- 따라서 고객이 먼저 full scan이 된다. 즉 메인쿼리가 먼저 발동한다.
- 그 다음 거래 table이 index scan된다. scalar subquery는 나중에 실행되는 것이다.
EXECUTION PLAN
----------------------------------------------
0 SELECT STATEMNT
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID BATCHED) OF '거래'
3 2 INDEX (RANGE SCAN) OF '거래_X02' (INDEX)
4 0 TABLE ACCESS (FULL) OF '고객' (TABLE)
5 4 INDEX (RANGE SCAN) OF '고객_X01' (INDEX)
- scalar subquery로 두 개 이상의 값을 얻는 건 불가능하다.
- 아래와 같은 sql은 불가능하다는 의미다.
SELECT c.고객번호, c.고객명
,(SELECT avg(거래금액), min(거래금액), max(거래금액)
FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm')
AND 고객번호 = c.고객번호)
FROM 고객 c
WHERE c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
- 그렇다고 아래처럼 바꾸면 거래 테이블에서 같은 테이블을 반복해 읽게 된다.
SELECT c.고객번호, c.고객명
,(SELECT AVG(거래금액) FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm') AND 거래번호 = c.고객번호)
,(SELECT MIN(거래금액) FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm') AND 거래번호 = c.고객번호)
,(SELECT max(거래금액) FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm') AND 고객번호 = c.고객번호)
FROM 고객 c
WHERE c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
- 따라서 사실 아래와 같은 방식이 많이 사용됐다.
- scalar subquery를 inline view 형식으로 바꾸는 것이다.
SELECT 고객번호, 고객명
, to_number(substr(거래금액, 1, 10)) 평균거래금액
, to_number(substr(거래금액, 11, 10)) 최소거래금액
, to_number(substr(거래금액, 21)) 최대거래금액
FROM (
SELECT c.고객번호, c.고객명
, (SELECT LPAD(AVG(거래금액), 10) || LPAD(MIN(거래금액), 10) || MAX(거래금액)
FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm')
AND 고객번호 = c.고객번호) 거래금액
FROM 고객 c
WHERE c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
)
- 11g부터는 pushdown이 도입돼 위와 같이 쓰지 않아도 된다.
- 아래와 같이 inline view를 쓰는 게 가능해져 더 직관적으로 바뀌었다.
SELECT c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
FROM 고객 c
, (SELECT /*+ no_merge push_pred */
고객번호, avg(거래금액) 평균거래
, min(거래금액) 최소거래, max(거래금액) 최대거래
FROM 거래
WHERE 거래일시 >= trunc(sysdate, 'mm')
GROUP BY 고객번호) t
WHERE c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
AND t.고객번호(+) = c.고객번호
-
위처럼 pushdown을 사용하면 실행계획은 아래와 같이 VIEW PUSHED PREDICATE가 뜨게 된다.
- scalar subquery는 병렬쿼리에선 엔간하면 쓰면 안된다.
- 대량 데이터를 처리하는 병렬쿼리는 해시 조인이 효과적이다.
- 또한 scalar subquery는 NL join이 이뤄지는 것이다.
- 따라서 캐싱 효과가 작으면 random access의 부담이 있다.
- 12c부터는 scalar subquery가 효과가 없다면 unnest 해서 merge할 수도 있다.
- /*+ unnest merge */를 통해 목적을 달성한다.
- 만약 unnest merge를 해서 문제가 생겼다면, no_unnest hint를 주면 된다.
- _optimizer_unnest_scalar_sq를 false로 놓는 것은 장기적으론 좋지 않은 선택이다.