execution plan TABLE ACCESS BY INDEX ROWID cardinality
An ancient "issue" that is still with us, even in 18.4

SQL&gt; select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 -
Production
Version 18.4.0.0.0

SQL&gt; create table t1 as select * from dba_objects where object_id is not null;

Table created.

SQL&gt; alter table t1 add constraint t1_pk primary key (object_id);

Table altered.

SQL&gt; exec dbms_stats.gather_table_stats(user, 'T1', cascade=&gt;true);

PL/SQL procedure successfully completed.

SQL&gt; explain plan for select * from (select * from t1 order by object_id) where rownum &lt;= 5;

Explained.

SQL&gt; select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3766500789

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2405 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |       |       |       |            |          |
|   2 |   VIEW                        |       |     5 |  2405 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|     3   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK |     5 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM&lt;=5)

16 rows selected.


The above is kind of explainable, but this is where the numbers get strange (3670 comes from num_rows/num_distinct):

SQL&gt; explain plan for select * from (select * from t1 where owner = 'SYS' order by object_id) where rownum &lt;= 5;

Explained.

SQL&gt; select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3766500789

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2405 |     5   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |       |       |       |            |          |
|   2 |   VIEW                        |       |     6 |  2886 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |  3670 |   447K|     5   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK |   102 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM&lt;=5)
   3 - filter("OWNER"='SYS')

17 rows selected.


rownum implicitly triggers some first_rows_N optimization that messes up the cardinality (this was discussed on Oracle-l, 12 years ago: https://www.freelists.org/post/oracle-l/ORDER-BY-and-first-rows-10-madness,4). One workaround is to run it with all_rows hint (yes, even if it is the current setting in the session), also I had to place the hint for the index, otherwise the database would have chosen FTS in this example:

SQL&gt; explain plan for select /*+ all_rows */ * from (select /*+ index(t1 t1_pk) */ * from t1 order by object_id) where rownum &lt;= 5;

Explained.

SQL&gt; select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3766500789

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2405 |  1459   (1)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |       |       |       |            |          |
|   2 |   VIEW                        |       | 62395 |    28M|  1459   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK | 62395 |       |   131   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM&lt;=5)

16 rows selected.


Much better. Another reason to use analytic functions or the 12c+ syntax instead of rownum. Analytic function:

SQL&gt; explain plan for select * from (select t1.*, row_number() over (order by object_id) as rn from t1) where rn &lt;= 5;

Explained.

SQL&gt; select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3641836603

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2470 |  1459   (1)| 00:00:01 |
|*  1 |  VIEW                         |       |     5 |  2470 |  1459   (1)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |       | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK | 62395 |       |   131   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"&lt;=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")&lt;=5)

17 rows selected.


Or 12c+ syntax:

SQL&gt; explain plan for select * from t1 order by object_id fetch first 5 rows only;

Explained.

SQL&gt; select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3641836603

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2535 |  1459   (1)| 00:00:01 |
|*  1 |  VIEW                         |       |     5 |  2535 |  1459   (1)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |       | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK | 62395 |       |   131   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"&lt;=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "T1"."OBJECT_ID")&lt;=5)

17 rows selected.