중복 조인을 통한 해시 조인 성능 개선

해시 조인의 Temp 사용으로 인해 성능이 저하되는 경우 해시 조인 결과가 매우 작다면 중복 조인을 통해 Work Area 사용량을 최소화하여 성능을 개선할 수 있습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1, LPAD ('X', 1000, 'X') AS c2 FROM XMLTABLE ('1 to 100000');
CREATE TABLE t2 AS SELECT c1 + 99999 AS c1, c2 FROM t1;

 

아래 쿼리는 101 MB의 Temp를 사용하여 수행에 0.63초가 소요됩니다.

-- 2
SELECT *
  FROM t1 a
     , t2 b
 WHERE b.c1 = a.c1;

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |   A-Time   | Buffers | Reads  | Writes | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |      1 |00:00:00.63 |   28593 |  11625 |  11625 |          |         |
|*  1 |  HASH JOIN         |      |      1 |      1 |00:00:00.63 |   28593 |  11625 |  11625 |  108M (1)|     101M| -- !
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100K|00:00:00.03 |   14296 |      0 |      0 |          |         |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|00:00:00.03 |   14296 |      0 |      0 |          |         |
-------------------------------------------------------------------------------------------------------------------

 

아래와 같이 t1, t2 테이블을 해시 조인하여 ROWID만 조회하고, ROWID로 t1, t2 테이블을 NL 조인하는 방식으로 Work Area 사용량을 최소화할 수 있습니다. 8935 KB의 PGA를 사용하여 수행에 0.08초가 소요됩니다.

-- 2
SELECT /*+ LEADING(A) USE_NL(B C) ROWID(B) ROWID(C) */
       b.*
     , c.*
  FROM (SELECT /*+ NO_MERGE */
               a.ROWID AS t1_rowid
             , b.ROWID AS t2_rowid
          FROM t1 a
             , t2 b
         WHERE b.c1 = a.c1) a
     , t1 b
     , t2 c
 WHERE b.ROWID = a.t1_rowid
   AND c.ROWID = a.t2_rowid;

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |      1 |00:00:00.08 |   28594 |          |
|   1 |  NESTED LOOPS                |      |      1 |      1 |00:00:00.08 |   28594 |          |
|   2 |   NESTED LOOPS               |      |      1 |      1 |00:00:00.08 |   28593 |          |
|   3 |    VIEW                      |      |      1 |      1 |00:00:00.08 |   28592 |          |
|*  4 |     HASH JOIN                |      |      1 |      1 |00:00:00.08 |   28592 | 8935K (0)| -- !
|   5 |      TABLE ACCESS FULL       | T1   |      1 |    100K|00:00:00.02 |   14296 |          |
|   6 |      TABLE ACCESS FULL       | T2   |      1 |    100K|00:00:00.02 |   14296 |          |
|   7 |    TABLE ACCESS BY USER ROWID| T1   |      1 |      1 |00:00:00.01 |       1 |          |
|   8 |   TABLE ACCESS BY USER ROWID | T2   |      1 |      1 |00:00:00.01 |       1 |          |
-------------------------------------------------------------------------------------------------