ORACLE MINUS
from
1. MINUS OPERATION
MINUS OPERATION 은 SET OPERATION 중의 하나이다. 사용하기 위해서는 MINUS 를 기준으로 전쿼리와 후쿼리의 데이터 타입이 일치해야
한다.
2. MINUS EXECUTION PLAN
SQL> get minus
1 select empno,ename from emp
2 minus
3 select empno,ename from emp
4* where job = 'SALESMAN'
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 3686975449
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 240 | 8 (63)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 15 | 150 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 15 | 150 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 5 | 90 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 90 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("JOB"='SALESMAN')
minus operation 을 사용하면 위와 같이 각 집합(select 결과)을 SORT UNIQUE 하여 차집합을 구하게 된다. SORT UNIQUE 항목은
중복된 값을 제거(DISTINCT)하여 정렬한다. 정렬의 기준이 되는 값은 SELECT LIST 에 오는 값들이다.
중복된 값을 각기 제거를 하기 때문에 MINUS 를 수행하기 전에 대상이 되는 각 집합은 더 작아질 수 있다.
SQL> SELECT COUNT(*) FROM EMP;
COUNT(*)
----------
15
SQL> SET AUTOT ON
SQL> GET MINUS1
1 select job from emp
2 minus
3 select job from emp
4* where job = 'SALESMAN'
SQL> /
JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
Execution Plan
----------------------------------------------------------
Plan hash value: 3798726549
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 160 | 8 (63)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 15 | 120 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 15 | 120 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE NOSORT| | 5 | 40 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 40 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("JOB"='SALESMAN')
SORT UNIQUE OPERATION 은 이미 엑세스 한 대상에 대해 다시 엑세스 하거나 정렬대상에 UNIQUE INDEX KEY 가
있는 경우 생략될 수 있다. 이미 엑세스 한 대상(정렬완료된…) 의 예는 위의 id = 4 를 보면 SORT UNIQUE NOSORT
오퍼레이션을 확인할 수 있다. UNIQUE INDEX KEY 를 통해 엑세스 한 경우 아래와 같이 실행플랜이 풀릴 수 있다.
SQL> get minus1
1 select /*+ index(e,PK_EMP) */ e.empno from emp e
2 minus
3 select /*+ index(e,PK_EMP) */ e.empno from emp e
4* where job = 'SALESMAN'
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 4241070328
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 120 | 6 (84)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE NOSORT | | 15 | 60 | 2 (50)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 15 | 60 | 1 (0)| 00:00:01 |
| 4 | SORT UNIQUE NOSORT | | 5 | 60 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 60 | 3 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | PK_EMP | 15 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
3. NOT EXISTS, OUTER JOIN
SORT OPERATION 은 비싼 비용의 작업이다. SORT 를 하기 위해서는 별도의 공간(메모리 : PGA)이 필요하며 이 공간이 부족한
경우 디스크를 사용하는 (MULTI PASS) 가 발생할 위험이 있다.
이를 회피하기 위한 방법으로 MINUS OPERATION 을 NOT EXISTS 로 변경하는 방법과 OUTER JOIN 으로 변경하는 방법이 있다.
<<NOT EXISTS>>
SQL> get minus3
1 select empno,ename from emp e
2 where not exists (select 1 from emp
3 where job = 'SALESMAN'
4 and empno = e.empno
5* )
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 3143289562
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 220 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 10 | 220 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 15 | 150 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 24 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
기존에 minus 부분부터 후 쿼리에 해당하는 부분을 not exitst 를 사용한 화면이다. exists 의 사용에 대해서는 여기서는 따로 언급하지
않겠다. sub query 안에 보면 join 할 때 사용하는 구문과 동일한 부분이 있다.(4행) 실행플랜을 가지고 이야기를 하면 아래와 같다.
1. emp 테이블의 조건을 만족하는 첫 row 를 읽어들여 empno(조인조건) 을 상수화한다.
2. emp 의 PK_EMP 를 읽고 조인을 만족하는지 확인한다.
3. 조인이 성공하면 EMP TABLE 을 읽어 job = ‘SALESMAN’ 인지 확인한다.
4. 위 조건을 만족하면 조인이 성공했다는 걸 의미한다. 이 경우는 NOT EXISTS 를 사용하였으므로 이 데이터는 폐기된다.
5. 1~4 를 반복수행(NESTED LOOP JOIN) 하며 조인을 실패한 데이터를 운반단위에 적재한다.
** NOT EXISTS 안의 SELECT-LIST 부분은 별의미 없다. NOT EXISTS 는 JOIN 이 실패했는지 성공했는지만을 확인한다. (BOOLEAN)**
결과적으로 minus 를 사용한 경우 각 집합에 대해 sort를 하고 minus 연산을 통해 데이터가 버려지는데 반해 not exists 를 사용하면
각 집합을 NL JOIN 을 통해 성공한 집합을 버리게 된다.
위의 sub query 를 no_unnest 로 풀게 되면 다음과 같이 풀린다.
SQL> get minus3
1 select empno,ename from emp e
2 where not exists (select /*+ no_unnest */ 1 from emp
3 where job = 'SALESMAN'
4 and empno = e.empno
5* )
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 1197114631
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 11 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 15 | 150 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 12 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
"EMPNO"=:B1 AND "JOB"='SALESMAN'))
3 - filter("JOB"='SALESMAN')
4 - access("EMPNO"=:B1)
no_unnest 는 말 그대로 sub query 를 unnest 하지 않는다. unnest 를 한다는 것은 괄호 ( ) 를 제거한다는 것이고
괄호가 제거된 서브쿼리는 주쿼리와 조인으로 풀겠다는 의미이다. optimizer 는 기본적으로 unnest 하려고 한다.
/*+ no_unnest */ 를 사용하기 전 즉 앞의 쿼리에서 nested loops anti 는 조인으로 푸는 경우 나타나는 오퍼레이션이다.
/*+ FILTER 로 풀리는 경우의 OPERATION 단계를 설명 */
<<OUTER JOIN>>
/*+ OUTER 로 풀수 있는 방법 확인, 추가 */