SQLP - 쿼리변환(1)

@markdown

# 쿼리변환(1)

<br/>

## 쿼리변환이란

____

- 실행계획을 생성하고 비용을 계산하기에 앞서 사용자 SQL을 최적화에 유리한 형태로 재작성함


- Query Transformer가 역할 담당

### 휴리스틱(Heuristic) 쿼리 변환

- 규칙 기반(Rule-based) 최적화 기법

- 결과만 보장된다면 무조건 쿼리 변환을 수행

### 비용기반(Cost-based) 쿼리 변환

- 변환된 쿼리의 비용이 더 낮을 때만 그것을 사용하고, 그렇지 않을때는 원본 쿼리 그대로 두고 최적화를 수행

<br/>

## 서브쿼리 Unnesting

- 중첩된 서브쿼리를 풀어내는 것을 말함

- 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있음

- 아래는 하나의 쿼리에 서브쿼리가 이중삼중으로 중첩될 수 있음을 보여줌

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

WHERE EXISTS(

-- JE( Join Elimination( hint : ELIMINATE_JOIN, O_ELIMINATE_JOIN ) )

SELECT 'X' FROM DEPT WHERE DEPTNO = A.DEPTNO

) AND SAL  > (

SELECT AVG(SAL) FROM EMP B

WHERE EXISTS(

SELECT /*+   */ 'O'

        FROM SALGRADE S

        WHERE B.SAL BETWEEN LOSAL AND HISAL

        AND GRADE = 4

    ) 

);

</code></pre>

<br/>


- 중첩된 서브쿼리는 메인쿼리와 부모와 자식이라는 종속적이고 계층적인 관계가 존재

- 논리적인 관점에서 처리과정은 IN, Exists를 불문하고 필터 방식이어야 함

- 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하면서 조건에 맞지 않는 데이터를 골라내는 것

<br/>

### 옵티마이저의 서브쿼리 선택 방법

- 1. 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화함(서브쿼리 Unnesting)

- 메인과 서브쿼리 간의 계층구조를 풀어 서로같은 레벨로 만들어준다는 의미에서 '서브쿼리 Flatting' 이라 부름(다양한 실행계획)

- 2. 서브쿼리를 Unesting하지 않고 원래 상태로 최적화함(메인쿼리, 서브쿼리 각각 별도의 서브플랜으로 구분해 최적화 수행)

- 다양한 실행계획을 생성해 내는 작업이 매우 제한적(제한적 실행계획)

<br/>

## 서브쿼리 Unnesting 동작 원리

____

- SQL문을 Unnesting 하지 않고 그대로 최적화한다면 옵티마이저는 아래와 같이 필터 방식의 실행계획을 수립함

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

WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT)

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

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

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

|   0 | SELECT STATEMENT  |      |    12 |  1044 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |    12 |  1044 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

   1 - filter("DEPTNO" IS NOT NULL)

</code></pre>

- Unnesting 하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행한다.

- 위와 같은 서브쿼리가 Unnesting 되면, 변환된 쿼리는 아래와 같다.

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

(SELECT DEPTNO FROM DEPT) A, EMP B

WHERE B.DEPTNO = A.DEPTNO

↓ 서브쿼리가 Unnesting 되면, 변환된 쿼리는 아래와 같은 조인문으로 변경

SELECT * FROM

(SELECT DEPTNO FROM DEPT) A, EMP B

WHERE B.DEPTNO = A.DEPTNO

↓ 뷰 Merging 과정을 거쳐 최종적으로 아래와 같은 형태가 됨

SELECT EMP.* FROM DEPT, EMP

WHERE EMP.DEPTNO = DEPT.DEPTNO

</code></pre>

- 1. UNNEST : 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도

- 2. NO_UNNEST : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도

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

WHERE DEPTNO IN(SELECT /*+ NO_UNNEST */ DEPTNO FROM DEPT)

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

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

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

|   0 | SELECT STATEMENT   |         |    12 |  1044 |     4   (0)| 00:00:01 |

|*  1 |  FILTER            |         |       |       |            |          |

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

|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |    13 |     1   (0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT"

              WHERE "DEPTNO"=:B1))

   3 - access("DEPTNO"=:B1) -- 별도의 서브플랜으로 최적화

</code></pre>

<br/>

## 뷰 Merging

____

- 아래의 쿼리1 처럼 인라인 뷰를 사용하면 쿼리 내용을 파악하기 쉽지만, 최적화를 수행하는 옵티마이저의 시작에서는 불편함

- 그래서 쿼리2 처럼 쿼리 블록을 풀어내는 특징을 갖는다.(뷰 Merging)

- 뷰 Merging을 해야 옵티마이저가 더 다양한 액세스 경로를 조사 대상으로 삼는다.

<br/>

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

SELECT *

FROM 

(SELECT * FROM EMP WHERE JOB = 'SALESMAN') A,

(SELECT * FROM DEPT WHERE LOC = 'CHICAGO') B

WHERE A.DEPTNO = B.DEPTNO

<쿼리 2>

SELECT * FROM EMP A, DEPT B

WHERE A.DEPTNO = B.DEPTNO

AND A.JOB = 'SALESMAN'

AND B.LOC = 'CHICAGO'

</code></pre>

<br/>

### 뷰 Merging이 불가능한 경우

- 집합 연산자( union, union all, intersect, minus )

- connect by절

- rownum pseudo 칼럼

- select-list에 집계 함수( avg, count, max, min, sum) 사용

- 분석함수 

## 조건절 Pushing

____

- 옵티마이저가 뷰를 처리함에 있어 1차적으로 뷰 Merging을 고려하지만, 조건절 Pushing을 시도할 수도 있다.

- 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 기능을 말함

- 뷰 안에서의 처리 일량 최소화, 리턴 결과 건수를 줄임으로써 다음 단계에서 처리해야 할 일량을 줄일 수 있다.

## 조건절 Pushing 기술

- 조건절(Predicate) Pushdown : 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함

- 조건절(Predicate) Pullup : 쿼리 블록 안에 있는 조건절을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 PushDown 하는 데 사용함

- 조인 조건(Join Predicate) Pushdown : NL Join 수쟁 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 쪽(=right side) 뷰 쿼리 블록 안으로 밀어 넣는 것을 말함

### 조건절 Pushdown

- group by 절을 포함한 아래 뷰를 처리할 때, 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽에 밀어 넣을 수 있다면 group by 해야 할 데이터양을 줄일 수 있다.

- 효과적인 인덱스 선택이 가능

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

SELECT DEPTNO, AVG_SAL

FROM (

SELECT DEPTNO, AVG(SAL) AS AVG_SAL FROM EMP

GROUP BY DEPTNO 

) E

WHERE DEPTNO = 30;

</code></pre>

- 위 쿼리에서 쿼리 변환이 이루어지지 않는다면, EMP 테이블을 Full Scan 하고서 group by 이후에 deptno=30 조건을 필터링 했을 것이다.

- 조건절 Pushing이 작동함으로써 emp_deptno_idx 사용하여 일량을 줄일 수 있다.

<br/>

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

SELECT B.DEPTNO, B.DNAME, A.AVG_SAL

FROM (

SELECT DEPTNO, AVG(SAL) AS AVG_SAL

FROM EMP

GROUP BY DEPTNO

) A, DEPT B

WHERE A.DEPTNO = B.DEPTNO

AND B.DEPTNO = 30

</code></pre>

- deptno=30 조건절을 적용해 데이터양을 줄이고 group by와 조인연산을 수행하여 일량을 줄임

- depno=30이 인라인 뷰에 pushdown 될 수 있었던 이유는 조건절 이행 쿼리변환이 먼저 일어났기 때문이다.

- B.DEPTNO=30조건이 조인 조건을 타고 A쪽에 전이됨으로써 A.DEPTNO=30 조건절이 내부적으로 생성됐고, 이것이 인라인 뷰 안쪽으로 Pushing 된 것

### 조건절 Pullup

- 조건절을 쿼리 블록 바깥쪽으로 끄집어 내는 것

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

SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO 

) E1,

(

SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO 

) E2

WHERE E1.DEPTNO = E2.DEPTNO;

</code></pre>

- Index Scan을 한다면 아래와 같은 형태로 쿼리 변환이 일어난 것

- E2에 DEPTNO = 10 조건절 추가

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

SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO 

) E1, (

SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO

) E2     

WHERE E1.DEPTNO = E2.DEPTNO; 

</code></pre>

<br/>

### 조인 조건 Pushdown

- 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 것

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

> 사진 출처 : 구루비넷