JF – Join Factorization

Just a quickie. No nice isolated demo etc.

I was just looking at rewriting a query that someone asked me to review.

I was playing with what should be the initial driving logic of a bigger query, changing it from a DECODE to a UNION ALL (or an OR) to make use of a FILTER condition.

i.e. from something like:

SELECT t.*
FROM   yyyyy c
,      xxxxx t
WHERE  c.trga_code   = 'LON'
AND    t.cmpy_num    = c.cmpy_num
AND    DECODE(:b1,'S',t.value_date,t.tran_date) >= TO_DATE(:b2,'YYYYMMDD')
AND    DECODE(:b1,'S',t.value_date,t.tran_date) <= TO_DATE(:b4,'YYYYMMDD');

to something not dissimilar to:

SELECT t.*
FROM   yyyyy c
,      xxxxx t
WHERE  c.trga_code   = 'LON'
AND    t.cmpy_num    = c.cmpy_num
AND    :b1           = 'S'
AND    t.value_date >= TO_DATE(:b2,'YYYYMMDD')
AND    t.value_date <= TO_DATE(:b4,'YYYYMMDD')
UNION ALL
SELECT t.*
FROM   yyyyy c
,      xxxxx t 
WHERE  c.trga_code  = 'LON'
AND    t.cmpy_num   = c.cmpy_num
AND    :b1         != 'S'
AND    t.tran_date >= TO_DATE(:b2,'YYYYMMDD')
AND    t.tran_date <= TO_DATE(:b4,'YYYYMMDD');

And I got an unexpected execution plan:

---------------------------------------------------------------------------------------------
| Id  | Operation                              | Name               | Rows  | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                    | 13271 |       |       |
|*  1 |  HASH JOIN                             |                    | 13271 |       |       |
|*  2 |   INDEX RANGE SCAN                     | YYYYY_3_IDX        |   268 |       |       |
|   3 |   VIEW                                 | VW_JF_SET$49BA79CF | 13304 |       |       |
|   4 |    UNION-ALL                           |                    |       |       |       |
|*  5 |     FILTER                             |                    |       |       |       |
|   6 |      PARTITION LIST ALL                |                    |  6652 |     1 |    11 |
|*  7 |       TABLE ACCESS BY LOCAL INDEX ROWID| XXXXX              |  6652 |     1 |    11 |
|*  8 |        INDEX RANGE SCAN                | XXXXX_16_IDX       |   112K|     1 |    11 |
|*  9 |     FILTER                             |                    |       |       |       |
|  10 |      PARTITION LIST ALL                |                    |  6652 |     1 |    11 |
|* 11 |       TABLE ACCESS BY LOCAL INDEX ROWID| XXXXX              |  6652 |     1 |    11 |
|* 12 |        INDEX RANGE SCAN                | XXXXX_4_IDX        | 67411 |     1 |    11 |
---------------------------------------------------------------------------------------------

That’s not quite what I was expecting – weird.

I was sitting here thinking “what’s that? … vw_jf? vw_jf?… hang on … vw_jf … jf … jf rings a bell… join factorization”.
Bingo.

See the Oracle Optimizer Blog for more information on Join Factorization and where they summarise the feature as

Join factorization is a cost-based transformation.
It can factorize common computations from branches in a UNION ALL query which can lead to huge performance improvement.

Fair enough. But I don’t want it. Not just yet. Let’s turn it off.
(Although I’ve mentioned before that I don’t tend to like being too prescriptive regarding a “correct plan”, in this case, for my purposes, I don’t want it doing that. I might let the CBO reconsider it later once I piece everything back together but for now…)

V$SQL_HINT mentions a NO_FACTORIZE_JOIN hint.

Unfortunately (or perhaps fortunately given the vast array of undocumented hints exposed here), it doesn’t tell us how to use it and I fiddled around for a bit but couldn’t get it to work.

So, the proper approach is to use ALTER SESSION or OPT_PARAM to change the setting of _optimizer_join_factorization (don’t go changing the setting of hidden parameters without the approval of Oracle Support, etc, etc, etc).

Which gave me what I wanted (for now).

--------------------------------------------------------------------------------------------- 
| Id  | Operation                             | Name                | Rows  | Pstart| Pstop | 
--------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                      |                     | 13272 |       |       | 
|   1 |  UNION-ALL                            |                     |       |       |       | 
|*  2 |   FILTER                              |                     |       |       |       | 
|*  3 |    HASH JOIN                          |                     |  6636 |       |       | 
|*  4 |     INDEX RANGE SCAN                  | YYYYY_3_IDX         |   268 |       |       | 
|   5 |     PARTITION LIST SUBQUERY           |                     |  6652 |KEY(SQ)|KEY(SQ)| 
|*  6 |      TABLE ACCESS BY LOCAL INDEX ROWID| XXXXX               |  6652 |KEY(SQ)|KEY(SQ)| 
|*  7 |       INDEX RANGE SCAN                | XXXXX_16_IDX        |   112K|KEY(SQ)|KEY(SQ)| 
|*  8 |   FILTER                              |                     |       |       |       | 
|*  9 |    HASH JOIN                          |                     |  6636 |       |       | 
|* 10 |     INDEX RANGE SCAN                  | YYYYY_3_IDX         |   268 |       |       | 
|  11 |     PARTITION LIST SUBQUERY           |                     |  6652 |KEY(SQ)|KEY(SQ)| 
|* 12 |      TABLE ACCESS BY LOCAL INDEX ROWID| XXXXX               |  6652 |KEY(SQ)|KEY(SQ)| 
|* 13 |       INDEX RANGE SCAN                | XXXXX_4_IDX         | 67411 |KEY(SQ)|KEY(SQ)| 
--------------------------------------------------------------------------------------------- 

P.S. This is not meant to be an investigation into the whys and wherefores of why JF was picked and whether it was a good choice, nor any reflection of whether JF is a good thing and whether there are any issues with it.

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

Further information on those Internal View names:
Jonathan Lewis – Internal Views

Advertisements