꿈꾸는 개발자, DBA 커뮤니티 구루비

FILTER 동작방식

Main SQL에서 추출된 데이터 건수만큼 서브쿼리가 반복적으로수행되면 처리되는 방식
Main SQL의 추출 결과에 대해서, 매 로우마다 서브쿼리에 조인 연결 값을 제공한 후 수행한 후, TRUE일 경우 데이터를 추출
Main SQL의 결과가 100만건이면 최대 100만번 수행
이런 이유로 적절한 인덱스가 없을 경우 Full Table Scan을 100만번 수행되어 SQL 성능 저하 발생
Main SQL의 추출 결과가 많더라도 서브쿼리의 Input 값이 동일하면 Filter가 1번만 수행되는 Filter Optimization이라 불리는 최적화 작업을 수행하므로 성능 문제가 발생하지 않는다.
{tip:title=Filter Optimization}
서브쿼리를 수행한 Input 값을 Cache하며, 동일한 Input값이 사용되는 경우 추가적입 읽기 작업 없이 Cache된 값을 사용
{tip}
서브 쿼리의 Input 값이 동일한 확률이 매우 희박하므로 추출 건수가 많은 경우 서브커리를 Filter 동작 방식으로 처리할 경우 성능상 비횰율적인 경우가 더 많음
Filter 동작 방식으로 수행될 경우 Input 값의 종류가 적어 성능에 유리한지를 반드시 확인해야 함.

Script. 서브쿼리 테스트용


DROP TABLE SUBQUERY_T1 PURGE;
DROP TABLE SUBQUERY_T2 PURGE;
DROP TABLE SUBQUERY_T3 PURGE;

<SUBQUERY_T1>
* 생성요건
- 테이블건수 1,500,000
- 컬럼 C4의 값의 종류: 250,000
- 컬럼 C5의 값의 종류 : 26
- 컬럼 C6SMS 100,000 부터 시작하여, 순차적으로 증가하며 값의 종류 : 250,000


* 테이블 생성
CREATE TABLE SUBQUERY_T1
AS
SELECT LEVEL AS C4, CHR(65+MOD(LEVEL, 26)) AS C5, LEVEL+99999 AS C6
FROM DUAL
CONNECT BY LEVEL <=250000;

- 250,000개의 데이터를 생성 한 후 동일한 테이블 값을 6번 반복하여 복사
 FOR I IN 1..6 LOOP
   INSERT INTO SUBQUERY_T1 SELECT * FROM SUBQUERY_T1;
   COMMIT;
 END LOOP;
END;
/

- 각 컬럼에 인덱스 생성 및 통계정보를 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'SUBQUERY_T1', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);

CREATE INDEX SUBQUERY_T1_IDX1_01 ON SUBQUERY_T1(C4, C5);
CREATE INDEX SUBQUERY_T1_IDX1_02 ON SUBQUERY_T1(C5);

<SUBQUERY_T2>
* 생성요건
- 테이블건수 500,000
- 컬럼 C1 값 종류 : 500,000
- 컬럼 C2 값 종류 : 26
- 컬럼 C3 값 종류 : 500,000(100,000부터 증가)
- 컬럼 C4 값 종류 : 26

* 테이블 생성
CREATE TABLE SUBQUERY_T2 
AS
SELECT LEVEL AS C1,
       CHR(65+MOD(LEVEL, 26)) AS C2,
       LEVEL+99999 AS C3,
       CHR(65+MOD(LEVEL,26)) AS C4
  FROM DUAL
CONNECT BY LEVEL <= 500000;

* 각 컬럼 인덱스 생성 및 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT', TABNAME=>'SUBQUERY_T2', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);

CREATE INDEX SUBQUERY_T2_IDX_01 ON SUBQUERY_T2(C2, C1);
ALTER TABLE SUBQUERY_T2 ADD CONSTRAINT PK_SUBQUERY_2 PRIMARY KEY(C1);

<SUBQUERY_T3>
*생성 요건
- 테이블 데이터 건수 : 500,000
- 컬럼 C1 값 : 500,000
- 컬럼 C2 값 : 26
- 컬럼 C3 값 : 500,000 (100,000부터 순차적으로 증가)

*테이블 생성
CREATE TABLE SUBQUERY_T3 
AS
SELECT LEVEL AS C1,
       CHR(65+MOD(LEVEL, 26)) AS C2,
       LEVEL+99999 AS C3
  FROM DUAL
