Semi-join to Inner-join enhacement in 12C

I will be using a slightly modified version of the example used by Jonathan Lewis  here to demonstrate the enhancement introduced in 12c to the semi-join to inner-join transformation.Let’s begin !

TEST CASE :

create table chi
as
with generator as (
        select  --+ materialize
                rownum  id
        from dual
        connect by
                level <= 1e4
)
select
        rownum - 1                              id,
        trunc((rownum-1)/10)                    n1,
        trunc(dbms_random.value(0,1000))        n2,
        rpad('x',1000)                          padding
from
        generator
;

create table par
as
with generator as (
        select  --+ materialize
                rownum  id
        from dual
        connect by
                level <= 1e4
)
select
        rownum - 1      id,
        rpad('x',1000)  padding
from
        generator
where
        rownum <= 1e3
;

exec dbms_stats.gather_table_stats(null,'PAR');
exec dbms_stats.gather_table_stats(null,'CHI');

Here is our test query :

select /*+   LEADING(@"SEL$5DA710D3" "P"@"SEL$2" )   FULL(@"SEL$5DA710D3" "P"@"SEL$2" )   */
        count(*)
from
        chi c
where   exists (
                select null
                from par p
                where p.id = c.n1
        )
;

Execution plan in 11.2.0.3.9 :

Execution plan in 12.1.0.2.6 :

Observation :

So in the two version the semi-join to inner join kicked in.We can see the addition of the “SORT UNIQUE” in the execution plan and that is to ensure that any row from the table “CHI” appears at most once in the result set.

Let’s now add a primary key on the column “PAR”(id)  so the row source SORT UNIQUE become redundant.

alter table par add constraint par_pk primary key(id);

Execution plan in 11.2.0.3.9 :

Execution plan in 12.1.0.2.6 :

Observation :

The optimizer in the 12c version has managed to eliminate the redundant “SORT UNIQUE” operation which will reduce the plan cost. It seem that this enhancement is controlled by the following fix control (Thanks to  Mikhail Velikikh  for pointing to it in the comment section here) :

select /*+   LEADING(@"SEL$5DA710D3" "P"@"SEL$2" )   FULL(@"SEL$5DA710D3" "P"@"SEL$2" )   OPT_PARAM( '_fix_control' '17088819:0') */
        count(*)
from
        chi c
where   exists (
                select null
                from par p
                where p.id = c.n1
        )
;

Note : Tony hasler has stated that this enhancement may be implemented in future release in his great book “Expert Oracle SQL”  page 319 “I imagine that the redundant SORT UNIQUE operation introduced by the transformation will disappear in a future release.” and he was right.

That’s it 😀