This is the explain plan for a certain query. The query contains a PK order by clauses and matches on two other columns on the where clause.
Since PK is used for index full scan, I assume cardinality to be accurate. So I expect for the table access to have the same cardinality: PK values are unique, so there should be exactly 168 unique ROWIDs in index, pointing to exactly 168 rows, not 5633.
In which circumstances can TABLE ACCESS BY INDEX ROWID cardinality exceed that of INDEX FULL SCAN?
An ancient "issue" that is still with us, even in 18.4
SQL> 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> create table t1 as select * from dba_objects where object_id is not null;
Table created.
SQL> alter table t1 add constraint t1_pk primary key (object_id);
Table altered.
SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true);
PL/SQL procedure successfully completed.
SQL> explain plan for select * from (select * from t1 order by object_id) where rownum <= 5;
Explained.
SQL> 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<=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> explain plan for select * from (select * from t1 where owner = 'SYS' order by object_id) where rownum <= 5;
Explained.
SQL> 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<=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> explain plan for select /*+ all_rows */ * from (select /*+ index(t1 t1_pk) */ * from t1 order by object_id) where rownum <= 5;
Explained.
SQL> 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<=5)
16 rows selected.
Much better. Another reason to use analytic functions or the 12c+ syntax instead of rownum. Analytic function:
SQL> explain plan for select * from (select t1.*, row_number() over (order by object_id) as rn from t1) where rn <= 5;
Explained.
SQL> 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"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=5)
17 rows selected.
Or 12c+ syntax:
SQL> explain plan for select * from t1 order by object_id fetch first 5 rows only;
Explained.
SQL> 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"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "T1"."OBJECT_ID")<=5)
17 rows selected.
