JF – Join Factorization
June 7, 2011 1 Comment
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
Pingback: Sql tuning request « OraStory