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
4898 rows selected. Incorrect Result
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';
4898 rows selected. Incorrect Result
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.
|