The database version we have 12.1.0.2. The problem is because we have hit the bug 25392535 in 12.1.0.2 version. Query giving wrong result because of parallelism. In our database default parallelism is enable 2.

 

SQL> show parameter parallel_threads_per_cpu;

 

NAME                                 TYPE        VALUE

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

parallel_threads_per_cpu             integer     2

 

The following way you can test your query giving incorrect or correct result.

 

Option 1: Change Optimizer feature for session

• Execute the query and the query gives wrong result (I have not share here the complex query executed in my database)

 

4898 rows selected.

Incorrect Result

 

• alter Session Set optimizer_features_enable = '11.2.0.4';

 

• Re-execute the query again.

 

5428 rows selected.

Correct Result

 

Option 2: Disable parallelism for session

[Optional] If you already change the optimizer to '11.2.0.4' change it back

alter Session Set optimizer_features_enable = '12.1.0.2';

• Execute the query

 

4898 rows selected.

Incorrect Result

 

• ALTER session disable parallel query;
• Re-execute your query

 

5428 rows selected.

Correct Result.

 

Solution:

Apply patch 25392535 which is available in MOS see https://updates.oracle.com/download/25392535.html

  

Please check Oracle Doc ID 2290845.1 and Doc ID 2217465.1 for more information.