After 12.2 Upgrade Wrong Result Set with Rownum
(Doc ID 2483344.1)
Last updated on APRIL 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterInformation in this document applies to any platform.
Symptoms
- Wrong results with rownum after upgrading to 12.2
- Good plan is following:
SQL> select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cf13, child number 3
-------------------------------------
SELECT * FROM Test t WHERE ...AND ROWNUM > 0...
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | COUNT | | | | | |
| 2 | CONCATENATION | | | | | |
|* 3 | FILTER | | | | | |
| 4 | MAT_VIEW ACCESS BY INDEX ROWID| TEST | 182 | 18200 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN DESCENDING | X_TEST_3 | 182 | | 1 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | MAT_VIEW ACCESS BY INDEX ROWID| TEST | 182 | 18200 | 5 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN DESCENDING | X_TEST_3 | 182 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------- - Bad plan has SORT ORDER BY and UNION-ALL:
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | COUNT | | | | | |
| 2 | CONCATENATION | | | | | |
|* 3 | FILTER | | | | | |
| 4 | MAT_VIEW ACCESS BY INDEX ROWID| TEST | 182 | 18200 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN DESCENDING | X_TEST_3 | 182 | | 1 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | MAT_VIEW ACCESS BY INDEX ROWID| TEST | 182 | 18200 | 5 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN DESCENDING | X_TEST_3 | 182 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Changes
Upgraded to 12.2.
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 |
| Changes |
| Cause |
| Solution |
| References |