A tuning book says window functions and the 12c row limiting clause often fail to trigger Top‑N Stopkey even with a
perfect index, so I tried to reproduce that behavior and saw the same thing—ROWNUM uses stopkey, but ROW_NUMBER() and
FETCH FIRST don’t. Here’s the test case and the queries.
“I tagged this as 12c since row limiting was introduced in 12c, but the reproduction was done on FreeSQL (23c). Please note if behavior differs by version.”
Test case (all objects and data):
CREATE TABLE EQUIPMENT (
EQUIP_ID NUMBER NOT NULL,
EQUIP_NAME VARCHAR2(100) NOT NULL,
EQUIP_TYPE_CODE VARCHAR2(10) NOT NULL,
CONSTRAINT PK_EQUIPMENT PRIMARY KEY (EQUIP_ID)
);
CREATE TABLE STATUS_CHANGE_HISTORY (
EQUIP_ID NUMBER NOT NULL,
CHANGE_DATE CHAR(8) NOT NULL,
CHANGE_SEQ NUMBER(4) NOT NULL,
STATUS_CODE VARCHAR2(10) NOT NULL,
CONSTRAINT PK_STATUS_CHANGE_HISTORY
PRIMARY KEY (EQUIP_ID, CHANGE_DATE, CHANGE_SEQ)
USING INDEX
);
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX X_STATUS_CHANGE_HISTORY_01';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE INDEX X_STATUS_CHANGE_HISTORY_01
ON STATUS_CHANGE_HISTORY (EQUIP_ID, CHANGE_DATE DESC, CHANGE_SEQ DESC);
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX X_EQUIPMENT_01';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE INDEX X_EQUIPMENT_01 ON EQUIPMENT (EQUIP_TYPE_CODE, EQUIP_ID);
INSERT INTO EQUIPMENT (EQUIP_ID, EQUIP_NAME, EQUIP_TYPE_CODE)
SELECT 100000 + LEVEL,
'EQUIP-' || (100000 + LEVEL),
CASE WHEN LEVEL <= 800 THEN 'A001' ELSE 'B001' END
FROM dual CONNECT BY LEVEL <= 1000;
INSERT INTO STATUS_CHANGE_HISTORY (EQUIP_ID, CHANGE_DATE, CHANGE_SEQ, STATUS_CODE)
SELECT p.EQUIP_ID,
TO_CHAR(DATE '2024-01-01' + FLOOR((h.lv - 1) / 5), 'YYYYMMDD'),
MOD(h.lv - 1, 5) + 1,
'S' || LPAD(MOD(h.lv - 1, 90) + 10, 2, '0')
FROM EQUIPMENT p
CROSS JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 20) h;
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER, 'EQUIPMENT', CASCADE => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(USER, 'STATUS_CHANGE_HISTORY', CASCADE => TRUE);
END;
/
SELECT COUNT(*) AS EQUIPMENT_CNT FROM EQUIPMENT;
SELECT COUNT(*) AS HISTORY_CNT FROM STATUS_CHANGE_HISTORY;
SELECT index_name, column_name, descend
FROM user_ind_columns
WHERE index_name = 'X_STATUS_CHANGE_HISTORY_01'
ORDER BY column_position;
Queries:
1. Classic Top‑N with ROWNUM (gets COUNT STOPKEY, no sort):
SELECT equip_id, equip_name,
SUBSTR(last_hist, 1, 8) AS last_change_date,
TO_NUMBER(SUBSTR(last_hist, 9, 4)) AS last_change_seq,
SUBSTR(last_hist, 13) AS last_status_code
FROM (
SELECT equip_id, equip_name,
(SELECT change_date || LPAD(change_seq, 4) || status_code
FROM (
SELECT change_date, change_seq, status_code
FROM status_change_history
WHERE equip_id = p.equip_id
ORDER BY change_date DESC, change_seq DESC
)
WHERE ROWNUM <= 1) AS last_hist
FROM equipment p
WHERE equip_type_code = 'A001'
);
execution plan :
https://ibb.co/3Y7ptyR4 SELECT equip_id, equip_name,
SUBSTR(last_hist, 1, 8) AS last_change_date,
TO_NUMBER(SUBSTR(last_hist, 9, 4)) AS last_change_seq,
SUBSTR(last_hist, 13) AS last_status_code
FROM (
SELECT equip_id, equip_name,
(SELECT change_date || LPAD(change_seq, 4) || status_code
FROM (
SELECT change_date, change_seq, status_code,
ROW_NUMBER() OVER (ORDER BY change_date DESC, change_seq DESC) AS rn
FROM status_change_history
WHERE equip_id = p.equip_id
)
WHERE rn = 1) AS last_hist
FROM equipment p
WHERE equip_type_code = 'A001'
);
execution plan :
https://ibb.co/svM1LrfX 3. Row limiting clause (optimizer rewrites to window function; same plan as #2):
SELECT equip_id, equip_name,
SUBSTR(last_hist, 1, 8) AS last_change_date,
TO_NUMBER(SUBSTR(last_hist, 9, 4)) AS last_change_seq,
SUBSTR(last_hist, 13) AS last_status_code
FROM (
SELECT equip_id, equip_name,
(SELECT change_date || LPAD(change_seq, 4) || status_code
FROM status_change_history
WHERE equip_id = p.equip_id
ORDER BY change_date DESC, change_seq DESC
FETCH FIRST 1 ROWS ONLY) AS last_hist
FROM equipment p
WHERE equip_type_code = 'A001'
);
execution plan:
https://ibb.co/v6L4LJf6 Questions:
- Why can the optimizer apply Top‑N Stopkey with ROWNUM in this correlated subquery, but not with ROW_NUMBER() or
FETCH FIRST 1 ROWS ONLY, even though the index order matches the sort?
- Are there specific constraints that block stopkey when the Top‑N is expressed as a window function or row limiting
clause in a correlated subquery?
- Is there a hint or rewrite pattern that would let ROW_NUMBER() or FETCH FIRST avoid the sort and use the descending
index, or is the classic ROWNUM pattern the only reliable option here?
https://freesql.com/?compressed_code=H4sIAAAAAAAACu1XzXKjRhC%252BU8U79A1Yg40Ub%252BK14lQhGFvEgtHCyLZOFBFURK2EWEDruGrPeYA8Yp4kNcOvZCy7vHKSw44vZqa7p%252Fvrr3taigLp%252Bj7erM4hy9fJp%252FABFn6ShHEY8JyLxkgnEH7eRIkXBTLPQbGKndhfhc2eOx26xBGXfpZ7iyjLZejJcCYBaC6wzfnCj38PvcDPW1oEe%252FbUGiJHfKz%252FQYZTSdrVz8LPey%252F9QaIHlVKW%252B%252Fkm8%252BbrIOS5SwdbIFLlJyPrjg0KrXqV6q2I4OtXGE80Q2x5CacS3d5yoWWk8WavZRnaJp8yVpqrjgsNisg6fdgRvB0hB9WhwwUkx9XHjiR2DOTAcLYVqIFcve0T29hSlLa%252BiuscfGtPLfj5Anqt0ya51FV6wKJg7qzCOIeE7rUdzh%252BSkEUPFyBoqtoTeE4a8JzCeOzFm9VvYQrncB%252FFwfoekk22CANI%252FfjTd17%252Fz3i9I1GuLjS2VuvaJyQcfFuBLwG%252BQc4jb%252Br1SobXi2Uxjd%252B6FLsqKo3hbYtpGa2i%252FHsp%252Feel9DyRXkqjV7L9EhF9BJem4xLo0epyAdvj2ZtQj%252F4pCmTzRbgKeU53kEYQEG04RoA%252BTs2JhWxSQc02PNNoXC2IUn9hAvZ0PJbb4rZmoUrgRnP0keb0xZ6qSk%252FIk9kEeTo20La89Ni%252Bjm2XOJppE5hce423E8e0NGcG12gGYuWzVMW7FaJLNDJ1PX2k2VfIG5kuwc6sO9y9oZYGDGqZfTnimbRX1EUfG6viqdQlWnnXBcZeLDrDqvjTCY%252FcDkFuOVm3wqlr2ldg2ga6q6A8eQcXr13M7KkERrpOYHINURyEf8Dff%252F4FaThPQ1op91G%252BYNUB6zQIU6bx6vvg3QnPDdGVaVM76A7pU4LAtCxkmDRrguHgSREe3HUD6Kk9YcBz6E5HE2JimxaYDZiMkOMCof%252FThFAJ2xjw3EmLbc%252FZ5Tlsv4yMHYRjHeUxuYq%252BcqBMvZdAC4Ioj9axvyyS9W%252Fmoy7uA%252BSgbYvh3upzOy1IrmE%252FEIw%252FSmDGWZjmEPi5X5WW0nLhHHqyqqp0IMia825qnENf3Rb%252BtnTwnGm7yCFg2gQ%252FhoV2iaany7v9Wqqnk55KFxzBGN2gcfNmC0xDEegTLG4LSY2UrrmoSCs7ob9hzlS1SG7xdAEauwiEYfG%252FbZRjRLDxlzynY9umTgxnjT69a7Ab35NN8vmmKLdbcxN3crxTpcU0xZ6D1uvNilboq%252F1TRe0pNIYjuBxj7Iji4nj5BRToSXAC76X2tCPMZrOZZRmGUG%252B2zi1s1LoyLdYj6LVwd4V6GNqW%252FKAyUVWGvgyCKkgllE3uE1o%252F2HXhV2zaIJaRFsguv0ANPHTh3lclWBSvLrYskxymiH6S4MrPF2HKRrUoy6P5IdhfNyNjaLmsU7vHVxrtLB4bFootceoiRy6pTBESZMpYXTMQXPwCxJki2iteaKWTg90WWw3tmxE8k%252BDGX0aBT%252Fv5AaArWaHjqU3Ed2w6bbqsbhPYJtWgU6N6DWv5TnAYhUpt9gwVvwtgvl5uVnH5EYTZPIyDksybLEy9KA68QibjuWIybtTpULzn0ee5ZpIvrknWGXsLB4qCsP4PTSidz9UTAAA%253D&code_language=PL_SQL&db_version=23&code_format=false
The COUNT STOPKEY optimization for FETCH FIRST is available in 26ai. Running on 23.26.0, I get this plan for FETCH FIRST:
set feed only
SELECT equip_id, equip_name,
SUBSTR(last_hist, 1, 8) AS last_change_date,
TO_NUMBER(SUBSTR(last_hist, 9, 4)) AS last_change_seq,
SUBSTR(last_hist, 13) AS last_status_code
FROM (
SELECT equip_id, equip_name,
(SELECT change_date || LPAD(change_seq, 4) || status_code
FROM status_change_history
WHERE equip_id = p.equip_id
ORDER BY change_date DESC, change_seq DESC
FETCH FIRST 1 ROWS ONLY) AS last_hist
FROM equipment p
WHERE equip_type_code = 'A001'
);
set feed on
select * from dbms_xplan.display_cursor ( format => 'ALLSTATS LAST');
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 800 |00:00:00.01 | 14 |
|* 1 | COUNT STOPKEY | | 800 | | 800 |00:00:00.01 | 2304 |
| 2 | VIEW | | 800 | 2 | 800 |00:00:00.01 | 2304 |
| 3 | TABLE ACCESS BY INDEX ROWID | STATUS_CHANGE_HISTORY | 800 | 20 | 800 |00:00:00.01 | 2304 |
|* 4 | INDEX RANGE SCAN DESCENDING| PK_STATUS_CHANGE_HISTORY | 800 | 2 | 800 |00:00:00.01 | 1504 |
|* 5 | TABLE ACCESS FULL | EQUIPMENT | 1 | 500 | 800 |00:00:00.01 | 14 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
4 - access("EQUIP_ID"=:B1)
5 - filter("EQUIP_TYPE_CODE"='A001')Using ROW_NUMBER () still uses a PUSHED RANK rather than COUNT STOPKEY in this release. This is just a matter of this optimization not being applied.
If you want the STOPKEY optimization, use FETCH FIRST (26ai on) or ROWNUM in earlier releases.