Hi,
Please refer to the linked LiveSQL for the table, index, and data definition.
I execute a query that uses ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC). When a partitioned index on SOME_DATE DESC is used, the result contains wrong row numbers. The order of rows is correct only inside each partition, but partitions are in the wrong order.
SELECT
ID,
TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'),
ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num
FROM
TEST_TABLE
WHERE
SOME_GROUP = 1
AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS');
Gives me wrong ROW_NUM:
ID TO_CHAR(SOME_DATE,' ROW_NUM
---------- ------------------- ----------
3 2023-11-06 03:00:00 1
2 2023-11-06 02:00:00 2
1 2023-11-06 01:00:00 3
6 2023-11-07 03:00:00 4
5 2023-11-07 02:00:00 5
4 2023-11-07 01:00:00 6
10 2023-11-08 04:00:00 7
9 2023-11-08 03:00:00 8
8 2023-11-08 02:00:00 9
7 2023-11-08 01:00:00 10
If I force a full table scan instead of an index scan with /*+ FULL() */ hint, the result is correct:
SELECT /*+ FULL(TEST_TABLE) */
ID,
TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'),
ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num
FROM
TEST_TABLE
WHERE
SOME_GROUP = 1
AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS');
Gives me:
ID TO_CHAR(SOME_DATE,' ROW_NUM
---------- ------------------- ----------
10 2023-11-08 04:00:00 1
9 2023-11-08 03:00:00 2
8 2023-11-08 02:00:00 3
7 2023-11-08 01:00:00 4
6 2023-11-07 03:00:00 5
5 2023-11-07 02:00:00 6
4 2023-11-07 01:00:00 7
3 2023-11-06 03:00:00 8
2 2023-11-06 02:00:00 9
1 2023-11-06 01:00:00 10
Yup, that's a bug. Replicated on my 21c instance.
SQL> set autotrace on explain
SQL> SELECT
2 ID,
3 TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'),
4 ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num
5 FROM
6 TEST_TABLE
7 WHERE
8 SOME_GROUP = 1
9 AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS');
ID TO_CHAR(SOME_DATE,' ROW_NUM
---------- ------------------- ----------
3 2023-11-06 03:00:00 1
2 2023-11-06 02:00:00 2
1 2023-11-06 01:00:00 3
6 2023-11-07 03:00:00 4
5 2023-11-07 02:00:00 5
4 2023-11-07 01:00:00 6
10 2023-11-08 04:00:00 7
9 2023-11-08 03:00:00 8
8 2023-11-08 02:00:00 9
7 2023-11-08 01:00:00 10
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1537971951
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 0 (0)| 00:00:01 | | |
| 1 | WINDOW NOSORT | | 1 | 35 | 0 (0)| 00:00:01 | | |
| 2 | PARTITION RANGE ITERATOR | | 1 | 35 | 0 (0)| 00:00:01 | 1 | 8714 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_TABLE | 1 | 35 | 0 (0)| 00:00:01 | 1 | 8714 |
|* 4 | INDEX RANGE SCAN | I_TEST_TABLE_DATE | 1 | | 0 (0)| 00:00:01 | 1 | 8714 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SOME_GROUP"=1 AND SYS_OP_DESCEND("SOME_DATE")>HEXTORAW('8784F4F7EFFEF8FF') AND
SYS_OP_DESCEND("SOME_DATE") IS NOT NULL)
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SOME_DATE"))<TO_DATE(' 2023-11-08 15:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL>
SQL> SELECT /*+ FULL(TEST_TABLE) */
2 ID,
3 TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'),
4 ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num
5 FROM
6 TEST_TABLE
7 WHERE
8 SOME_GROUP = 1
9 AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS');
ID TO_CHAR(SOME_DATE,' ROW_NUM
---------- ------------------- ----------
10 2023-11-08 04:00:00 1
9 2023-11-08 03:00:00 2
8 2023-11-08 02:00:00 3
7 2023-11-08 01:00:00 4
6 2023-11-07 03:00:00 5
5 2023-11-07 02:00:00 6
4 2023-11-07 01:00:00 7
3 2023-11-06 03:00:00 8
2 2023-11-06 02:00:00 9
1 2023-11-06 01:00:00 10
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 655623222
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 821 (1)| 00:00:01 | | |
| 1 | WINDOW SORT | | 1 | 35 | 821 (1)| 00:00:01 | | |
| 2 | PARTITION RANGE ITERATOR| | 1 | 35 | 820 (1)| 00:00:01 | 1 | 8714 |
|* 3 | TABLE ACCESS FULL | TEST_TABLE | 1 | 35 | 820 (1)| 00:00:01 | 1 | 8714 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SOME_GROUP"=1 AND "SOME_DATE"<TO_DATE(' 2023-11-08 15:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
My guess is that there is an optimization to go in descending fashion to avoid the sort, but whilst we are doing that for the index entries, we are not going through the partitions in descending order.
I'll pass this along but can I suggest you log an SR with this example and they can log a bug.
(bugs reported by customers get a lot more traction internally than bugs logged by me).
Thanks for the awesome test case - please reference the LiveSQL link in the SR as well