Mohamed Houri’s Oracle Notes

August 9, 2020

SET operation to join

Filed under: Oracle — hourim @ 10:06 am

In this article about semi-join in modern relational databases I’ve shown that, neither Oracle nor Postgres, are able to transform a SET operation like INTERSECT or MINUS into a SEMI-JOIN. Jonathan Lewis has, however, explained in a comment to this article, that Oracle has been, in fact, able to handle conversions from INTERSECT (and minus) to semi join (and anti-join) since 10g. But the conversion is only possible by changing the default value of the _convert_set_to_join parameter from FALSE to TRUE. Starting from 19.1.0.1 the conversion is now done automatically because Oracle has finally decided to let this transformation happening by default as outlined by Tanel Poder cofep script

SQL> @cofep 12.2.0.1 19.1.0.1
Compare Optimizer_Features_Enable Parameter differences
for values 12.2.0.1 and 19.1.0.1

PARAMETER              '12.2.0.1'    '19.1.0.1'   DESCRIPTION
---------------------- ------------- ------------ --------------------------------------------
_convert_set_to_join   FALSE         TRUE         enables conversion of set operator to join

Here’s below a simple demonstration

create table t1
as 
select
   rownum n1
  ,mod(rownum,5) n2
  ,trunc( (rownum -1/3)) n3
from
  dual
connect by
  level <= 1e4;  
  
create table t2
as 
select
   rownum n1
  ,mod(rownum,3) n2
  ,trunc( (rownum -1/5)) n3
from
  dual
connect by
  level <= 5;    

exec dbms_stats.gather_table_stats(user, ‘t1’) ;
exec dbms_stats.gather_table_stats(user, ‘t2’) ;
SQL> show parameter optimizer_features

PARAMETER_NAME                  TYPE        VALUE
------------------------------- ----------- ---------
optimizer_features_enable       string      19.1.0

SQL> @pd _convert_set
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                  VALUE    DESCRIPTION
---------- ----- --------------------- -------- ------------------------------------------
      3718   E86 _convert_set_to_join  FALSE    enables conversion of set operator to join

SQL> select t2.n1 from t2 intersect select t1.n1 from t1;

        N1
----------
         1
         2
         3
         4
         5

SQL> select * from table(dbms_xplan.display_cursor);

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  INTERSECTION       |      |       |       |
|   2 |   SORT UNIQUE       |      |     5 |    15 |
|   3 |    TABLE ACCESS FULL| T2   |     5 |    15 |
|   4 |   SORT UNIQUE       |      | 10000 | 40000 |
|   5 |    TABLE ACCESS FULL| T1   | 10000 | 40000 |
----------------------------------------------------

As you can see the intersect operation has not been transformed into a semi-join because the corresponding hidden parameter is by default set to FALSE.

But let’s now do the same experiment under the new optimizer 19.1.0.1 and check the difference:

SQL> alter session set optimizer_features_enable='19.1.0.1';

Session altered.

SQL> select t2.n1 from t2 intersect select t1.n1 from t1;

        N1
----------
         1
         2
         4
         5
         3

SQL> @xpsimp

SQL_ID  0atbzgvynmj51, child number 1
-------------------------------------
select t2.n1 from t2 intersect select t1.n1 from t1

Plan hash value: 3703458891
----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  HASH UNIQUE        |      |     5 |    35 |
|*  2 |   HASH JOIN SEMI    |      |     5 |    35 |
|   3 |    TABLE ACCESS FULL| T2   |     5 |    15 |
|   4 |    TABLE ACCESS FULL| T1   | 10000 | 40000 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SYS_OP_MAP_NONNULL("T2"."N1")=SYS_OP_MAP_NONNULL("T1"."N1"))

Spot now how the INTERSECT set operation has been automatically transformed into a HASH JOIN SEMI operation. This becomes possible by default because, starting from optimier_features_enable 19.1.0.1, the default value of the hidden parameter _convert_set_to_join is set to TRUE.

While I was trying to print out the execution plan non-sharing reason I spotted out something interesting:

SQL> @nonshared 0atbzgvynmj51
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

SQL_ID                        : 0atbzgvynmj51
ADDRESS                       : 00007FFB65E682E0
CHILD_ADDRESS                 : 00007FFB65E65D80
CHILD_NUMBER                  : 0
HASH_MATCH_FAILED             : Y
REASON                        : <ChildNode><ChildNumber>0</ChildNumber><ID>3</ID>
                                <reason>Optimizer mismatch(12)</reason><size>2x476</size>
								<optimizer_features_enable> 19.1.0  19.1.0.1  </optimizer_features_enable>
								<_optimizer_undo_cost_change> 19.1.0  19.1.0.1 </_optimizer_undo_cost_change>
								<_convert_set_to_join> false true              </_convert_set_to_join>
							    <_subquery_pruning_mv_enabled> false  true     </_subquery_pruning_mv_enabled>
							    <_optimizer_push_down_distinct> 0   5   </_optimizer_push_down_distinct>
							    <_fix_control_key> 0  260074645         </_fix_control_key>
							    <_px_shared_hash_join> false  true      </_px_shared_hash_join>
							    </ChildNode>
CON_ID                        : 1
-----------------
SQL_ID                        : 0atbzgvynmj51
ADDRESS                       : 00007FFB65E682E0
CHILD_ADDRESS                 : 00007FFB65C23F38
CHILD_NUMBER                  : 1
OPTIMIZER_MISMATCH            : Y
REASON                        :
CON_ID                        : 1
-----------------

PL/SQL procedure successfully completed.

It looks like Oracle is now showing all the new optimizer parameters that have changed between the optimizer used to compile child number 0 and that used to compile child cursor n°1. This is confirmed using Tanel Poder cofep script

