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

5 Comments

  1. Posted January 2, 2010 at 04:28 | Permalink | Reply

    interesting. What if you add primary key to T1 and foreign keys to T2 and T3. on my tests after I add the keys this is the plan I can see

    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      | 20000 |   156K|    16  (50)| 00:00:01 |
    |   1 |  UNION-ALL         |      |       |       |            |          |
    |*  2 |   TABLE ACCESS FULL| T2   | 10000 | 80000 |     8   (0)| 00:00:01 |
    |*  3 |   TABLE ACCESS FULL| T3   | 10000 | 80000 |     8   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    

    and for the same keys and same statistics on 10.2.0.4 what I get is below

    ------------------------------------------------------------------------------------
    | Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |              | 20000 |   214K|    16  (63)| 00:00:01 |
    |   1 |  UNION-ALL          |              |       |       |            |          |
    |   2 |   NESTED LOOPS      |              | 10000 |   107K|     8  (25)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL| TEST2        | 10000 | 70000 |     6   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN| SYS_C0012227 |     1 |     4 |     0   (0)| 00:00:01 |
    |   5 |   NESTED LOOPS      |              | 10000 |   107K|     8  (25)| 00:00:01 |
    |   6 |    TABLE ACCESS FULL| TEST3        | 10000 | 70000 |     6   (0)| 00:00:01 |
    |*  7 |    INDEX UNIQUE SCAN| SYS_C0012227 |     1 |     4 |     0   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------
    

    I am a bit lazy today to do 10053 but maybe tomorrow.

    Thank you for joining blogosphere with your blog Timur it is way easy to follow your work now comparing to your invaluable comments on certain blogs

    • Posted January 8, 2010 at 22:06 | Permalink | Reply

      Hello, Coscan,

      thank you for kind words :)

      Your first plan with additional parent-child relations between t1 and t2/t3 tables makes possible another query transformation – join elimination applied in two query blocks, thus removing redundant joins.

      PS. I’ve edited your comment to be well-formatted with sourcecode tag.

      • Posted January 10, 2010 at 23:06 | Permalink | Reply

        But normally this should happen in the second execution plan which was taken from 10.2.0.4 DB am I wrong. This was the reason why I did the comment actually. I was thinking maybe it is not the elimination but the factoring in 11.2.0.1 but I did not see any JF related information on 10053 trace. Interesting issue

  2. Posted August 8, 2010 at 17:09 | Permalink | Reply

    I’m also confused , why not try JF . And JF introduces a wrong result bug which i am fighting , noted in Bug 9504322

    • Posted August 8, 2010 at 21:05 | Permalink | Reply

      Hi maclean,

      Thank you for bug #. It seems like there are one-off patches for it for some platforms so you could try it or request it to be ported on your platform.

2 Trackbacks

  1. By COALESCE_SQ « Timur Akhmadeev's blog on January 18, 2010 at 00:16

    [...] of them represents new CBO features (like FACTORIZE_JOIN/NO_FACTORIZE_JOIN). Here I’ll show purpose of COALESCE_SQ hint (NO_COALESCE is it’s [...]

  2. By JF – Join Factorization « OraStory on June 7, 2011 at 18:31

    [...] Further sources on Join Factorization: Oracle Optimizer Blog – Join Factorization Jože Senegačnik – Query Transformations Timur Akhmadeev – Join Factorization [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 289 other followers