새로쓴 대용량 데이터베이스솔루션 1 (2011년)
부정형(Anti) 세미조인
- '111'이 아닌 것을 찾겟다고 한다면 우리가 찾아야 할 대상이 어떤 값이 존재하는지 알 수 없으므로 연결을 위한 상수값을 제공하기가 여의치 않다.
- 그러나 약간만 생각을 바꾸어 보면 길이 없는 것도 아니다. 학창시절 수학시간에서 배웠던 상식적인 개념을 생각해 보자.
- 가령, '10-X'라는 수식이 있을때 'X'에 있는 마이너스를 플러스(+)로 바꾸는 방법이 있다. ( '10-(+X)' )
- 즉, 조인의 비교연산자는 'NOT'을 사용하지 않는 긍정형을 쓰고, 이것을 괄호로 묶는 것( 인라인뷰 )을 'NOT IN' 이나 'NOT EXISTS'로 비교하는 방법을 사용하면 된다.
- 다시 말해서 집합간의 연결은 기존의 세미조인으로 실시하고, 그 결과는 판정만 반대로 하는 방식을 적용하기 때문에 앞서 설명했던 세미조인과 매우 유사하다고 할 수 있다.
- 여기서 'IN' 이나 'EXISTS'와 같은 비교 연산자의 의미는 어떤 것을 찾고자 하는 것이 아니라 단지 기술한 서브쿼리의 결과가 'TRUE'인지 'FAULT'인지를 판정하는 불린 연산을 뜻한다.
- 항상 확인자의 역할을 담당 할 수 밖에 없다.
준비 스크립트 ( 위에 테이블 재사용 ( 편의상 ) )
DROP TABLE TAB1 PURGE;
DROP TABLE TAB2 PURGE;
CREATE TABLE TAB1 AS
SELECT LEVEL KEY1
, '상품'||LEVEL COL1
, TRUNC( dbms_random.value( 1,7 ) ) * 500 + TRUNC( dbms_random.value( 1,3 ) ) * 750 COL2
FROM DUAL
CONNECT BY LEVEL <= 10000;
CREATE TABLE TAB2 AS
SELECT LEVEL KEY1
, TRUNC(dbms_random.value( 1, 10000 ) ) KEY2
, TRUNC( SYSDATE ) - 100 + CEIL( LEVEL/ 1000 ) COL1
, 'A' COL2
FROM DUAL
CONNECT BY LEVEL <= 100000;
CREATE INDEX TAB1_COL1 ON TAB1( COL1 );
CREATE INDEX TAB2_COL1 ON TAB2( COL1 );
CREATE INDEX TAB2_KEY2 ON TAB2( KEY2, COL1 );
EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'TAB1' );
EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'TAB2' );
p. 600 실습 스크립트 ( 확인자 )
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND KEY1 NOT IN ( SELECT KEY2
5 FROM TAB2 B
6 WHERE COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE ) );
COUNT(*)
----------
899
SQL> @XPLAN
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.12 | 15487 |
|* 2 | FILTER | | 1 | | 899 |00:00:00.99 | 15487 |
| 3 | TABLE ACCESS BY INDEX ROWID | TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 |
|* 4 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 |
|* 5 | FILTER | | 1112 | | 213 |00:00:01.11 | 15278 |
|* 6 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1112 | 1 | 213 |00:00:01.10 | 15278 |
|* 7 | INDEX RANGE SCAN | TAB2_COL1 | 1112 | 2000 | 2001K|00:00:04.01 | 8184 | ---- (a)
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NULL)
4 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
5 - filter(TRUNC(SYSDATE@!-1)<=TRUNC(SYSDATE@!))
6 - filter(LNNVL("KEY2"<>:B1))
7 - access("COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!))
- 서브쿼리가 확인자로 풀림
- (a)서브쿼리의 처리주관 인덱스는 연결고리인 KTAB2_KEY2 인덱스가 아니라 TAB2_COL1 사용 되고 있다.
- 이거슨 서브쿼리가 계속해서 동일한 범위를 반복해서 치리하게 된다는것을 의미한다. ( 부정형에는 캐쉬가 먹지 않는가.?? 위경우는 TAB1.KEY1경우가 유니크하기때문에... )
- 만약 인덱스가 ( KEY2, COL1 )로 구성되었거나 COL1에 인덱스가 없다면 당연히 KEY2 인덱스를 사용하는 실행계획이 작성된다.
- 이것은 옵티마이저의 잘못이다. ( 데이터베이스 버전에따라 나타나지 않을 수도 있다. )
잘못된 실행 계획이 수립되는 것을 방지하는 바람직 SQL ( TAB2_COL1 풀림 )
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND NOT EXISTS ( SELECT/*+ */ 'X'
5 FROM TAB2 B
6 WHERE A.KEY1 = B.KEY2
7 AND COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE ) );
COUNT(*)
----------
899
SQL> @XPLAN
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 224 | | | |
|* 2 | HASH JOIN ANTI | | 1 | 1 | 899 |00:00:00.01 | 224 | 1517K| 1517K| 1182K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 | | | |
|* 4 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 2000 | 2000 |00:00:00.02 | 15 | | | |
|* 6 | INDEX RANGE SCAN | TAB2_COL1 | 1 | 2000 | 2000 |00:00:00.01 | 8 | | | | <-- ㅡㅡ^
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."KEY1"="B"."KEY2")
4 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
6 - access("COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!))
- 책에서는 필터형으로 설명이되었는데 옵티마이져는 해쉬 조인 안티를 선택하였다.
- 옵티마이져가 해쉬를 선택한 이유는 ? 서브쿼리를 한번만 액세스 하기 때문이다.( 주관 )
잘못된 실행 계획이 수립되는 것을 방지하는 바람직 SQL ( TAB2_KEY2 유도 )
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND NOT EXISTS ( SELECT/*+ INDEX( B TAB2_KEY2 ) */ 'X'
5 FROM TAB2 B
6 WHERE A.KEY1 = B.KEY2
7 AND COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE ) );
COUNT(*)
----------
899
SQL> @XPLAN
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 | 531 | | | |
|* 2 | HASH JOIN ANTI | | 1 | 1 | 899 |00:00:00.08 | 531 | 1517K| 1517K| 1182K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 | | | |
|* 4 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 | | | |
|* 5 | INDEX FULL SCAN | TAB2_KEY2 | 1 | 2000 | 2000 |00:00:00.08 | 322 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."KEY1"="B"."KEY2")
4 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
5 - access("COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!))
filter(("COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!)))
왜 TAB2_COL1 인덱스를 선택하였나??
SQL> SELECT/*+ INDEX( B TAB2_KEY2 ) */ COUNT(*)
2 FROM TAB2 B
3 WHERE COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE );
COUNT(*)
----------
2000
SQL> SELECT COUNT(*)
2 FROM TAB2
3 WHERE KEY2 IN (
4 SELECT/*+ INDEX( B TAB2_KEY2 ) */ DISTINCT KEY2
5 FROM TAB2 B
6 WHERE COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE ) );
COUNT(*)
----------
19431
그럼 이제 책에서 말하는 필터형으로 풀어보자.!
p. 601 실행 스크립트
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND NOT EXISTS ( SELECT/*+ NO_UNNEST NO_PUSH_SUBQ */ 'X'
5 FROM TAB2 B
6 WHERE A.KEY1 = B.KEY2
7 AND COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE ) );
COUNT(*)
----------
899
SQL> @XPLAN
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 2434 |
|* 2 | FILTER | | 1 | | 899 |00:00:00.03 | 2434 |
| 3 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 |
|* 4 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 |
|* 5 | FILTER | | 1112 | | 213 |00:00:00.02 | 2225 |
|* 6 | INDEX RANGE SCAN | TAB2_KEY2 | 1112 | 1 | 213 |00:00:00.01 | 2225 | <-- 굿잡
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NULL)
4 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
5 - filter(TRUNC(SYSDATE@!-1)<=TRUNC(SYSDATE@!))
6 - access("B"."KEY2"=:B1 AND "COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!))
- 필터형으로 푸니 책에서 말씀하신데로 잘 풀리는구나... ( 버전에 따라 틀리수도 있다 )
- 선행 집한에서 상수값을 제공받아 처리한다.( 확인자 )
- 이런한 처리는 랜덤 액세스가 증가한다는 단점을 가지고 있기 때문에 상수값을 제공받았을 때 수행되는 처리량과 독자적으로 수행할 때의 처리량을 비교하여 판단해야 한다.
- 이 실행계획읜 장점은 부분범위 처리가 가능하다는 것이다. ( 운반단위 )
보너스.. NL_AJ로 풀면 어떤가..??
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND NOT EXISTS ( SELECT/*+ NL_AJ */ 'X'
5 FROM TAB2 B
6 WHERE A.KEY1 = B.KEY2
7 AND COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE ) );
COUNT(*)
----------
899
SQL> @XPLAN
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 1324 |
| 2 | NESTED LOOPS ANTI | | 1 | 1 | 899 |00:00:00.02 | 1324 |
| 3 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 |
|* 4 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 |
|* 5 | INDEX RANGE SCAN | TAB2_KEY2 | 1112 | 2000 | 213 |00:00:00.01 | 1115 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
5 - access("A"."KEY1"="B"."KEY2" AND "COL1">=TRUNC(SYSDATE@!-1) AND
"COL1"<=TRUNC(SYSDATE@!))
부정형이 일반 조인이랑 같은 방식이라면 캐쉬도 되는건가??
SQL> SELECT /*+ gather_plan_statistics INDEX( X ORDDATE_INDEX ) */ COUNT(*)
2 FROM "ORDER" X
3 WHERE ORDDATE LIKE '20110621%'
4 AND NOT EXISTS ( SELECT /*+ NL_AJ */ 'O'
5 FROM DEPT Y
6 WHERE Y.DEPTNO = X.SALDEPTNO
7 AND Y.TYPE1 = 1 );
COUNT(*)
----------
70568
SQL> @XPLAN
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.14 | 688 |
| 2 | NESTED LOOPS ANTI | | 1 | 3190 | 70568 |00:00:00.35 | 688 |
| 3 | TABLE ACCESS BY INDEX ROWID| ORDER | 1 | 3899 | 86400 |00:00:00.78 | 675 |
|* 4 | INDEX RANGE SCAN | ORDDATE_INDEX | 1 | 3899 | 86400 |00:00:00.26 | 316 |
|* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 11 | 1 | 2 |00:00:00.01 | 13 |
|* 6 | INDEX UNIQUE SCAN | DEPT_PK | 11 | 1 | 11 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ORDDATE" LIKE '20110621%')
filter("ORDDATE" LIKE '20110621%')
5 - filter("Y"."TYPE1"=1)
6 - access("Y"."DEPTNO"="X"."SALDEPTNO")
연결 고리가 존재하지 않을 때는 어떻게 해야하는가.?
- ( 개인적으로 연결고리가 존재하더라도 부정형은 Hash가 월등한것으로 판단됨 ( 주관 ) )
p. 602 FILTER
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND KEY1 NOT IN ( SELECT KEY1
5 FROM TAB2 B
6 WHERE COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE ) );
COUNT(*)
----------
1112
SQL> @XPLAN
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.14 | 16889 |
|* 2 | FILTER | | 1 | | 1112 |00:00:01.15 | 16889 |
| 3 | TABLE ACCESS BY INDEX ROWID | TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 |
|* 4 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 |
|* 5 | FILTER | | 1112 | | 0 |00:00:01.13 | 16680 |
|* 6 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1112 | 1 | 0 |00:00:01.12 | 16680 |
|* 7 | INDEX RANGE SCAN | TAB2_COL1 | 1112 | 2000 | 2224K|00:00:04.46 | 8896 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NULL)
4 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
5 - filter(TRUNC(SYSDATE@!-1)<=TRUNC(SYSDATE@!))
6 - filter(LNNVL("KEY1"<>:B1))
7 - access("COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!))
- 메인쿼리에서 추출한 범위가 매우 넓어서 서브쿼리가 랜덤으로 처리할 양이 매우 많아진다면 상당한 부담이 될 것이다.
- 이런한 경우에는 Sort Merge 조인처럼 각각의 집합을 별도로 한 번씩만 액세스하여 정렬시킨 다음 머지를 통해서 연결하는 것이 훨씬 유리하다.
- 부정형으로 연결되었지만 이런한 방식의 처리가 문제될 것은 없다. 머지 단계에서 일반적이 머지의 반대인 '머지에 실패한 것'을 추출하기만 하면 나머지는 동일한 방법이 되기 때문에이다.
- 그러나 대부분의 경우 옵티마이져는 필터형 처리로 실행계획을 수립하기 때문에 필요하다면 뭔가 특별한 조치를 해야 한다. ( Hash or Merge )
p.602 MERGE JOIN ANTI
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND KEY1 IS NOT NULL
5 AND KEY1 NOT IN ( SELECT /*+ MERGE_AJ */ KEY1
6 FROM TAB2 B
7 WHERE COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE )
8 AND KEY1 IS NOT NULL );
COUNT(*)
----------
1112
SQL> @XPLAN
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 224 | | | |
| 2 | MERGE JOIN ANTI | | 1 | 1 | 1112 |00:00:00.02 | 224 | | | |
| 3 | SORT JOIN | | 1 | 1216 | 1112 |00:00:00.01 | 209 | 24576 | 24576 |22528 (0)|
|* 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 | | | |
|* 5 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 | | | |
|* 6 | SORT UNIQUE | | 1112 | 2000 | 0 |00:00:00.01 | 15 | 83968 | 83968 |73728 (0)|
|* 7 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 2000 | 2000 |00:00:00.02 | 15 | | | |
|* 8 | INDEX RANGE SCAN | TAB2_COL1 | 1 | 2000 | 2000 |00:00:00.01 | 8 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("KEY1" IS NOT NULL)
5 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
6 - access("KEY1"="KEY1")
filter("KEY1"="KEY1")
7 - filter("KEY1" IS NOT NULL)
8 - access("COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!))
- NOT IN 을 사용한 경우만 가능하며 ( MERGE JOIN ANTI )
- 비용기준 옵티마이져로 정의 되어야만 한다.
- 세미 조인이 'IN' 이나 'EXISTS' 모두 가능하듯이 부벙형 조인도 당연히 그래야 하지만 현재까지는 'NOT EXISTS'를 사용한 경우는 힌트가 적용되지 않는다 ( 현재도..?? )
p.603 HASH JOIN ANTI
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND KEY1 IS NOT NULL
5 AND KEY1 NOT IN ( SELECT /*+ HASH_AJ */ KEY1
6 FROM TAB2 B
7 WHERE COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE )
8 AND KEY1 IS NOT NULL );
COUNT(*)
----------
1112
SQL> @XPLAN
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 224 | | | |
|* 2 | HASH JOIN ANTI | | 1 | 1 | 1112 |00:00:00.01 | 224 | 1517K| 1517K| 1180K (0)|
|* 3 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 | | | |
|* 4 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 2000 | 2000 |00:00:00.02 | 15 | | | |
|* 6 | INDEX RANGE SCAN | TAB2_COL1 | 1 | 2000 | 2000 |00:00:00.01 | 8 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("KEY1"="KEY1")
3 - filter("KEY1" IS NOT NULL)
4 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
5 - filter("KEY1" IS NOT NULL)
6 - access("COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!))
- MERGE_AJ과는 다르게 SORT UNIQUE 부분이 나타나지 않는 것은 해쉬 조인이 연결을 위해 사전에 준비해 두는 작업이 필요한 것은 머지 조인과 유사하지만 실제 연결단계에서 필터형식으로 처리되므로 굳이 유일한 집합을 만들어야 할 필요가 없기 때문이다.
- 항상 이런 방법이 유리한 것이 아니라는 것은 앞서 그 이유를 충분히 설명하였다. (막 쓰지말고 확인하고 )
- 특히 머지 부정형 조인이나 해쉬 부정형 조인은 전체범위 처리를 하는 경우가 많기 때문에 반드시 부분범위 처리를 하고자 할 때는 사용해서는 안 된다는 것을 분명이 기억하기 바란다.
- 세미 조인이 'IN' 이나 'EXISTS' 모두 가능하듯이 부벙형 조인도 당연히 그래야 하지만 현재까지는 'NOT EXISTS'를 사용한 경우는 힌트가 적용되지 않는다 ( 현재도..?? )
왜 IS NOT NULL을 추가해야하는가.? ( 없었을때는 안됨 )
- 긍정형 : 어차피 내가 찾고자 하는 데이터가 아니므로 빠지더라도 문제가 될 것이 없다. 1, 2, NULL 2를 액세스할 때 집합 내에 NULL의 존재유무는 결과에 아무런 문제도 일으키지 않는다.
- 부정형
- 2가 아닌 것은 1, NULL이지만, 만약 NULL이 비교연산에서 무시된다면 '1'만 찾게 된다. 이것은 우리가 원하는 결과가 아니다. ( 부정형 서브쿼리에서도 그대로 적용 )
- 예을 들어 메인쿼리에서 액세스한 값이 1,2,3,NULL이라고 하고 부정형으로 확인자가 될 서브쿼리에는 1,2,NULL이 있다고 가정해 보자.
- 메인쿼리의 결과 중에 서브쿼리에 없는 것을 찾이면 그 결과는 '3'과 'NULL'이다. 얼핏 생각하면 '3'만 찾아야 할 것처럼 보이지만 그것은 NULL값의 개념을 잘 모르는 사람들 생각
- 미지수 'X'와 미지수 'Y'는 같은 미지수라는 것일 뿐 이드을 '='이라고 할 수 없는 것과 같다.
- 그러므로 메인쿼리에 있는 NULL값이 서브쿼리에도 있다고 해서 존재한다는 조건(Exists)을 만족했다고 해서는 안된다.
결론 ?
- 부정형 해쉬 조인은 실제로는 긍정형과 같은 방법으로 조인을 시도해서 조인의 성공여부를 결정하는 방법만 반대로 할뿐이므로 연결되는 것을 찾는 작업을 하면서 그 결과만 반대로 처리한다.
- 만약 서브쿼리에 NULL값이 존재한다면 결과에 영향을 미칠 수 있으므로 NOT NULL 이라는 전제가 있을 때만 이런한 방식의 조인이 가능하다.
현재도 NOT EXISTS를 사용한 경우는 힌트가 적용되지 않는가.? ㅠ
보너스 MERGE JOIN ANTI ( NOT EXISTS )( 현재는 됨 )
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND KEY1 IS NOT NULL
5 AND NOT EXISTS ( SELECT /*+ MERGE_AJ */ 'X'
6 FROM TAB2 B
7 WHERE A.KEY1 = B.KEY1
8 AND COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE )
9 AND KEY1 IS NOT NULL );
COUNT(*)
----------
1112
SQL> @XPLAN
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 224 | | | |
| 2 | MERGE JOIN ANTI | | 1 | 1 | 1112 |00:00:00.02 | 224 | | | |
| 3 | SORT JOIN | | 1 | 1216 | 1112 |00:00:00.01 | 209 | 24576 | 24576 |22528 (0)|
|* 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 | | | |
|* 5 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 | | | |
|* 6 | SORT UNIQUE | | 1112 | 2000 | 0 |00:00:00.01 | 15 | 83968 | 83968 |73728 (0)|
|* 7 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 2000 | 2000 |00:00:00.02 | 15 | | | |
|* 8 | INDEX RANGE SCAN | TAB2_COL1 | 1 | 2000 | 2000 |00:00:00.01 | 8 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("KEY1" IS NOT NULL)
5 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
6 - access("A"."KEY1"="B"."KEY1")
filter("A"."KEY1"="B"."KEY1")
7 - filter("KEY1" IS NOT NULL)
8 - access("COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!))
보너스 HASH JOIN ANTI ( NOT EXISTS )( 현재는 됨 )
SQL> SELECT /*+ gather_plan_statistics INDEX( A TAB1_COL1 )*/ COUNT( * )
2 FROM TAB1 A
3 WHERE COL1 LIKE '상품1%'
4 AND KEY1 IS NOT NULL
5 AND NOT EXISTS ( SELECT /*+ HASH_AJ */ 'X'
6 FROM TAB2 B
7 WHERE A.KEY1 = B.KEY1
8 AND COL1 BETWEEN TRUNC( SYSDATE - 1 ) AND TRUNC( SYSDATE )
9 AND KEY1 IS NOT NULL );
COUNT(*)
----------
1112
SQL> @XPLAN
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 224 | | | |
|* 2 | HASH JOIN ANTI | | 1 | 1 | 1112 |00:00:00.01 | 224 | 1517K| 1517K| 1189K (0)|
|* 3 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 1216 | 1112 |00:00:00.01 | 209 | | | |
|* 4 | INDEX RANGE SCAN | TAB1_COL1 | 1 | 1216 | 1112 |00:00:00.01 | 5 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 2000 | 2000 |00:00:00.02 | 15 | | | |
|* 6 | INDEX RANGE SCAN | TAB2_COL1 | 1 | 2000 | 2000 |00:00:00.01 | 8 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."KEY1"="B"."KEY1")
3 - filter("KEY1" IS NOT NULL)
4 - access("COL1" LIKE '상품1%')
filter("COL1" LIKE '상품1%')
5 - filter("KEY1" IS NOT NULL)
6 - access("COL1">=TRUNC(SYSDATE@!-1) AND "COL1"<=TRUNC(SYSDATE@!))
"구루비 데이터베이스 스터디모임" 에서 2011년에 "새로쓴 대용량 데이터베이스 솔루션1" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4456
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.