SQL> @cofep 19.1.0 19.1.0.1
Compare Optimizer_Features_Enable Parameter differences
for values 19.1.0 and 19.1.0.1

PARAMETER                       '19.1.0'   '19.1.0.1' DESCRIPTION
------------------------------- ---------- ---------- ----------------------------------------------------------------------
_optimizer_push_down_distinct   0          5          push down distinct from query block to table
optimizer_features_enable       19.1.0     19.1.0.1   optimizer plan compatibility parameter
_optimizer_undo_cost_change     19.1.0     19.1.0.1   optimizer undo cost change
_subquery_pruning_mv_enabled    FALSE      TRUE       enable the use of subquery predicates with MVs to perform pruning
_convert_set_to_join            FALSE      TRUE       enables conversion of set operator to join
_px_shared_hash_join            FALSE      TRUE       enable/disable shared hash join

6 rows selected.

4 Comments »

  1. Hello, Mohamed.

    You wrote “Starting from 19.1.0.1 the conversion is now done automatically because Oracle has finally decided to let this transformation happening…”. It might not be quite true. I saw the correspondence between you and Franck Pachot where it was stated that you always need to append “.1” to the default optimizer_features_enable value to enable _convert_set_to_join (among other hidden parameters). So was in the case of the version 12.1.0.2 as in the Franck’s article https://blog.dbi-services.com/oracle-121021-set-to-join-conversion/. But I can’t investigate it further as I don’t have the version higher than 19 at my disposal.

    Comment by Vasiliy Antonov — February 25, 2021 @ 5:44 am | Reply

    • I wish I had a chance to correct “Frank” to “Franck” :).

      Comment by Vasiliy Antonov — February 25, 2021 @ 5:50 am | Reply

  2. Hi Vasiliy,

    I have compared the CBO parameter difference between 12.2.0.1 and 19.1.0.1 and have found that the default value of_convert_set_to_join has switched from FALSE to TRUE as from 19.1

    SQL> @cofep 12.2.0.1 19.1.0.1
    
    compare Optimizer_Features_Enable Parameter differences
    for values 12.2.0.1 and 19.1.0.1
    
    PARAMETER                                           '12.2.0.1'   '19.1.0.1'   DESCRIPTION
    --------------------------------------------------- ------------ ------------ ----------------------------------------------------------------------                                                                       enables or disables key vector support for timestamp
    _convert_set_to_join                                FALSE        TRUE         enables conversion of set operator to join
    _sqlexec_pwiseops_with_sqlfuncs_enabled             FALSE        TRUE         enables partition wise operations even in the presence of functions
     _px_dynamic_granules_adjust                         0            10           adjust dynamic granule regeneration
    _optimizer_gather_stats_on_load_index               FALSE        TRUE         enable/disable online index stats gathering for loads
    
    ../..
    _optimizer_gather_stats_on_conventional_config      65535        0            settings for optimizer online stats gathering on conventional DML
    _px_join_skew_use_histogram                         FALSE        TRUE         Enables retrieval of skewed values from histogram when possible
    _optimizer_key_vector_payload                       FALSE        TRUE         enables or disables dimension payloads in vector transform
    _optimizer_use_stats_on_conventional_dml            FALSE        TRUE         use optimizer statistics gathered for conventional DML
    _cell_offload_vector_groupby_withnojoin             FALSE        TRUE         allow offload of vector group by without joins
    _sqlexec_reorder_wif_enabled                        FALSE        TRUE         enable re-ordering of window functions
    _px_shared_hash_join                                FALSE        TRUE         enable/disable shared hash join
    _px_join_skew_null_handling                         FALSE        TRUE         enables null skew handling improvement for parallel outer joins
    _key_vector_join_pushdown_enabled                   FALSE        TRUE         enables or disables key vector join push down support
    _cell_offload_grand_total                           FALSE        TRUE         allow offload of grand total aggregations
    _bloom_pruning_setops_enabled                       FALSE        TRUE         Allow bloom pruning to be pushed through set operations
    _bloom_filter_setops_enabled                        FALSE        TRUE         Allow bloom filter to be pushed through set operations
    _cell_offload_vector_groupby_fact_key               FALSE        TRUE         enable cell offload of vector group by with fact grouping keys
    _px_hybrid_partition_execution_enabled              FALSE        TRUE         enable parallel hybrid partition execution
    _optimizer_gather_stats_on_conventional_dml         FALSE        TRUE         optimizer online stats gathering for conventional DML 
    

    So, probably that by adding this “1” at the 12c optimizer_features_enable has something special of a fix-control or anything else that makes the transformation happening by default

    Best regards

    Comment by hourim — February 25, 2021 @ 8:39 am | Reply

  3. Mohamed, but if you had compared 12.2.0.1.1 and 19.1.0.1 you would have probably found that the parameter hadn’t changed (TRUE). But I am sure you can’t use the cofep script for that as 12.2.0.1.1 is invalid value in 19c. But it is indeed a valid value in version 12.2.0.1. I don’t have SYS account at 12.2.0.1 to confirm it as clear as you can by using cofep script. But I witness it as the plan of a query changes from “MINUS” to “ANTI JOIN” when I switch the OFE parameter.

    So the behavior is the same when switching 12.1.0.2 to 12.1.0.2.1, 12.2.0.1 to 12.2.0.1.1, 18.1.0 to 18.1.0.1, 19.1.0 to 19.1.0.1. So I would suppose that Oracle has not yet finally decided to let this transformation happening by default. We have to see the behavior of version 21c with the default OFE value.

    Comment by Vasiliy Antonov — February 26, 2021 @ 3:27 am | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: