쿼리 변환 - 조인 FACTORIZATION

SQL> SELECT /*+ FACTORIZE_JOIN(@"SET$1") */ *
  2  FROM t1, t2
  3  WHERE t1.id = t2.id AND t2.id < 10
  4  UNION ALL
  5  SELECT *
  6  FROM t1, t2
  7  WHERE t1.id = t2.id AND t2.id > 990;
SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ FACTORIZE_JOIN(@"SET$1") */ * FROM t1, t2 WHERE t1.id =
t2.id AND t2.id < 10 UNION ALL SELECT * FROM t1, t2 WHERE t1.id = t2.id
AND t2.id > 990

Plan hash value: 1282049862

---------------------------------------------------
| Id  | Operation            | Name               |
---------------------------------------------------
|   0 | SELECT STATEMENT     |                    |
|   1 |  HASH JOIN           |                    |
|   2 |   VIEW               | VW_JF_SET$3DF3FC68 |
|   3 |    UNION-ALL         |                    |
|   4 |     TABLE ACCESS FULL| T2                 |
|   5 |     TABLE ACCESS FULL| T2                 |
|   6 |   TABLE ACCESS FULL  | T1                 |
---------------------------------------------------

SQL> SELECT /*+ NO_FACTORIZE_JOIN(@"SET$1") */ *
  2  FROM t1, t2
  3  WHERE t1.id = t2.id AND t2.id < 10
  4  UNION ALL
  5  SELECT *
  6  FROM t1, t2
  7  WHERE t1.id = t2.id AND t2.id > 990;
SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ NO_FACTORIZE_JOIN(@"SET$1") */ * FROM t1, t2 WHERE t1.id =
t2.id AND t2.id < 10 UNION ALL SELECT * FROM t1, t2 WHERE t1.id = t2.id
AND t2.id > 990

Plan hash value: 4147288133

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