Join factorization

Oracle 11.2.0.1 has introduced one of the most intuitive features to the cost-based transformation: join factorization.

In short, it states

(T1 join T2) union [all] (T1 join T3)

can be transformed to

T1 join (T2 union [all] T3)

Nice, isn’t it? Well, it looks cool, but unfortunately it doesn’t work as expected in 11.2.0.1. Here is a working example:

drop table t1 cascade constraints purge;
drop table t2 cascade constraints purge;
create table t1(x, pad) as select rownum, rownum from dual connect by level <= 10000;
create table t2(y, pad) as select rownum, rownum from dual connect by level <= 10000;

explain plan for
select * from t1 join t2 on (t1.x = t2.y)
union all
select * from t1 join t2 on (t1.x = t2.y);

select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    | 20000 |  1269K|    25   (4)| 00:00:01 |
|*  1 |  HASH JOIN           |                    | 20000 |  1269K|    25   (4)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T1                 | 10000 |   253K|     8   (0)| 00:00:01 |
|   3 |   VIEW               | VW_JF_SET$401CA474 | 20000 |   761K|    16   (0)| 00:00:01 |
|   4 |    UNION-ALL         |                    |       |       |            |          |
|   5 |     TABLE ACCESS FULL| T2                 | 10000 |   253K|     8   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2                 | 10000 |   253K|     8   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - access("T1"."X"="ITEM_1")

As you can see Oracle has introduced an inline view VW_JF_SET$401CA474 (where JF stands for Join Factorization), but example is useless: a query is just a copy-paste of two identical query blocks combined with UNION ALL. Next query is more like a “real-world” case:

drop table t3 cascade constraints purge;
create table t3(z, pad) as select rownum, rownum from dual connect by level <= 10000;

explain plan for
select t1.x, t2.pad from t1 join t2 on (t1.x = t2.y)
union all
select t1.x, t3.pad from t1 join t3 on (t1.x = t3.z);

select * from table(dbms_xplan.display);----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 20000 |   761K|    34  (53)| 00:00:01 |
|   1 |  UNION-ALL          |      |       |       |            |          |
|*  2 |   HASH JOIN         |      | 10000 |   380K|    17   (6)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   | 10000 |   126K|     8   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   | 10000 |   253K|     8   (0)| 00:00:01 |
|*  5 |   HASH JOIN         |      | 10000 |   380K|    17   (6)| 00:00:01 |
|   6 |    TABLE ACCESS FULL| T1   | 10000 |   126K|     8   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL| T3   | 10000 |   253K|     8   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("T1"."X"="T2"."Y")
   5 - access("T1"."X"="T3"."Z")

Now Oracle decided to leave everything intact, with a message in 10053 trace saying

 Join-Factorization on query block SET$1 (#1)
JF: Using search type: exhaustive
JF: Generate basic transformation units
Validating JF unit: (branch: {2, 3} table: {T1, T1})
  rejected: join predicates do not match 

JF: Generate transformation units from basic units


JF: No state generated.

which means “no JF possible”.
PS. more details on this feature can be found in US Patent 2007/0219969 A1 – there are number of interesting information I planned to verify using new Oracle release. Seems it’s not possible now due to bug(s).

About these ads