CONNECT BY LEVEL <= 500000;

* 각 컬럼 인덱스 생성 및 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT', TABNAME=>'SUBQUERY_T3', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);

CREATE INDEX SUBQUERY_T3_IDX_01 ON SUBQUERY_T3(C1, C2);
ALTER TABLE SUBQUERY_T3 ADD CONSTRAINT PK_SUBQUERY_3 PRIMARY KEY(C1);

테스트1.Main SQL의 추출 건수가 많고, INPUT값이 UNIQUE한 경우


var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1
   AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
                 FROM SUBQUERY_T1 t1
                WHERE t1.c4 = t2.c1);

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch     2302    1.326        1.378          0     789240          0     230001
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     2304    1.326        1.378          0     789240          0     230001


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
 230001  FILTER  (cr=789240 pr=0 pw=0 time=1019504 us)
 380001   FILTER  (cr=3611 pr=0 pw=0 time=305638 us)
 380001    TABLE ACCESS FULL SUBQUERY_T2 (cr=3611 pr=0 pw=0 time=205235 us cost=373 size=4560036 card=380003)
 230001   INDEX RANGE SCAN SUBQUERY_T1_IDX1_01 (cr=785629 pr=0 pw=0 time=0 us cost=3 size=10 card=2)

FILTER 방식으로 동작
SUBQYER_T2를 Full Table Scan으로 읽으며 "C1 >= :B1 AND C1 <= :B2" 조건에 만족하는 데이터 추출 ==> 380,001
C1의 값을 SUBQUERY_T1_IDX_01 인덱스를 사용해 총 380,001번 반복조회하여 최종 건수 230,001건을 추출
Main Query의 추출건수(38만건)만큼 서브 쿼리가 38만번 반복적으로 수행
SQL전체 I/O 발생량의 대부분은 서브쿼리에서 반복적으로 사용하는 SUBQUERY_T1_IDX_01인덱스에서 발생

테스트2. Main SQL의 추출 건수가 적고, INPUT값이 UNIQUE한 경우


var b1 number
var b2 number

exec :b1 := 20000
exec :b2 := 20004

SELECT C1,
       C2,
       C3
  FROM SUBQUERY_T2 T2
 WHERE C1 >= :B1
   AND C1<=:B2
   AND EXISTS(SELECT /*+ NO_UNNEST */'X'
          FROM SUBQUERY_T1 T1
         WHERE T1.C4 = T2.C1)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0         21          0          5
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.000          0         21          0          5

Rows     Row Source Operation
-------  -----------------------------------------------------------------------
      5  FILTER  (cr=21 pr=0 pw=0 time=0 us)
      5   TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=21 pr=0 pw=0 time=0 us cost=13 size=756 card=63)
      5    INDEX RANGE SCAN PK_SUBQUERY_2 (cr=19 pr=0 pw=0 time=224 us cost=7 size=0 card=2250)
      5     INDEX RANGE SCAN SUBQUERY_T1_IDX1_01 (cr=15 pr=0 pw=0 time=0 us cost=3 size=10 card=2)

FILTER 방식으로 수행되지만, Main SQL의 조건이 효율적이어서 추출되는 데이터 건수가 총 5건 밖에 되지 않음
서브쿼리도 5번만 수행되어 비교적 양호

테스트3. Main SQL의 추출 건수는 많지만, INPUT값의 종류가 26개인 경우


var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000

SELECT C1,
       C2,
       C3
  FROM SUBQUERY_T2 T2
 WHERE C1 >= :B1
   AND C1<=:B2
   AND EXISTS(SELECT /*+ NO_UNNEST */'X'
          FROM SUBQUERY_T1 T1
         WHERE T1.C5 = T2.C2)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.005          0          0          0          0
Fetch     3802    0.484        0.974         60      19809          0     380001
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     3804    0.484        0.979         60      19809          0     380001

Misses in library cashe during parse   : 1
Misses in library cashe during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
 380001  FILTER  (cr=19809 pr=60 pw=0 time=457459 us)
 380001   FILTER  (cr=5107 pr=0 pw=0 time=292967 us)
 380001    TABLE ACCESS FULL SUBQUERY_T2 (cr=5107 pr=0 pw=0 time=185774 us cost=377 size=4560036 card=380003)
  14640   INDEX RANGE SCAN SUBQUERY_T1_IDX1_02 (cr=14702 pr=60 pw=0 time=0 us cost=3 size=4 card=2)

