SQLP - 쿼리변환(2)

@markdown

# 쿼리변환(2)

<br/>

## 조건절 이행

____

- A 테이블에서 사용된 필터 조건이 조인 조건절을 타고 반대편 B 테이블에 대한 필터 조건으로 이행되는 경우

<pre><code class="sql" style="font-size:15px">SELECT *

FROM DEPT D, EMP E

WHERE E.JOB = 'MANAGER'

AND E.DEPTNO = 10

AND D.DEPTNO = E.DEPTNO;

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                 |      1 |        |      1 |00:00:00.01 |    7 |

|   1 |  NESTED LOOPS                 |                 |      1 |        |      1 |00:00:00.01 |    7 |

|   2 |   NESTED LOOPS                |                 |      1 |      1 |      3 |00:00:00.01 |    5 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT            |      1 |      1 |      1 |00:00:00.01 |    3 |

|*  4 |     INDEX RANGE SCAN          | DEPT_PK         |      1 |      1 |      1 |00:00:00.01 |    2 |

|*  5 |    INDEX RANGE SCAN           | FK_EMP          |      1 |      3 |      3 |00:00:00.01 |    2 |

|*  6 |   TABLE ACCESS BY INDEX ROWID | EMP             |      3 |      1 |      1 |00:00:00.01 |    2 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"=10)

   5 - access("E"."DEPTNO"=10)

   6 - filter("E"."JOB"='MANAGER')

</code></pre>

- 위 쿼리에서 deptno = 10은 emp 테이블에 대한 필터조건이다. 하지만 실행계획에 나타난 정보를 보면 dept 테이블에도 같은 조건이 추가된 것을 확인할 수 있다.

- `e.deptno = 10` → `e.deptno = d.deptno` → `d.deptno = 10`으로 추론이 되어 조건절 이행이 일어나 쿼리 변환이 일어났다.

- 이렇게 조건절 이행으로 인해 Hash Join, Merge Join 등을 수행하기 전에 emp, dept 테이블에 필터링을 적용함으로써 조인되는 데이터 양을 줄일 수 있는 효과가 있다. 

<br/>

## 불필요한 조인 제거

____

- 결과 집합에 영향을 미치지 않는 조인 조건을 제거함

- 1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면, 쿼리 수행시 불필요해 결과집합에 영향을 미치지 않는다.

- 이런 경우 옵티마이저는 M쪽 테이블만 읽도록 불필요한 조인을 제거하여 쿼리를 변환한다.

- 이 조인 제거 기능을 Oracle에서 사용하기 위해선 PK, FK 제약 설정해주어야 하고, Null 허용 칼럼을 사용하는 조인 조건을 삭제 시 문제가 될 수 있기 때문에 옵티마이저가 내부적으로 `e.deptno is not null` 조건을 추가해준다.

## OR 조건을 Union으로 변환(OR-Expansion)

____

<pre><code class="sql" style="font-size:15px">SELECT * FROM EMP

WHERE JOB = 'CLERK' OR DEPTNO = 20

</code></pre>

- 위의 쿼리가 그대로 실행되면 OR 조건이므로 Full Table Scan으로 처리된다.

- 이때 JOB과 DEPTNO에 각각 생성된 인덱스를 사용하고 싶다면 아래와 같이 UNION ALL 형태로 바꿔주면 됨

<pre><code class="sql" style="font-size:15px">SELECT * FROM EMP

WHERE JOB = 'CLERK'

UNION ALL

SELECT * FROM EMP

WHERE DEPTNO = 20

AND LNNVL(JOB='CLERK')

</code></pre>

- JOB과 DEPTNO 칼럼을 선두로 갖는 두 인덱스가 각각 사용되었고, UNION ALL 위쪽 브랜치는 JOB = 'CLERK'인 집합을 읽고 아래쪽 브랜치는 DEPTNO = 20인 집합만을 읽는다.

- 이때 EMP 테이블 액세스가 두번 일어났고, 중복 액세스가 일어나더라도 결과집합에는 중복이 없게 하려고 UNION ALL 아래쪽에 Oracle이 내부적으로 LNNVL 함수를 사용한 것을 확인할 수 있다.

- Oracle에서 OR-Expansion을 제어하기 위한 힌트로 `USE_CONCAT`, `NO_EXPAND`가 있다.

<br/>

## 기타 쿼리 변환

____

### 집합 연산을 조인으로 변환

- `Intersect`나 `Minus` 같은 집합 연산을 조인 형태로 변환하는 것을 말함

- 아래의 deptno=10에 속한 사원들의 JOB, MGR을 제외시키고 나머지 JOB, MGR 집합을 찾는 쿼리인데, Sort Unique 연산을 수행한 후에 MINUS 연산을 수행한 것을 확인할 수 있다.

<pre><code class="sql" style="font-size:15px">SELECT JOB, MGR FROM EMP

MINUS

SELECT JOB, MGR FROM EMP

WHERE DEPTNO = 10;

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |    12 |   204 |     8  (63)| 00:00:01 |

