지식을 정리할 필요가 생겼다 *
ORACLE FILTER OPERATION
1. ORACLE FILTER OPERATION
FILTER OPERATION 은 NOT IN,NOT EXISTS 를 사용하는 경우 발견할 수 있는 OPERATION 이다. 사실 위의 부정형 연산을 사용하면 FILTER 말고도 ANTI JOIN 으로 풀릴 수 있지만 여기서는 따로 언급하지 않겠다.
NOT IN 을 사용하는 경우 FILTER OPERATION 이 발생할 수 있는데 이는 NL JOIN 과 유사하게 움직인다. 메인쿼리의 결과에 대해 서브쿼리의 결과값을 버퍼에 임시저장해 연결이 성공한 것을 버리고 실패한 것을 남긴다. 이 과정이 드라이빙되는 테이블의 각 로우에 대해 일어나기 때문에 NESTED LOOP JOIN 과 유사하다. 반복엑세스 되는 INNER TABLE 은 입력결과에 따른 조인결과를 버퍼에 저장을 해놓고 이용을 한다. 때문에 FILTER OPERATION 의 경우 INNER TABLE 의 DISTINCT KEYS 가 작은게 유리하다.
/*+ 추가 예제 작성할 것 */
INNER TABLE 이 거대해지면 DRIVING 결과건 수만큼 반복엑세스 되는 비효율이 발생한다.
때문에 SUB QUERY FLATTEN 을 위해 ANTI JOIN 으로 변경하거나/*+정확한 상황 상정 */
MERGE_AJ, HASH_AJ 와 같은 HINT 로 QUERY TRANSFORMATION 을 꾀하기도 한다.
2. FILTER OPERATION 의 변화
먼저 간단히 CTAS 를 이용해 hr.employees2 를 만들어 진행해보았다. employees2 의 구성컬럼은 first_name, last_name, salary 이다.
<CASE1>
select employee_id,first_name,last_name
from employees
where first_name not in
(select first_name from employees2
where salary > 10000)
/
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3180 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 106 | 3180 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2033 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES2 | 14 | 154 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("FIRST_NAME"="FIRST_NAME")
3 - filter("SALARY">10000)
위 경우 OPTIMIZER 가 알아서 HASH ANTI JOIN 으로 풀었다. 이는 HASH_AJ 힌트를 사용한 결과와 동일하다. HASH_AJ 의 동작은 HASH JOIN 을 실시하는데 조인에 성공한 결과를 버리고 나머지가 성공대상이 된다. 조인에 성공한 결과를 버린다는 것은 조인에 실패한 결과를 최종결과로 가져간다는 것을 의미한다.
그럼 여기서 JOIN 고리가 NULLABLE 하고 INNNER TABLE 에 그 값이 있다면 어떤 실행계획이 나올지 궁금하다.
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DAT JOB_ID
----------- ------------------------- ------------------------- -------- ----------
999 LAST LLL@AB.COM 10/03/04 ST_MAN
employees 에 위와같이 데이터를 넣고 앞서 수행한 쿼리를 다시 실행해 보았고 동일한 결과가 나온다. 이것이 의미하는 바는 driving table 에서 연결고리에 first_name is null 인 값이 들어갔을 때 이에대한 equi join 은 unknown 이 된다. 이 연결의 성패여부는 결과값에 있냐 없냐로 알 수 있는데 결과값에 없음을 확인했다. 다시말해 결과로 나오는 것은 조인연결에 실패한 것만 나오고 성공한 결과와 확인 불가능한 값들은 배제되었다.
<case2>
no_unnest 로 filter operation 사용
select employee_id,first_name,last_name
from employees
where first_name not in
(select /*+ no_unnest */first_name from employees2
where salary > 10000)
/
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 2014 | 164 (0)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2033 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES2 | 1 | 11 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMPLOYEES2"
"EMPLOYEES2" WHERE "SALARY">10000 AND LNNVL("FIRST_NAME"<>:B1)))
3 - filter("SALARY">10000 AND LNNVL("FIRST_NAME"<>:B1))
PREDICATE 1번을 보면 이와 같이 해석할 수 있다.
다음을 만족하는 결과를 제외 ( SALARY > 10000 이며 조건이 TRUE 인 것 )
조건은 LNNVL 부분을 의미하며 이 부분이 TRUE 가 되는 경우는 FALSE 이거나 UNKNOWN 인 경우이다. FALSE 는 값이 다른걸 의미하며 UNKNOWN 은 NULL 값이 들어왔을 때 이다.
다시 정리해 이야기를 하면 결과에서 제외되는 것은 NOT EXISTS 내부가 TRUE 가 되는
경우로 이는 TRUE and TRUE 가 이루어질 때 만들어진다.
SALARY > 10000 이면서 LNNVL 이 TRUE 인 경우이다. LNNVL 이 TRUE 가 나오는 것은 내부조건이 UNKNOWN, FALSE 가 나올 때로 FIRST_NAME 이 같거나 비교불가능한 경우이다.
위에서 이야기한 것을 반대로 이야기 하면 결과로 나오는 것은
WHERE 절이 거짓이 되는 경우로
SALARY <= 10000 (거짓) 이면서 FIRST_NAME 이 같거나 다른 경우
SALARY > 10000 이면서 FIRST_NAME 이 다르거나 비교불가능