Main Query의 추출건수 : 380,001건/ INPUT 종류 : 26
위의 서브쿼리는 Main SQL의 추출 결과만큼 배번 수행하지 않았음.
서브쿼리의 INPUT값을 CACHE하여 INPUT값이 같을 경우 서브쿼리를 수행하지 않았음.

Filter 방식 수행

FILTER 방식은 Main SQL의 추출 결과가 많고 서브쿼리에 제공해 주는 값(INPUT)의 종류가 많다면 성능이 좋지 않음
Main SQL의 추출 거누가 적거나, Main SQL의 추출결과가 많을 경우 INPUT값의 종류가 적으면 성능 양호
FILTER 방식으로 수행되면 먼저 서브쿼리의 조인 연결 컬럼에 인덱스가 존재하는지 확인해야 함.
FULL TABLE SCAN으로처리된다면 심각한 성능 문제가 발생할 수 있음

조인 동작 방식

조안방식과 FILTER 동작방식과 비교시 큰 차이점은 가변성이다.
FILTER는 수행순서나 수행방법이 고정 (Main SQL -> SubQuery) 다양한 상황에 유연한 대처가 어려움
조인 동작 방식은 Nest Loops Join, Hash Join, Sort Merge Join, Semi Join, Anti Join등의 다양한 조인 방법 중 유리한 것을 선택 가능
SEMI/ANTI JOIN을 제외하고 수행 순서까지 선택 가능

기본적으로 SEMI/ANTI JOIN은 수행순서가 변경되지 않아 Main Query Block의 테이블이 먼저 수행.
Oracle 10부터 Hash Join의 경우에 한해 Driving Table의 순서 변경이 가능

Nested Loops Join Semi를 제외한 나머지 조인 방법은 Filter 동작 방식이 가지고 있는 FILTER 오퍼레이션 의 효과는 없음.
INPUT값의 종류가 적은 경우라면, FILTER 방식이 성능상 유리 할 수 있음.

테스트1.Filter 방식으로 수행되어 성능 문제가 발생하는 SQL


var b1 number
var b2 number
exec :b1 := 249990
exec :b2 := 250210

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1
   AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
                 FROM SUBQUERY_T1 t1
                WHERE T1.C6 = T2.C3
                  AND T1.C6 >= :b1)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2  311.628      932.413    7885118    9223016          0         11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4  311.628      932.413    7885118    9223016          0         11



Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     11  FILTER  (cr=9223016 pr=7885118 pw=0 time=0 us)
    221   FILTER  (cr=1317 pr=0 pw=0 time=1980 us)
    221    TABLE ACCESS FULL SUBQUERY_T2 (cr=1317 pr=0 pw=0 time=1100 us cost=372 size=2702664 card=225222)
     11   FILTER  (cr=9221699 pr=7885118 pw=0 time=0 us)
     11    TABLE ACCESS FULL SUBQUERY_T1 (cr=9221699 pr=7885118 pw=0 time=0 us cost=164 size=10 card=2)

테이블 T1컬럼 C6에 인덱스가 없어 Main SQL의 추출 건수만큼 SUBQUERY_T1 테이블을 반복적으로 Full Table Scan 수행
SUBQUERY_T1의 컬럼 C6에 인덱스를 생성하면 성능이 개선.
{note}
인덱스 생성시 SUBQUERY_T1 테이블을 액세스하는 다른 SQL의 실행계획에 영향을 미치는지 확인해야 함
트랜잭션 프로그램의 부하도 고려해야 함
인덱스 추가에 따른 디스크의 여유 및 앞으로의 증가량 체크
DB 서버의 성능 측면에서 인덱스를 생성하는 것이 이득이 크지 않다면 다른 개선 방법을 찾아봐야 함.
{note}


-- c6에 인덱스를 생성 한 후 결과

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1
   AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
                 FROM SUBQUERY_T1 t1
                WHERE T1.C6 = T2.C3
                  AND T1.C6 >= :b1)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.003          0          0          0          0
Fetch        2    0.000        0.065          8        249          0         11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.068          8        249          0         11

Misses in library cashe during parse   : 0
Misses in library cashe during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     11  FILTER  (cr=249 pr=8 pw=0 time=0 us)
    221   FILTER  (cr=7 pr=0 pw=0 time=4400 us)
    221    TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=7 pr=0 pw=0 time=220 us cost=4 size=2664 card=222)
    221     INDEX RANGE SCAN PK_SUBQUERY_2 (cr=4 pr=0 pw=0 time=2970 us cost=3 size=0 card=222)
     11   FILTER  (cr=242 pr=8 pw=0 time=0 us)
     11    INDEX RANGE SCAN SUBQUERY_T1_IDX_03 (cr=242 pr=8 pw=0 time=0 us cost=3 size=5 card=1)



SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1
   AND c1 <= :b2
   AND EXISTS (SELECT /*+ UNNEST HASH_SJ */ 'X'
                 FROM SUBQUERY_T1 t1
                WHERE T1.C6 = T2.C3
                  AND T1.C6 >= :b1)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    2.761        9.234      37509      43888          0         11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    2.761        9.235      37509      43888          0         11


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     11  FILTER  (cr=43888 pr=37509 pw=0 time=0 us)
     11   HASH JOIN SEMI (cr=43888 pr=37509 pw=0 time=0 us cost=10404 size=748 card=44)
    221    TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=5 pr=0 pw=0 time=880 us cost=4 size=1860 card=155)
    221     INDEX RANGE SCAN PK_SUBQUERY_2 (cr=3 pr=0 pw=0 time=440 us cost=3 size=0 card=222)
6400640    TABLE ACCESS FULL SUBQUERY_T1 (cr=43883 pr=37509 pw=0 time=5948287 us cost=10368 size=32003330 card=6400666)

반복적인 Full Table Scan을 줄이는게 성능 개선 포인트
/*+ UNNEST HASH_SJ*/ 힌트를 부여
HASH JOIN SEMI로 수행. 단 한번 Full Table Scan만으로 수행되도록 변경
인덱스가 존재하지 않는 것이 비효율의 근본적인 원인이지만, 인덱스를 생성하기 힘든 경우에 위와 같은 튜닝으로 개선 효과 발생

Filter 방식 Test


var b1 number
var b2 number
var b3 number
var b4 number

exec :b1 := 1
exec :b2 := 450210
exec :b3 := 100000
exec :b4 := 100004

SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS (SELECT /*+ NO_UNNEST */'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.009          0         31          0          0
Fetch        5   63.929       63.982      37509   33288865          0        320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        7   63.929       63.991      37509   33288896          0        320

Misses in library cashe during parse   : 1
Misses in library cashe during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    320  FILTER  (cr=33288865 pr=37509 pw=0 time=159 us)
16000000   FILTER  (cr=43886 pr=37509 pw=0 time=16265983 us)
16000000    TABLE ACCESS FULL SUBQUERY_T1 (cr=43886 pr=37509 pw=0 time=12611300 us cost=10424 size=192000000 card=16000000)
      5   FILTER  (cr=33244979 pr=0 pw=0 time=0 us)
      5    TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=33244979 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
15935488     INDEX UNIQUE SCAN PK_SUBQUERY_2 (cr=17309491 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

Main SQL의 추출 데이터 : 1,600만건.
SubQuery는 PK 인덱스를 UNIQUE 스캔 수행하여 I/O가 33만 블록
최종 데이터는 320건
320건을 추출하기 위해 처리한 I/O블록수와 수행 시간이 많이 소요 되었음.

Join 방식 Test


SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS (SELECT /*+ UNNEST HASH_SJ */'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        5    4.103        5.269      37509      45202          0        320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        7    4.103        5.269      37509      45202          0        320

Misses in library cashe during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    320  FILTER  (cr=45202 pr=37509 pw=0 time=319 us)
    320   HASH JOIN RIGHT SEMI (cr=45202 pr=37509 pw=0 time=319 us cost=10861 size=8448 card=384)
      5    TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=8 us cost=374 size=50 card=5)
16000000    TABLE ACCESS FULL SUBQUERY_T1 (cr=43886 pr=37509 pw=0 time=8017471 us cost=10408 size=192000000 card=16000000)

기존 SQL에서 서브쿼리가 1,600만번 반복 수행되던 부분을 제거하여 SQL 성능 개선
최종 결과가 320건추출 ==> 5초 수행

성능 개선 수행

성능 개선 여지는 SUBQUERY_T2의 추출 건수 =>5건으로 SUBQUERY_T1과 조인 후 최종 결과 320건 추출
SUBQUERY_T2를 먼저 처리하고, Main SQL의 SUBQUERY_T1 테이블과 Nested Loops Join(조인 연결 컬럼 인덱스 존재) 수행

개선 방법

SQL변경(O) : 서브쿼리를 조인으로 변경하여 효율적인 실행계획 유도
SQL변경(X) : 서브쿼리와 Main SQL에 조인 순서(LEADING), 조인 방법(NL_SJ),QUERY BLOCK(QB_NAME) 힌트 추가하여 효율적인 실행 계획 유도


SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_NL(T1@MAIN) */
       C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS (SELECT /*+ UNNEST QB_NAME(SUB) */'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        5    0.062        0.052          0       1654          0        320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        7    0.062        0.053          0       1654          0        320

Misses in library cashe during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    320  FILTER  (cr=1654 pr=0 pw=0 time=3509 us)
    320   NESTED LOOPS  (cr=1654 pr=0 pw=0 time=3349 us)
    320    NESTED LOOPS  (cr=1334 pr=0 pw=0 time=2871 us cost=477 size=7040 card=320)
      5     SORT UNIQUE (cr=1316 pr=0 pw=0 time=8 us cost=374 size=50 card=5)
      5      TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=4 us cost=374 size=50 card=5)
    320     INDEX RANGE SCAN SUBQUERY_T1_IDX1_01 (cr=18 pr=0 pw=0 time=567 us cost=2 size=0 card=64)
    320    TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=320 pr=0 pw=0 time=0 us cost=66 size=768 card=64)

서브쿼리를 조인으로 변경하기 위해서 추출 결과의 건수가 틀려지는 것을 방지하기 위해서 반드시 중복값을 제거하기 위해 SORT UNIQUE 오퍼레이션이 발생
서브쿼리의 추출 데이터가 5건에 불과해 추가적인 정렬 작업의 부하가 거의 없음.

서브쿼리 동작 방식을 제어하는 힌트들

HINT명	설명
NO_UNNEST	서브쿼리를 FILTER동작방식으로 처리하고 싶을 경우, 서브쿼리에 NO_UNNEST 힌트를 사용
UNNEST	FILTER 동작방식을 선택하지 않고 조인 동작방식으로 처리하고자 할 때. 서브쿼리에 UNNEST 힌트 사용
NL_SJ	EXISTS나 IN조건 사용시 서브쿼리에 UNNEST와 함께 NL_SJ힌트를 사용하면, NESTED LOOPS JOIN SEMI로 처리되도록 유도
HASH_SJ	EXISTS나 IN조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_SJ 힌트를 부여하면 HASH JOIN SEMI로 처리하도록 제어
NL_AJ	NOT EXISTS나 NOT IN 조건을 사용한 경우 서브쿼리에 UNNEST와 함께 NL_AJ 힌트를 사용하면, NESTED LOOPS JOIN ANTI로 처리하도록 제어
HASH_AJ	NOT EXISTS나 NOT IN 조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_AJ 힌트를 사용하면 HASH JOIN ANTI로 처리하도록 제어
ORDERED	FROM절에 나열된 순서대로 수행하도록 조인 순서를 정하는 힌트. 서브쿼리가 존재한다면 서브쿼리가 가장 먼저 수행
QB_NAME	QUERY BLOCK의 이름을 지정
SWAP_JOIN_INPUTS	HASH JOIN시 조인 순서를 변경 가능. 명시된 테이블이 BUILD 테이블이 됨
NO_SWAP_JOIN_INPUTS	HASH JOIN시 조인 순서가 바뀌는 경우, 이를 강제적으로 변경되지 못하도록 제어
PUSH_SUBQ	서브쿼리가 먼저 수행하도록 제어. FILTER로 수행됨

서브쿼리를 FILTER 동작 방식으로 수행하도록 제어


NO_UNNEST 힌트를 부여하면, Filter 동작 방식으로 수행하도록 제어할 수 있다.


SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ NO_UNNEST*/ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |    12 | 97718   (1)| 00:19:33 |
|*  1 |  FILTER                       |               |       |       |            |          |
|*  2 |   FILTER                      |               |       |       |            |          |
|*  3 |    TABLE ACCESS FULL          | SUBQUERY_T1   | 40000 |   468K| 10453   (3)| 00:02:06 |
|*  4 |   FILTER                      |               |       |       |            |          |
|*  5 |    TABLE ACCESS BY INDEX ROWID| SUBQUERY_T2   |     1 |    10 |     3   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | PK_SUBQUERY_2 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

NL SEMI JOIN으로 수행되도록 제어


NO_UNNEST 힌트를 부여하면, Filter 동작 방식으로 수행하도록 제어할 수 있다.


SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST NL_SJ*/ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |  1352 | 29744 | 90486   (1)| 00:18:06 |
|*  1 |  FILTER                       |               |       |       |            |          |
|   2 |   NESTED LOOPS SEMI           |               |  1352 | 29744 | 90486   (1)| 00:18:06 |
|*  3 |    TABLE ACCESS FULL          | SUBQUERY_T1   | 40000 |   468K| 10453   (3)| 00:02:06 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| SUBQUERY_T2   |    42 |   420 |     2   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_SUBQUERY_2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

HASH JOIN SEMI JOIN으로 수행되며, 서브쿼리를 Main SQL 테이블 보다 먼저 수행하도록 제어


UNNEST와 HASH_SJ, SWAP_JOIN_INPUTS힌트를 사용하면 서브쿼리부터 수행하도록 실행계획 제어 가능


SELECT
       C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST HASH_SJ SWAP_JOIN_INPUTS(T2)*/ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);
