Wrong Results on Antijoin due to Table Elimination from Execution Plan
(Doc ID 2465472.1)
Last updated on MAY 09, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]Information in this document applies to any platform.
Symptoms
- Wrong results are returned from an antijoin:
select rowid…
from test where… and ref IS NOT NULL
AND (id,date,text) NOT IN
(SELECT id, date,text FROM test_1)
AND (…) IN
(SELECT… FROM test_1);
No reference found to table, test_1, in the execution plan.
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1 | |
| 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TEST| 51K | 2096K | 17K | 00:03:28 | ◄◄◄ No reference to table, TEST_1, in execution plan.
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(NULL IS NOT NULL)
2 - filter("text" IS NOT NULL)
The filter, NULL IS NOT NULL, is observed.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |