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 😀