VW_BUSHY 인터널 뷰

VW_BUSHY 인터널 뷰를 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

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

CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 100');
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE TABLE t4 AS SELECT * FROM t1;

아래는 해시 조인의 일반적인 실행계획이다. 이런 유형의 실행 계획을 Left Deep Tree 방식이라고 한다.

-- 2
SELECT /*+ ORDERED */
       *
  FROM t1 a, t2 b, t3 c, t4 d
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1
   AND d.c1 = c.c1;

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  HASH JOIN           |      |
|   2 |   HASH JOIN          |      |
|   3 |    HASH JOIN         |      |
|   4 |     TABLE ACCESS FULL| T1   |
|   5 |     TABLE ACCESS FULL| T2   |
|   6 |    TABLE ACCESS FULL | T3   |
|   7 |   TABLE ACCESS FULL  | T4   |
-------------------------------------

아래 쿼리는 SWAP_JOIN_INPUTS 힌트를 사용했다. 실행계획이 변경된 것을 확인할 수 있다. 이런 유형의 실행계획을 Right Deep Tree 방식이라고 한다.

-- 3
SELECT /*+ ORDERED SWAP_JOIN_INPUTS(C) SWAP_JOIN_INPUTS(D) */
       *
  FROM t1 a, t2 b, t3 c, t4 d
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1
   AND d.c1 = c.c1;

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  HASH JOIN           |      |
|   2 |   TABLE ACCESS FULL  | T4   |
|   3 |   HASH JOIN          |      |
|   4 |    TABLE ACCESS FULL | T3   |
|   5 |    HASH JOIN         |      |
|   6 |     TABLE ACCESS FULL| T1   |
|   7 |     TABLE ACCESS FULL| T2   |
-------------------------------------

아래 쿼리는 인라인 뷰와 NO_MERGE 힌트를 사용했다. 이런 유형의 실행계획을 Bushy Tree 방식이라고 한다. 조건에 따라 세 가지 방식의 실행계획을 선택하여 사용할 수 있다.

-- 4
SELECT *
  FROM (SELECT /*+ NO_MERGE */ b.* FROM t1 a, t2 b WHERE b.c1 = a.c1) b
     , (SELECT /*+ NO_MERGE */ c.* FROM t3 c, t4 d WHERE d.c1 = c.c1) c
 WHERE c.c1 = b.c1;

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  HASH JOIN           |      |
|   2 |   VIEW               |      |
|   3 |    HASH JOIN         |      |
|   4 |     TABLE ACCESS FULL| T1   |
|   5 |     TABLE ACCESS FULL| T2   |
|   6 |   VIEW               |      |
|   7 |    HASH JOIN         |      |
|   8 |     TABLE ACCESS FULL| T3   |
|   9 |     TABLE ACCESS FULL| T4   |
-------------------------------------

12.2 버전부터 인라인 뷰을 사용하지 않고 BUSHY_JOIN 힌트를 통해 Bushy Tree 방식을 사용할 수 있다. 실행계획 5번에서 VW_BUSHY 인터널 뷰를 확인할 수 있다.

-- 5
SELECT /*+ QB_NAME(MAIN) BUSHY_JOIN(@MAIN (C@MAIN D@MAIN)) LEADING(A B) SWAP_JOIN_INPUTS(C) */
       *
  FROM t1 a, t2 b, t3 c, t4 d
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1
   AND d.c1 = c.c1;

--------------------------------------------------
| Id  | Operation            | Name              |
--------------------------------------------------
|   0 | SELECT STATEMENT     |                   |
|   1 |  HASH JOIN           |                   |
|   2 |   HASH JOIN          |                   |
|   3 |    TABLE ACCESS FULL | T1                |
|   4 |    TABLE ACCESS FULL | T2                |
|   5 |   VIEW               | VW_BUSHY_7E92F0A1 |
|   6 |    HASH JOIN         |                   |
|   7 |     TABLE ACCESS FULL| T3                |
|   8 |     TABLE ACCESS FULL| T4                |
--------------------------------------------------

관련 힌트는 아래와 같다. 문서화되지 않은 힌트이므로 사용에 주의할 필요가 있다.

-- 6
SELECT name, inverse, version FROM v$sql_hint WHERE name = 'BUSHY_JOIN';

NAME       INVERSE       VERSION
---------- ------------- --------
BUSHY_JOIN NO_BUSHY_JOIN 12.2.0.1

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

아래는 해당 기능과 관련된 파라미터다. 기본적으로 비활성화되어 있다. (18.1 버전에서도 비활성되어 있다.)

-- 7
 NUM NAME                            VALUE  DESCRIPTION
---- ------------------------------- ------ ----------------------------------
3994 _optimizer_bushy_join           OFF    enables bushy join
3995 _optimizer_bushy_fact_dim_ratio 20     bushy join dimension to fact ratio
3996 _optimizer_bushy_fact_min_size  100000 minimumm fact size for bushy join
3997 _optimizer_bushy_cost_factor    100    cost factor for bushy join