Dear @loiclefevre
I have the same issue on Oracle 19c having fetch first is slower than rownum filter.
I did capture the runtime execution of both queries and here is the result :
The run exection for rownum filter query :
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dquut4kms4h0j, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ t.c_airline,C_CITY_NAME,C_USERNAME
from (select c1_0.c_airline, c2_0.C_CITY_NAME,
p1_0.C_USERNAME, concat(concat(p1_0.c_first_name, ' '),
p1_0.c_last_name), concat(concat(p1_0.c_persian_first_name, '
'), p1_0.c_persian_last_name) from t_contract c1_0 left
join t_city c2_0 on c2_0.id=c1_0.f_city left
join t_profile p1_0 on p1_0.id=c1_0.f_profile
where c1_0.f_agency=11 and c1_0.b_last_version=1
order by c1_0.id desc ) t where ROWNUM <= 10
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633855969
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 32 (100)| | 10 |00:00:00.01 | 53 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 10 |00:00:00.01 | 53 |
| 2 | VIEW | | 1 | 11 | 16896 | 32 (0)| 00:00:01 | 10 |00:00:00.01 | 53 |
| 3 | NESTED LOOPS OUTER | | 1 | 11 | 1430 | 32 (0)| 00:00:01 | 10 |00:00:00.01 | 53 |
| 4 | NESTED LOOPS OUTER | | 1 | 11 | 561 | 21 (0)| 00:00:01 | 10 |00:00:00.01 | 34 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 5 | TABLE ACCESS BY INDEX ROWID| T_CONTRACT | 1 | 32330 | 1073K| 10 (0)| 00:00:01 | 10 |00:00:00.01 | 12 |
| 6 | INDEX FULL SCAN DESCENDING| SYS_C007650 | 1 | 12 | | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 7 | TABLE ACCESS BY INDEX ROWID| T_CITY | 10 | 1 | 17 | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 22 |
|* 8 | INDEX UNIQUE SCAN | SYS_C007634 | 10 | 1 | | 0 (0)| | 10 |00:00:00.01 | 12 |
| 9 | TABLE ACCESS BY INDEX ROWID | T_PROFILE | 10 | 1 | 79 | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 19 |
|* 10 | INDEX UNIQUE SCAN | SYS_C007846 | 10 | 1 | | 0 (0)| | 10 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - SEL$1
2 - SEL$49DCB01B / T@SEL$1
3 - SEL$49DCB01B
5 - SEL$49DCB01B / C1_0@SEL$2
6 - SEL$49DCB01B / C1_0@SEL$2
7 - SEL$49DCB01B / C2_0@SEL$2
8 - SEL$49DCB01B / C2_0@SEL$2
9 - SEL$49DCB01B / P1_0@SEL$3
10 - SEL$49DCB01B / P1_0@SEL$3
Outline Data
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$49DCB01B")
MERGE(@"SEL$1BEB249F" >"SEL$D2E4DCB5")
OUTLINE_LEAF(@"SEL$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OUTLINE(@"SEL$D2E4DCB5")
ANSI_REARCH(@"SEL$4")
OUTLINE(@"SEL$1BEB249F")
MERGE(@"SEL$1A566D0B" >"SEL$791DB105")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$791DB105")
ANSI_REARCH(@"SEL$F52A8B21")
OUTLINE(@"SEL$1A566D0B")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$F52A8B21")
ANSI_REARCH(@"SEL$3")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$1" "T"@"SEL$1")
INDEX_DESC(@"SEL$49DCB01B" "C1_0"@"SEL$2" ("T_CONTRACT"."ID"))
INDEX_RS_ASC(@"SEL$49DCB01B" "C2_0"@"SEL$2" ("T_CITY"."ID"))
INDEX_RS_ASC(@"SEL$49DCB01B" "P1_0"@"SEL$3" ("T_PROFILE"."ID"))
LEADING(@"SEL$49DCB01B" "C1_0"@"SEL$2" "C2_0"@"SEL$2" "P1_0"@"SEL$3")
USE_NL(@"SEL$49DCB01B" "C2_0"@"SEL$2")
USE_NL(@"SEL$49DCB01B" "P1_0"@"SEL$3")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
5 - filter(("C1_0"."F_AGENCY"=11 AND "C1_0"."B_LAST_VERSION"=1))
8 - access("C2_0"."ID"="C1_0"."F_CITY")
10 - access("P1_0"."ID"="C1_0"."F_PROFILE")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - "T"."C_AIRLINE"[VARCHAR2,1020], "C_CITY_NAME"[VARCHAR2,1020], "C_USERNAME"[VARCHAR2,1020]
2 - "T"."C_AIRLINE"[VARCHAR2,1020], "C_CITY_NAME"[VARCHAR2,1020], "C_USERNAME"[VARCHAR2,1020]
3 - "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C2_0"."C_CITY_NAME"[VARCHAR2,1020], "P1_0"."C_USERNAME"[VARCHAR2,1020]
4 - "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."F_PROFILE"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020]
5 - "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."F_CITY"[NUMBER,22], "C1_0"."F_PROFILE"[NUMBER,22]
6 - "C1_0".ROWID[ROWID,10]
7 - "C2_0"."C_CITY_NAME"[VARCHAR2,1020]
8 - "C2_0".ROWID[ROWID,10]
9 - "P1_0"."C_USERNAME"[VARCHAR2,1020]
10 - "P1_0".ROWID[ROWID,10]
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100 rows selected.
The run exection for fetch first filter query :
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID buw5ac3cjqw34, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ c1_0.c_airline,
c2_0.C_CITY_NAME, p1_0.C_USERNAME,
concat(concat(p1_0.c_first_name, ' '), p1_0.c_last_name),
concat(concat(p1_0.c_persian_first_name, ' '),
p1_0.c_persian_last_name) from t_contract c1_0 left
join t_city c2_0 on c2_0.id=c1_0.f_city left
join t_profile p1_0 on p1_0.id=c1_0.f_profile
where c1_0.f_agency=11 and c1_0.b_last_version=1
order by c1_0.id desc offset 0 rows fetch
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
first 10 rows only
Plan hash value: 1577094853
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 32 (100)| | 10 |00:00:00.01 | 53 |
|* 1 | VIEW | | 1 | 10 | 36100 | 32 (0)| 00:00:01 | 10 |00:00:00.01 | 53 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 11 | 1430 | 32 (0)| 00:00:01 | 10 |00:00:00.01 | 53 |
| 3 | NESTED LOOPS OUTER | | 1 | 11 | 1430 | 32 (0)| 00:00:01 | 10 |00:00:00.01 | 53 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 4 | NESTED LOOPS OUTER | | 1 | 11 | 561 | 21 (0)| 00:00:01 | 10 |00:00:00.01 | 34 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T_CONTRACT | 1 | 32330 | 1073K| 10 (0)| 00:00:01 | 10 |00:00:00.01 | 12 |
| 6 | INDEX FULL SCAN DESCENDING| SYS_C007650 | 1 | 12 | | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 7 | TABLE ACCESS BY INDEX ROWID| T_CITY | 10 | 1 | 17 | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 22 |
|* 8 | INDEX UNIQUE SCAN | SYS_C007634 | 10 | 1 | | 0 (0)| | 10 |00:00:00.01 | 12 |
| 9 | TABLE ACCESS BY INDEX ROWID | T_PROFILE | 10 | 1 | 79 | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 19 |
|* 10 | INDEX UNIQUE SCAN | SYS_C007846 | 10 | 1 | | 0 (0)| | 10 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - SEL$63F05DA2 / from$_subquery$_006@SEL$4
2 - SEL$63F05DA2
5 - SEL$63F05DA2 / C1_0@SEL$1
6 - SEL$63F05DA2 / C1_0@SEL$1
7 - SEL$63F05DA2 / C2_0@SEL$1
8 - SEL$63F05DA2 / C2_0@SEL$1
9 - SEL$63F05DA2 / P1_0@SEL$2
10 - SEL$63F05DA2 / P1_0@SEL$2
Outline Data
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$63F05DA2")
MERGE(@"SEL$66B15841" >"SEL$41465E65")
OUTLINE_LEAF(@"SEL$4")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OUTLINE(@"SEL$41465E65")
ANSI_REARCH(@"SEL$3")
OUTLINE(@"SEL$66B15841")
MERGE(@"SEL$8812AA4E" >"SEL$10B37F33")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$10B37F33")
ANSI_REARCH(@"SEL$948754D7")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$948754D7")
ANSI_REARCH(@"SEL$2")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
NO_ACCESS(@"SEL$4" "from$_subquery$_006"@"SEL$4")
INDEX_DESC(@"SEL$63F05DA2" "C1_0"@"SEL$1" ("T_CONTRACT"."ID"))
INDEX_RS_ASC(@"SEL$63F05DA2" "C2_0"@"SEL$1" ("T_CITY"."ID"))
INDEX_RS_ASC(@"SEL$63F05DA2" "P1_0"@"SEL$2" ("T_PROFILE"."ID"))
LEADING(@"SEL$63F05DA2" "C1_0"@"SEL$1" "C2_0"@"SEL$1" "P1_0"@"SEL$2")
USE_NL(@"SEL$63F05DA2" "C2_0"@"SEL$1")
USE_NL(@"SEL$63F05DA2" "P1_0"@"SEL$2")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_006"."rowlimit_$$_rownumber"<=10 AND "from$_subquery$_006"."rowlimit_$$_rownumber">0))
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("C1_0"."ID") DESC )<=10)
5 - filter(("C1_0"."F_AGENCY"=11 AND "C1_0"."B_LAST_VERSION"=1))
8 - access("C2_0"."ID"="C1_0"."F_CITY")
10 - access("P1_0"."ID"="C1_0"."F_PROFILE")
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
1 - "from$_subquery$_006"."C_AIRLINE"[VARCHAR2,1020], "from$_subquery$_006"."C_CITY_NAME"[VARCHAR2,1020],
"from$_subquery$_006"."C_USERNAME"[VARCHAR2,1020], "from$_subquery$_006"."CONCAT(CONCAT(P1_0.C_FIRST_NAME,''),P1_0.C_LAST_NAME)"[
VARCHAR2,2044], "from$_subquery$_006"."CONCAT(CONCAT(P1_0.C_PERSIAN_FIRST_NAME,''),P1_0.C_PERSIAN_LAST_NAME)"[VARCHAR2,2044],
"from$_subquery$_006"."rowlimit_$$_rownumber"[NUMBER,22]
2 - (#keys=1) "C1_0"."ID"[NUMBER,22], "C1_0".ROWID[ROWID,10], "P1_0"."C_USERNAME"[VARCHAR2,1020],
"C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."B_LAST_VERSION"[NUMBER,22], "C1_0"."F_AGENCY"[NUMBER,22], "C1_0"."F_CITY"[NUMBER,22],
"C1_0"."F_PROFILE"[NUMBER,22], "C2_0".ROWID[ROWID,10], "C2_0"."ID"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020],
"P1_0".ROWID[ROWID,10], "P1_0"."ID"[NUMBER,22], "P1_0"."C_FIRST_NAME"[VARCHAR2,1020], "P1_0"."C_LAST_NAME"[VARCHAR2,1020],
"P1_0"."C_PERSIAN_FIRST_NAME"[VARCHAR2,1020], "P1_0"."C_PERSIAN_LAST_NAME"[VARCHAR2,1020], ROW_NUMBER() OVER ( ORDER BY
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INTERNAL_FUNCTION("C1_0"."ID") DESC )[22]
3 - "C1_0".ROWID[ROWID,10], "C1_0"."ID"[NUMBER,22], "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."B_LAST_VERSION"[NUMBER,22],
"C1_0"."F_AGENCY"[NUMBER,22], "C1_0"."F_CITY"[NUMBER,22], "C1_0"."F_PROFILE"[NUMBER,22], "C2_0".ROWID[ROWID,10],
"C2_0"."ID"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020], "P1_0".ROWID[ROWID,10], "P1_0"."ID"[NUMBER,22],
"P1_0"."C_FIRST_NAME"[VARCHAR2,1020], "P1_0"."C_LAST_NAME"[VARCHAR2,1020], "P1_0"."C_PERSIAN_FIRST_NAME"[VARCHAR2,1020],
"P1_0"."C_PERSIAN_LAST_NAME"[VARCHAR2,1020], "P1_0"."C_USERNAME"[VARCHAR2,1020]
4 - "C1_0".ROWID[ROWID,10], "C1_0"."ID"[NUMBER,22], "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."B_LAST_VERSION"[NUMBER,22],
"C1_0"."F_AGENCY"[NUMBER,22], "C1_0"."F_CITY"[NUMBER,22], "C1_0"."F_PROFILE"[NUMBER,22], "C2_0".ROWID[ROWID,10],
"C2_0"."ID"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020]
5 - "C1_0".ROWID[ROWID,10], "C1_0"."ID"[NUMBER,22], "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."B_LAST_VERSION"[NUMBER,22],
"C1_0"."F_AGENCY"[NUMBER,22], "C1_0"."F_CITY"[NUMBER,22], "C1_0"."F_PROFILE"[NUMBER,22]
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 - "C1_0".ROWID[ROWID,10], "C1_0"."ID"[NUMBER,22]
7 - "C2_0".ROWID[ROWID,10], "C2_0"."ID"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020]
8 - "C2_0".ROWID[ROWID,10], "C2_0"."ID"[NUMBER,22]
9 - "P1_0".ROWID[ROWID,10], "P1_0"."ID"[NUMBER,22], "P1_0"."C_FIRST_NAME"[VARCHAR2,1020],
"P1_0"."C_LAST_NAME"[VARCHAR2,1020], "P1_0"."C_PERSIAN_FIRST_NAME"[VARCHAR2,1020], "P1_0"."C_PERSIAN_LAST_NAME"[VARCHAR2,1020],
"P1_0"."C_USERNAME"[VARCHAR2,1020]
10 - "P1_0".ROWID[ROWID,10], "P1_0"."ID"[NUMBER,22]
118 rows selected.
The _optimizer_cbqt_or_expansion was on in the session as you suggested.
_Hamid