해쉬(Hash)형 세미조인

• 필터 형식으로 처리되는 세미 조인은 랜덤 위주의 액세스가 발생하므로 만약 대량의 연결을 시도했을 때는 커다란 부담이 될 가능성이 충분히 있다.
• 물론 이런 문제를 해결하기 위해 Sort Merge형으로 실행을 유도할 수도 있겠지만, 일반적으로 해쉬 조인이 수행속도에 유리한 경우가 많기 때문에 이를 활용할 가치가 있다.

HASH JOIN SEMI

SQL> SELECT /*+ gather_plan_statistics  INDEX( X ORDDATE_INDEX ) LEADING( A ) */ COUNT(*)
  2    FROM "ORDER" X
  3  WHERE ORDDATE LIKE '20110621%'
  4       AND EXISTS ( SELECT /*+ HASH_SJ  */  'O'
  5                             FROM DEPT Y
  6                           WHERE Y.DEPTNO = X.SALDEPTNO
  7                               AND Y.TYPE1 = 1 );

  COUNT(*)
----------
     15832

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.12 |     678 |       |       |          |
|*  2 |   HASH JOIN SEMI              |               |      1 |    709 |  15832 |00:00:00.15 |     678 |  3256K|  1861K| 6674K (0)| <--
|   3 |    TABLE ACCESS BY INDEX ROWID| ORDER         |      1 |   3899 |  86400 |00:00:00.60 |     675 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | ORDDATE_INDEX |      1 |   3899 |  86400 |00:00:00.26 |     316 |       |       |          |
|*  5 |    TABLE ACCESS FULL          | DEPT          |      1 |      2 |      2 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("Y"."DEPTNO"="X"."SALDEPTNO")
   4 - access("ORDDATE" LIKE '20110621%')
       filter("ORDDATE" LIKE '20110621%')
   5 - filter("Y"."TYPE1"=1)

HASH JOIN RIGHT SEMI ( 적은 테이블을 선행 테이블로... )

SQL> SELECT /*+ gather_plan_statistics  INDEX( X ORDDATE_INDEX )  */ COUNT(*)
  2    FROM "ORDER" X
  3  WHERE ORDDATE LIKE '20110621%'
  4       AND EXISTS ( SELECT /*+ HASH_SJ  */  'O'
  5                             FROM DEPT Y
  6                           WHERE Y.DEPTNO = X.SALDEPTNO
  7                               AND Y.TYPE1 = 1 );

  COUNT(*)
----------
     15832

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.10 |     678 |       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI        |               |      1 |    709 |  15832 |00:00:00.14 |     678 |  1396K|  1396K|  474K (0)| <--
|*  3 |    TABLE ACCESS FULL          | DEPT          |      1 |      2 |      2 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| ORDER         |      1 |   3899 |  86400 |00:00:00.60 |     675 |       |       |          |
|*  5 |     INDEX RANGE SCAN          | ORDDATE_INDEX |      1 |   3899 |  86400 |00:00:00.26 |     316 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("Y"."DEPTNO"="X"."SALDEPTNO")
   3 - filter("Y"."TYPE1"=1)
   5 - access("ORDDATE" LIKE '20110621%')
       filter("ORDDATE" LIKE '20110621%')


26 개의 행이 선택되었습니다.

수행속도 향상의 요건

• 대량의 연결을 해야 하고 메모리 내에 충분한 해쉬 영역

제약 조건

• 1) 서브쿼리에는 하나의 테이블만 존재해야만 한다
• 2) 서브쿼리 내에 또 다시 서브쿼리를 사용했을 때는 적용이 불가능하다
• 3) 연결고리의 연산자는 반드시 '='이 되어야 한다.
• 4) 서브쿼리 내에 GROUP BY, CONNECT BY, ROWNUM을 사용할 수 없다.

부분 범위 가능 요건

• 어느 한쪽 집합이 메모리 내의 해쉬 영역에 내포 될 수 있으면 부분범위 처리가 가능하다 ( ???? )
• 이러한 경우는 온라인 애플리케이션의 경우 대량량 데이터라 하더라도 아주 빠른 수행 속도를 보장 받을 수가 있다.