오라클힌트, HASH_AJ, 안티조인이란? 중첩루프안티조인을 해시안티조인으로 튜닝사례실습, Nested Anti, Hash Anti, 오라클교육, 오라클학원,자바학원, JAVA학원
http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=327
ojc.asia
https://www.youtube.com/watch?v=BvFDn_R7A8c&list=PLxU-iZCqT52DFRbLFQIgGUFp-5En2DYRG&index=36
https://www.youtube.com/watch?v=eCAjkWShhe4&list=PLxU-iZCqT52DFRbLFQIgGUFp-5En2DYRG&index=35
https://www.youtube.com/watch?v=eQmUdTkIDm8&list=PLxU-iZCqT52DFRbLFQIgGUFp-5En2DYRG&index=34
오라클 HINT
ANTI JOIN 이란?
HASH_AJ
중첩루프 안티조인을 해시안티 조인으로 튜닝
Hints for Join Orders(HASH_AJ)
- ANTI 조인은 조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 연산으로 SQL연산에서 NOT IN, NOT EXISTS, MINUS 등이 있을 때 나타나는 실행계획 연산자 입니다.
- NOT EXISTS나 NOT IN 조건을 사용하는 경우 NESTED LOOP ANTI등으로 잘못 풀리면 성능이 저하되므로 HASH_AJ 힌트를 사용하여 HASH JOIN ANTI로 처리하도록 하는 오라클 힌트 구문 입니다.
- 안티 조인은 NESTED LOOP ANTI-JOIN, MERGE ANTI-JOIN 또는 HASH ANTI-JOIN으로 풀리도록 할 수 있는데 대체로 HASH ANTI-JOIN이 성능상 좋습니다.
- Oracle 10g까지는 컬럼 값이 NULL이 존재하지 않는 경우에 ANTI JOIN이 가능했는데 NOT IN 서브쿼리 사용시 메인, 서브쿼리의 조인 칼럼에 IS NOT NULL을 명시해야만 Anti Join으로 풀릴 수 있었습니다. 그 이후 버전은 안써줘도 되구요,
- 오라클 11g의 Optimizer는 널을 인지하면서 조인을 실행하는 Null Aware Anti Join(ANTI NA)이라는 새로운 Join Operation을 추가했다. 서브쿼리, 메인쿼리에 IS NOT NULL 조건을 주지 않아도 안티조인이 된다는 이야기 입니다.
- Anti Join NA(Null-Aware Anti-Join) : NULL을 인지(NULL을 체크) 하면서 ANTI JOIN 한다. 조인되는 양쪽 모두 칼럼이 NULL을 허용한다는 의미로 HASH JOIN NA는 NULL을 체크하는 서브쿼리를 별도로 추가하지는 않지만 Nested Loop Anti NA인 경우에는 NULL을 체크하는 서브쿼리가 추가됩니다.
- Anti Join SNA(Single Null-Aware Anti-Join) : 조인되는 컬럼 중 한쪽만 Null 허용하는 컬럼 이라는 뜻이다. 한쪽에만 IS NOT NULL이 있는 경우
[형식]
/*+ HASH_AJ( table) */
-- 먼저 MYEMP1, MYDEPT1 테이블에 생성되어 있는 인덱스 및 칼럼을 확인하자.
– MYEMP1의 JOB 컬럼에는 인덱스가 없습니다.
SQL> SELECT a.index_name, a.column_name, b.visibility
FROM user_ind_columns a, user_indexes b
WHERE a.table_name IN ('MYEMP1', ‘MYDEPT1’)
AND a.index_name = b.index_name ;
-- 부서명을 출력하는데, 직무(JOB)이 ‘ANALYST’인 사원들이 속하지 않은 부서명을 출력하려 합니다.
SQL> set timing on
SQL> set autotrace on
SQL> set line 130
SQL> SELECT dname
FROM mydept1
WHERE NOT EXISTS ( SELECT 1
FROM myemp1
WHERE myemp1.deptno = mydept1.deptno
AND JOB = 'ANALYST');
DNAME
-----------------------------------------
개발2팀
기획2팀
개발본부
교육본부
오라클자바커뮤니티
경 과: 00:00:02.62
Execution Plan
----------------------------------------------------------
Plan hash value: 4146422769
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 108 | 21 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 4 | 108 | 21 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | MYDEPT1 | 7 | 112 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| MYEMP1 | 1666K| 17M| 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_MYEMP1_DEPTNO | 6 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("JOB"='ANALYST')
4 - access("MYEMP1"."DEPTNO"="MYDEPT1"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
418208 consistent gets
0 physical reads
0 redo size
-- 이번에는 HASH_AJ 힌트를 사용하여 HASH JOIN ANTI로 동작하게 합니다.
SQL> SELECT dname
FROM mydept1
WHERE NOT EXISTS ( SELECT /*+ hash_aj */ 1
FROM myemp1
WHERE myemp1.deptno = mydept1.deptno
AND JOB = 'ANALYST');
DNAME
--------------------------------------------------
오라클자바커뮤니티
교육본부
개발2팀
개발본부
기획2팀
경 과: 00:00:01.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3799822877
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 108 | 56086 (2)| 00:00:03 |
|* 1 | HASH JOIN ANTI | | 4 | 108 | 56086 (2)| 00:00:03 |
| 2 | TABLE ACCESS FULL| MYDEPT1 | 7 | 112 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| MYEMP1 | 3333K| 34M| 56057 (2)| 00:00:03 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MYEMP1"."DEPTNO"="MYDEPT1"."DEPTNO")
3 - filter("JOB"='ANALYST')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
200310 consistent gets ← SELCET시 읽기 일관성 모드로 읽은 블록수가 반으로 줄었습니다.
0 physical reads
#오라클힌트, #HASH_AJ, #안티조인이란, #Anti조인, #중첩루프안티조인, #해시안티조인, #NestedAntiJoin, #HashAntiJoin, #오라클교육, #오라클학원, #ORACLE교육, #ORACLE학원, 오라클힌트, HASH_AJ, 안티조인이란, Anti조인, 중첩루프안티조인, 해시안티조인, NestedAntiJoin, HashAntiJoin, 오라클교육, 오라클학원, ORACLE교육, ORACLE학원,