|   1 |  MINUS              |      |       |       |            |          |

|   2 |   SORT UNIQUE       |      |    12 |   144 |     4  (25)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| EMP  |    12 |   144 |     3   (0)| 00:00:01 |

|   4 |   SORT UNIQUE       |      |     4 |    60 |     4  (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL| EMP  |     4 |    60 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - filter("DEPTNO"=10)

</code></pre>

<br/>

- 아래의 쿼리는 옵티마이저가 MINUS 연산을 조인 형태로 변환했을 떄의 실행계획이다.

<pre><code class="sql" style="font-size:15px">SELECT   /*+ opt_param('_convert_set_to_join','true'  )  */  JOB, MGR FROM EMP

MINUS

SELECT JOB, MGR FROM EMP

WHERE DEPTNO = 10;

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     2 |    54 |     8  (25)| 00:00:01 |

|   1 |  HASH UNIQUE        |      |     2 |    54 |     8  (25)| 00:00:01 |

|*  2 |   HASH JOIN ANTI    |      |     2 |    54 |     7  (15)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| EMP  |    12 |   144 |     3   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL| EMP  |     4 |    60 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access(SYS_OP_MAP_NONNULL("JOB")=SYS_OP_MAP_NONNULL("JOB") AND

              SYS_OP_MAP_NONNULL("MGR")=SYS_OP_MAP_NONNULL("MGR"))

   4 - filter("DEPTNO"=10)

</code></pre>

- Anti 조인을 수행하고 나서 중복 값을 제거하기 위해 Hash Unique 연산을 수행하는 것을 확인할 수 있다.

- 아래와 같은 형태로 쿼리 변환이 일어난 것

<pre><code class="sql" style="font-size:15px">SELECT DISTINCT JOB, MGR

FROM EMP  E

WHERE NOT EXISTS(

SELECT 'X' FROM EMP

WHERE DEPTNO = 10

AND SYS_OP_MAP_NONNULL(JOB)=SYS_OP_MAP_NONNULL(E.JOB)

AND SYS_OP_MAP_NONNULL(MGR)=SYS_OP_MAP_NONNULL(E.MGR)

);

</code></pre>

<br/>

### 조인 컬럼에 IS NOT NULL 조건 추가

<pre><code class="sql" style="font-size:15px">SELECT COUNT(E.EMPNO), COUNT(D.DNAME)

FROM EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO

AND SAL <= 2900

</code></pre>

- 위의 쿼리에서 조인문을 처리할 때 조인 컬럼 deptno가 null인 데이터는 조인에 실패하기 때문에 조인 액세스가 불필요함

- 그래서 아래와 같은 필터 조건을 추가해 주면 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있어 쿼리 성능 향상에 도움이 된다.

<pre><code class="sql" style="font-size:15px">SELECT COUNT(E.EMPNO), COUNT(D.DNAME)

FROM EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO

AND SAL <= 2900

AND E.DEPTNO IS NOT NULL

AND D.DEPTNO IS NOT NULL

</code></pre>

- `IS NOT NULL` 조건을 사용자가 직접 기술하지 않아도 옵티마이저가 필요하다고 판단되면 내부적으로 추가해준다.

<br/>

### 필터 조건 추가

<pre><code class="sql" style="font-size:15px">

SELECT * FROM EMP

WHERE SAL BETWEEN :MIN AND :MAX

</code></pre>

- 위의 쿼리 처럼 바인드 변수로 BETWEEN 검색하는 쿼리가 있을 때 사용자의 실수로 MIN, MAX를 바꿔 넣는다면 공집합 결과가 출력될 것이다.

- Oracle 9i 부터 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가해준다. 

<pre><code class="sql" style="font-size:15px">Predicate Information (identified by operation id):

---------------------------------------------------

1 - fillter(TO_NUMBER(:MIN) <= TO_NUMBER(:MAX))

</code></pre>

- 위 필터 조건이 추가되면 블록 I/O가 전혀 발생하지 않는다.

<br/>

### 조건절 비교 순서

<pre><code class="sql" style="font-size:15px">-- A Col : ... 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, ...

-- B Col : ... 991, 992, 993, 994, 995, 996, 997, 998, 999, 1000, ...

SELECT * FROM T

WHERE A = 1 -- 비교연산 일량 비효율 발생

AND   B = 1000;

</code></pre>

- 위와 같이 테이블에 데이터가 있을 때 조건절 순서대로 조회 한다면 A, B를 수행한다면 A 컬럼이 대부분 1이여서 B = 1000까지 비교 연산을 수행하게 되므로 비효율적이다.

- 그래서 B 컬럼 조건식을 먼저 비교 연산을 수행하게 하는 것이 유리하다.

- 최신 옵티마이저는 비교 연산해야 할 일량을 고려해 선택도가 낮은 컬럼의 조건식부터 처리하도록 내부적으로 순서를 조정한다. 

> SQL 전문가 가이드 : 과목3. SQL 고급 활용 및 튜닝, 3장 옵티마이저 원리 - 제2절 쿼리변환(p.584 ~ p.592)