Mohamed Houri’s Oracle Notes

December 22, 2017

Null-Accepting Semi-Join

Filed under: CBO — hourim @ 11:15 am

Introduction

Null-Accepting semi-join is a new enhancement brought to the CBO by the 12cR1 release. It extends the semi-join algorithm to qualify rows from the table in the left hand side that have a null value in the join column.It kicks in for queries like the following one:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
         OR exists (SELECT null FROM t2 where t2.n1 = t1.n1)
		);

It is recognisable in execution plans via its acronym NA (Null-Accepting)

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN SEMI NA |      |     7 |    42 |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------

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

It appears in the CBO 10053 trace file as (for its HASH JOIN version):

 Best:: JoinMethod: HashNullAcceptingSemi

Don’t get confused by the NA acronym that appears in the ANTI-JOIN operation where it refers to Null-Aware rather than to Null-Accepting as shown in the following execution plan and 10053 trace file respectively:

SELECT  
   count(1)
FROM t1
   WHERE t1.n1 NOT IN (select n1 from t2);

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN ANTI NA |      |     1 |     6 |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------

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

 Best:: JoinMethod: HashNullAwareAnti

If you want to clear this confusion then remember that the Null-Accepting transformation occurs for rows that (SEMI) join while Null-Aware transformation is for rows that would not join (ANTI).

The semi-join Null-Accepting logical operation can also be serviced by the NESTED LOOP physical operation as the following demonstrates:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
         OR exists (SELECT /*+ NL_SJ */ null FROM t2 where t2.n1 = t1.n1)
		); 

------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |
|   1 |  SORT AGGREGATE       |      |     1 |     6 |
|   2 |   NESTED LOOPS SEMI NA|      |     7 |    42 |
|   3 |    TABLE ACCESS FULL  | T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL  | T2   |     7 |    21 |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 – filter("T2"."N1"="T1"."N1")

Best:: JoinMethod: NestedLoopNullAcceptingSemi

It is the ability, acquired by the CBO as from 12cR1, to unnest the above kind of disjunctive subquery that makes the Null-Accepting transformation possible as shown in the corresponding10053 trace file:

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$2 (#2)
SU: Transforming EXISTS subquery to a join.
Registered qb: SEL$5DA710D3 0x1d225e60 (SUBQUERY UNNEST SEL$1; SEL$2)

Prior to 12cR1 it was not possible to automatically unnest the above subquery to join it with its parent block leading to the below execution plan where the inner table T2 is scanned mutliple times:

SELECT /*+ gather_plan_statistics */ 
   count(1)
FROM t1
   WHERE(t1.n1 is null
         OR exists (SELECT /*+ no_unnest */ null FROM t2 where t2.n1 = t1.n1)
        );  
---------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |
|*  2 |   FILTER            |      |      1 |        |     10 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |     10 |     10 |
|*  4 |    TABLE ACCESS FULL| T2   |      7 |      1 |      7 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 - filter("T2"."N1"=:B1)

The Bug

The Null-Accepting semi-join transformation comes, unfortunately, with a bug already identified in MyOracle Support via number 21201446. Here’s below how it can be reproduced:


SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

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

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

update t1 set n1 = null where n1 in (5,6,7);

exec dbms_stats.gather_table_stats(user, 't1');

exec dbms_stats.gather_table_stats(user, 't2');

SQL> SELECT  
         count(1)
    FROM t1
      WHERE(t1.n1 is null
         OR exists 
           (SELECT null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	    );

  COUNT(1)
----------
         7

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN SEMI NA |      |     7 |    42 |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(NVL("T2"."N1",42)=NVL("T1"."N1",42))

Using the HASH JOIN physical operation the query returns 7 rows. But it returns 10 rows when it uses the NESTED LOOP operation a shown below:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT /*+ NL_SJ */ null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

  COUNT(1)
----------
        10
		
------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |
|   1 |  SORT AGGREGATE       |      |     1 |     6 |
|   2 |   NESTED LOOPS SEMI NA|      |     7 |    42 |
|   3 |    TABLE ACCESS FULL  | T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL  | T2   |     7 |    21 |
------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 – filter(NVL("T2"."N1",42)=NVL("T1"."N1",42))	

The Null-Accepting semi-join transformation is driven by the following hidden parameter which , if cancelled, will workarround this bug as shown below:

SQL> alter session set "_optimizer_null_accepting_semijoin"=false;

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

  COUNT(1)
----------
        10

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |
|*  2 |   FILTER            |      |       |       |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     3 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 - filter(NVL("T2"."N1",42)=NVL(:B1,42))

How this has been fixed in 12cR2?

Because of the bug n° 21201446 It seems that Oracle has completely cancelled the Null-Accepting semi-join transformation in 12cR2 for both NESTED LOOP and HASH JOIN physical operations when the NVL function is applied on the join column. Here’s below why I am thinking so:

SQL> select banner from v$version where rownum=1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

  COUNT(1)
----------
        10

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |
|*  2 |   FILTER            |      |       |       |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     3 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 – filter(NVL("T2"."N1",42)=NVL(:B1,42))

In 12cR1, as we’ve shown above, the NESTED LOOP was not concerned by the bug. As from 12cR2 the NESTED LOOP SEMI JOIN is not anymore allowed to occur if a NVL function is applied on the join column as the following proves:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT /*+ NL_SJ */ null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

  COUNT(1)
----------
        10

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |
|*  2 |   FILTER            |      |       |       |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     3 |
----------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$2 / T2@SEL$2

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('_optimizer_null_accepting_semijoin' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      PQ_FILTER(@"SEL$1" SERIAL)
      FULL(@"SEL$2" "T2"@"SEL$2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 - filter(NVL("T2"."N1",42)=NVL(:B1,42))
Advertisements

Leave a Comment »

No comments yet.

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 )

w

Connecting to %s

Create a free website or 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.

EU Careers info

Your career in the European Union

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: