Shulou(Shulou.com)05/31 Report--

This article mainly introduces "oracle sql optimization not in clause contains null return result analysis", in daily operation, I believe many people in oracle sql optimization not in clause contains null return result analysis problem has doubts, small make up consult all kinds of data, sort out simple and easy to use operation method, hope to answer "oracle sql optimization not in clause contains null return result analysis" doubts helpful! Next, please follow the small series to learn together!

To create a test sheet:

create table t_dept as select * from scott.dept;

create table t_emp as select * from scott.emp;

insert into t_emp(deptno,ename) values(null,'MINGSHUO'); --Insert a data item into emp table, deptno column null

commit;

The data structure is as follows:

SQL> select distinct deptno from t_emp;

DEPTNO

----------

30

20

10

SQL> select distinct deptno from t_dept;

DEPTNO

----------

30

20

40

10

At this time, a query is initiated to query the department information not in emp but in dept table:

SQL> select * from t_dept where deptno not in (select deptno from t_emp where deptno is not null);

DEPTNO DNAME LOC

---------- -------------- -------------

40 OPERATIONS BOSTON

At this point there are results returned.

Then remove where dept is not null from the subquery and run the query again:

SQL> select * from t_dept where deptno not in (select deptno from t_emp);

no rows selected

The return result is empty.

Many people wonder why the subquery result set includes null, for example, when t_dept.deptno is 40, 40 not in (10,20,30,null) is also true. After all, oracle query optimizer is not as intelligent as human brain to understand flexibility, check the execution plan is easier to understand.

Execution Plan

----------------------------------------------------------

Plan hash value: 2864198334

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 172 | 5 (20)| 00:00:01 |

|* 1 | HASH JOIN ANTI NA | | 4 | 172 | 5 (20)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T_DEPT | 4 | 120 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T_EMP | 15 | 195 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("DEPTNO"="DEPTNO")

Note

-----

- dynamic sampling used for this statement (level=2)

Note here that id 1 is HASH JOIN ANTI NA. Not in is sensitive to null values. Therefore, ordinary anti-join is unable to handle null, so oracle introduced an improved version of the anti-join method that can handle null, this method is called "Null-Aware Anti Join". The keyword NA in operation comes from this.

In Oracle 11gR2, Oracl controls NA through the implicit parameter_OPTIMIZER_NULL_AWARE_ANTIJOIN, which defaults to TRUE to enable Null-Aware Anti Join.

Turn it off below, and then observe:

alter session set "_optimizer_null_aware_antijoin" = false;

Select * from t_dept where deptno not in (select deptno from t_emp);

The implementation plan is as follows:

Execution Plan

----------------------------------------------------------

Plan hash value: 393913035

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 30 | 4 (0)| 00:00:01 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL| T_DEPT | 4 | 120 | 2 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T_EMP | 14 | 182 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "T_EMP" "T_EMP" WHERE

LNNVL("DEPTNO":B1)))

3 - filter(LNNVL("DEPTNO":B1))

Note

-----

- dynamic sampling used for this statement (level=2)

lnnvl is used for a condition in the where clause of a statement, returning false if the condition is true; returning true if the condition is UNKNOWN or false. This function cannot be used in compound conditions such as AND, OR, or BETWEEN.

In this case, for example, when t_dept.deptno is 40,(40 not in 10) and (40 not in 20) and (40 not in 30) and (40 not in null), note that here is and"and", the conditions need to be satisfied.

True and true and false or unknown. After the lvnnvl function:

false and false and true, the result is still false. So naturally, it won't work out.

If you don't understand it, let's put it in a more intuitive way:

SQL> select * from t_dept where deptno not in (10,20,null);

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 719542577

----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T_DEPT | 1 | 30 | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("DEPTNO"10 AND "DEPTNO"20 AND

"DEPTNO"TO_NUMBER(NULL))

Note

-----

- dynamic sampling used for this statement (level=2)

Filter condition "DEPTNO"10 AND "DEPTNO"20 AND "DEPTNO"TO_NUMBER(NULL) Because of the last and condition, the entire condition is always flase or unkonw.

Therefore, null values appear in the subquery of not in, and no results are returned.

This can be replaced with not exists and external links:

Not exist is written:

In fact, this writing has already appeared before. Just after disabling anti-join, appearing in fileter, oracle may use this wording when rewriting sql internally:

select *

from t_dept d

where not exists (select 1 from t_emp e where d.deptno = e.deptno);

External links are written:

select d.* from t_dept d, t_emp e where d.deptno=e.deptno(+) and e.deptno is null;

My colleague also showed me Ding Jun's experiment, which has a discussion of compound columns. The conclusion is simple and clear. Here I will directly move it over, as follows:

/**

According to NULL comparison and logic operation rules, OR condition has one TRUE returns TRUE, all FALSE results are FALSE, others are UNKNOWN, such as NULL

(1,2) not in (null,2) is equivalent to 1 null or 2 2, then obviously the result returned is UNKNOWN, so it cannot be true, does not return a result, but

(1,2) not in (null,3) is equivalent to 1 null or 2 3, because 23 is already TRUE, so the condition is TRUE, returning the result, which explains why Q2

That's how the test turned out.

**/

Here's a simple result:

SQL> SELECT * FROM DUAL WHERE (1,2) not in ( (null,2) );

DUMMY

-----

SQL> SELECT * FROM DUAL WHERE (1,2) not in ( (null,3) );

DUMMY

-----

X

At this point, the study of "not in clause in oracle sql optimization contains null and returns null analysis" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.