SQL 튜닝 - JOIN(2)

6. SET OPERATOR

* SET 연산자 수행

- UNION의 경우 합치고 중복된 결과 중 중첩된 라인을 단일화하기 위해 UNIQUE 정렬을 실행한다.

- UNION ALL의 경우 단일화와 정렬을 수행하지 않아 성능이 좋다.

- 모든 SET 연산자의 실행 우선 순위는 동일하다.

- SET 연산자에 의해서 조회되는 문자형 결과의 자료형은 두 질의문에서 모두 문자형이 조회된다면 문자형으로 출력되고, 두 질의문 혹은 하나의 질의문에서 가변 문자형이 사용되면 가변 문자형으로 출력된다.

* SET 연산자 장단점

- 두 질의문의 결과를 대상으로 수학적인 개념의 집합 연산을 수행할 수 있게 한다.

- UNION ALL은 칼럼 개수와 자료형만 동일하면 N개의 질의문을 결합할 수 있는 수단이 된다.

- INTERSECT 연산이 IN/EXISTS 연산에 비해 빠른 속도를 가진다.

- SET 연산자가 사용된 질의문에 FIRST_ROWS 힌트가 사용되면 무시된다.

* SET 연산자 제약 사항

- SET 연산자를 사용하는 Select List에 연산식이 포함되어 있다면, Alias를 지정해야만 Order By 절에서 사용할 수 있다.

- SET 연산자를 사용하는 서브쿼리에서 ORDER BY절을 사용할 수 없다.

- FOR UPDATE 절 사용할 수 없다.

- SET 연산자는 BLOB, CLOB, BFILE, VARRAY 같은 자료형에 대해 사용할 수 없다.

- UNION, INTERSECT, MINUS 연산자는 LONG 칼럼에 사용할 수 없다.

 

7. SUBQUERY

* 정의: 하나의 질의문 내부에 하나 이상의 다른 질의문이 포함되어 그 결과를 이용하거나, 연관된 자료를 출력하기 위해 사용하는 질의문, 종류는 다음과 같다

- From 절: Inline View

- Where절: Nested/Correlated Subquery

- Scalar Subquery

Nested Subquery는 Where 절에서 사용된 경우를 말하며, 독립적으로 실행된 후 메인 쿼리는 그 결과를 이용하여 실행되는 형태를 취한다.

 

* Correlated Subquery

- where절에 사용되면서 메인 쿼리와 서로 연관되어 수행된다면 Correlated 서브 쿼리라고 한다. 문법적으로 메인 쿼리의 칼럼이 서브 쿼리에 사용되는 경우이며, Nested 서브쿼리와 다르게 본 질의문의 결과에 대해서 한 라인씩 연관되어 수행된다.

 

- Subquery와는 다르게 Outer Query에서 읽어온 행을 갖고 Inner쿼리를 실행하는 것을 반복하여 결과를 출력한다.

 

- Nested Subquery와 Correlated Subquery 예시 차이

1) Nested Subquery

SELECT EMPNO, ENAME
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
				FROM DEPT
                WHERE DNAME = 'SALES');

결과를 구하고, 메인쿼리에서 서브쿼리의 결과를 활용한다.

 

2) Correlated Subquery

SELECT EMPNO, ENAME
FROM EMP
WHERE EXISTS ( SELECT 1
				FROM DEPT
                WHERE DEPTNO = EMP.DEPTNO
                AND DNAME = 'SALES');

EMP.DEPTNO를 한 라인씩 연관되어 수행, JOIN을 수행한 결과와 같다.

 

8. SCALAR SUBQUERY

- 함수처럼 하나의 값만을 출력하는 특성

 

* Scalar Subquery의 특성

- 스칼라 서브쿼리는 Where 절에서 사용된 경우를 제외하고 실행계획에 표현되지 않으므로 SQL문 분석 시 주의할 필요가 있다.

- 스칼라 서브쿼리는 조인에 비해 실행 시 하드웨어 자원(메모리)이 더 많이 필요하다.

- 스칼러 서브쿼리의 결과가 공집합인 경우, 그룹 함수처럼 Null을 리턴한다.

 

* 사용할 수 없는 경우

- 칼럼에 대한 기본 값(Default)

- Check Constraints

- Function Based Index

- Start With와 Connect By 절

- DML 문장의 Return 절

- 클러스터에 대한 Hash 표현식

- 질의문과 상관 없는 Create Profile 같은 문장

 

9. TOP-N QUERY

인라인 뷰에 Order By 절을 사용하고 Top-Level 질의문의 Where 절에 Rownum(정보를 조회하거나 조인된 rOW가 반환되는 순서에 따라 부여받는 가상 칼럼) 조건을 사용

(예문)

SELECT *
FROM ( SELECT EMPNO
        FROM EMP
        ORDER BY EMPNO)
WHERE ROWNUM < 11;

(잘못된 예문)

SELECT ROWNUM, EMPNO, SAL
FROM EMP
WHERE ROWNUM <= 3
ORDER BY SAL DESC;

Order By 절이 가장 나중에 수행되기 때문에 원하는 결과가 반환되지 않는다.

 

cf) Rank 함수는 Select List 또는 Order By 절에서만 사용될 수 있다.

 

10. VIEW

- 뷰는 DB에서 물리적인 저장 공간을 차지하지 않고, 데이터도 보관하지 않는다. 질의문으로 정의되며 사용될 때 뷰가 참조하는 테이블에서 자료를 추출한다.

- Base Table = 뷰가 참조하는 테이블

- 딕셔너리(USER_VIEWS)에 텍스트 형태로 저장하기 위한 공간만 필요한 DB 객체

 

* VIEW의 사용 목적

1) 테이블 자료에 대한 접근 제한(보안)

2) 데이터의 구조 단순화

3) 사용자를 위한 단순한 인터페이스

4) 복잡한 질의문을 저장

5) 뷰를 사용하지 않고는 처리될 수 없는 질의문을 표현

6) 테이블 구조 변경에 의한 영향으로부터 프로그램 분리

 

* VIEW의 실행 원리

- 뷰의 정의를 변경하여 뷰의 Base 테이블을 액세스하는 동일한 문장으로 변환한다. 그 다음, 옵티마이저는 질의문을 사용한다. (View Merge)

옵티마이저는 질의문을 변경하기 위해 뷰의 정의를 참조하는 질의문에 병합하거나 병합하지 않는 방법을 사용한다.(View No-Merge)

11. INLINE VIEW

* From 절 자체에 서브쿼리를 사용 = 인라인 뷰

 

* Inline View의 사용

- 조인의 대체

- 조인 회수의 감소

- 절차성을 위해 사용

- Top-N 질의문에서 사용

 

* 뷰를 대신하는 임시(ad hoc) 질의에 사용

12. STANDARD SQL

* CROSS JOIN(카테시안 곱)

SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT;

* NATURAL JOIN(동일한 이름)

SELECT *
FROM EMP NATURAL JOIN DEPT;

* USING JOIN(원하는 칼럼에 대해서만 Equi Join)

SELECT E.EMPNO, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
USING (DEPTNO);

* ON JOIN(임의의 조인 조건 지정)

* OUTER JOIN

- LEFT OUTER JOIN(오른쪽 빈칸)

- RIGHT OUTER JOIN(왼쪽 빈칸)

- FULL OUTER JOIN