----------------------------------------------------------------------------------
d  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
 0 | SELECT STATEMENT      |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
 1 |  FILTER               |             |       |       |            |          |
 2 |   HASH JOIN RIGHT SEMI|             |  1352 | 29744 | 10828   (3)| 00:02:10 |
 3 |    TABLE ACCESS FULL  | SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
 4 |    TABLE ACCESS FULL  | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
----------------------------------------------------------------------------------

SQL 서브쿼리를 HASH SEMI JOIN으로 수행하되, Main SQL 테이블을 먼저 수행하도록 제어


UNNEST와 HASH_SJ 힌트를 사용하면, HASH SEMI JOIN으로 수행하도록 제어
SEMI JOIN은 MAIN SQL쪽 테이블을 먼저 수행하는 것이 기본이나 HASH RIGHT SEMI JOIN으로 수행되면 조인 순서가 변경되므로 
NO_SWAP_JOIN_INPUTS 힌트를 명시적으로 사용


SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST HASH_SJ NO_SWAP_JOIN_INPUTS(T2) */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  1 |  FILTER             |             |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  3 |    TABLE ACCESS FULL| SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
|*  4 |    TABLE ACCESS FULL| SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
-----------------------------------------------------------------------------------

SQL을 NL JOIN으로 수행하되, 서브쿼리를 수행하도록 제어


QB_NAME 힌트를 사용해 QUERY BLOCK명을 지정한 후, QUERY BLOCK 명을 지정한 후, 조인 순서와 조인 방법을 제어


SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_NAME(T1@MAIN) */
       C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST QB_NAME(SUB) */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1351 | 29722 | 10829   (3)| 00:02:10 |
|*  1 |  FILTER              |             |       |       |            |          |
|*  2 |   HASH JOIN          |             |  1351 | 29722 | 10829   (3)| 00:02:10 |
|   3 |    SORT UNIQUE       |             |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  4 |     TABLE ACCESS FULL| SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  5 |    TABLE ACCESS FULL | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
------------------------------------------------------------------------------------

HASH JOIN으로 처리하되, 허브쿼리로부터 수행하도록 제어


SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_HASH(T1@MAIN) */
       C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST QB_NAME(SUB) */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1351 | 29722 | 10829   (3)| 00:02:10 |
|*  1 |  FILTER              |             |       |       |            |          |
|*  2 |   HASH JOIN          |             |  1351 | 29722 | 10829   (3)| 00:02:10 |
|   3 |    SORT UNIQUE       |             |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  4 |     TABLE ACCESS FULL| SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  5 |    TABLE ACCESS FULL | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
------------------------------------------------------------------------------------

NOT EXISTS로 작성된 SQL을 NL JOIN ANTI로 수행하도록 제어


NOT EXISTS의 경우 NL JOIN ANTI로 수행 제어 하기 위해서 UNNEST, NL_AJ힌트를 부여


SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST NL_AJ */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  1 |  FILTER               |             |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  3 |    TABLE ACCESS FULL  | SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  4 |    TABLE ACCESS FULL  | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
-------------------------------------------------------------------------------------

NOT EXISTS로 작성된 SQL을 HASH JOIN ANTI 조인으로 수행하도록 제어


SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST HASH_AJ */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  1 |  FILTER               |             |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  3 |    TABLE ACCESS FULL  | SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  4 |    TABLE ACCESS FULL  | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
-------------------------------------------------------------------------------------

h9. 서브쿼리를 먼저 읽은 후, NL 으로 수행


SELECT 
       *
  FROM EMP E
 WHERE EMPNO IN (SELECT 
                        MAX(EMPNO)
                   FROM EMP X
                  GROUP BY DEPTNO);

-- 서브쿼리를 먼저 읽은 후, NL로 수행

SELECT /*+ LEADING(X@SUB) QB_NAME(MAIN) USE_NL(E@MAIN) */
       *
  FROM EMP E
 WHERE EMPNO IN (SELECT /*+ UNNEST QB_NAME(SUB) */
                        MAX(EMPNO)
                   FROM EMP X
                  GROUP BY DEPTNO);

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |  1400 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |          |    14 |  1400 |     8  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | EMP      |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   VIEW               | VW_NSO_1 |    14 |   182 |     4  (25)| 00:00:01 |
|   4 |    HASH GROUP BY     |          |    14 |   364 |     4  (25)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP      |    14 |   364 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

의도한대로 제어되지 않는 이유는 OPTIMIZER가 서브쿼리를 인라인 뷰로 변경하는 SQL 최적화 작업을 수행했기 때문이다.
VW_NSO_1이란 점에서 추축 가능
SQL이 변경되고, 이론 인해 QUERY BLOCK명도 변경되어 QB_NAME 힌트는 물론, 다른 힌트들도 무시
이런 경우는 FROM절에 나열된 순서대로 조인 순서를 결정하는 ORDERED 힌트를 사용하면 유도 할 수 있음
LOGICAL OPTIMIZER가 서브쿼리를 인라인 뷰로 변경할 때 FROM절의 맨 앞에 위치 시키기 대문에 ORDERED로 유도 가능


SELECT /*+ ORDERED USE_NL(E) */
       *
  FROM EMP E
 WHERE EMPNO IN (SELECT /*+ UNNEST */
                        MAX(EMPNO)
                   FROM EMP X
                  GROUP BY DEPTNO);

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    14 |  1400 |    18   (6)| 00:00:01 |
|   1 |  NESTED LOOPS                |          |       |       |            |          |
|   2 |   NESTED LOOPS               |          |    14 |  1400 |    18   (6)| 00:00:01 |
|   3 |    VIEW                      | VW_NSO_1 |    14 |   182 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY            |          |    14 |   364 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL       | EMP      |    14 |   364 |     3   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PK_EMP   |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    87 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

SUBQUERY가 여러개 일 경우 ORDERED 힌트로 제어 불가.
여러개의 SUBQUERY가 존재할 경우는 SQL을 재작성하여 SUBQUERY를 인라인 뷰로 면경 후, 힌트로 실행계획을 제어 해야 함

서브쿼리 동작방식 이해하기 (by ghlee97) [2013.09.05]

FILTER 동작방식

Script. 서브쿼리 테스트용

테스트1.Main SQL의 추출 건수가 많고, INPUT값이 UNIQUE한 경우

테스트2. Main SQL의 추출 건수가 적고, INPUT값이 UNIQUE한 경우

테스트3. Main SQL의 추출 건수는 많지만, INPUT값의 종류가 26개인 경우

Filter 방식 수행

조인 동작 방식

테스트1.Filter 방식으로 수행되어 성능 문제가 발생하는 SQL

Filter 방식 Test

Join 방식 Test

성능 개선 수행

서브쿼리 동작 방식을 제어하는 힌트들

서브쿼리를 FILTER 동작 방식으로 수행하도록 제어

NL SEMI JOIN으로 수행되도록 제어

HASH JOIN SEMI JOIN으로 수행되며, 서브쿼리를 Main SQL 테이블 보다 먼저 수행하도록 제어

SQL 서브쿼리를 HASH SEMI JOIN으로 수행하되, Main SQL 테이블을 먼저 수행하도록 제어

SQL을 NL JOIN으로 수행하되, 서브쿼리를 수행하도록 제어

HASH JOIN으로 처리하되, 허브쿼리로부터 수행하도록 제어

NOT EXISTS로 작성된 SQL을 NL JOIN ANTI로 수행하도록 제어

NOT EXISTS로 작성된 SQL을 HASH JOIN ANTI 조인으로 수행하도록 제어