create table t as select * from all_objects
where object_id is not null
Table created.
create index ix on t ( object_id )
Index created.
explain plan for select * from t
order by object_id desc
fetch first 5 rows only
Statement processed.
select * from dbms_xplan.display()
PLAN_TABLE_OUTPUT | Plan hash value: 1088673635 | -------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 5 | 2535 | 3 (0)| 00:00:01 | | |* 1 | VIEW | | 5 | 2535 | 3 (0)| 00:00:01 | | |* 2 | WINDOW NOSORT STOPKEY | | 5 | 690 | 3 (0)| 00:00:01 | | | 3 | TABLE ACCESS BY INDEX ROWID| T | 52589 | 7087K| 3 (0)| 00:00:01 | | | 4 | INDEX FULL SCAN DESCENDING| IX | 5 | | 2 (0)| 00:00:01 | | -------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5) | 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T"."OBJECT_ID") | DESC )<=5) |
---|
explain plan for
select * from t
order by object_id desc
offset 5 rows fetch next 5 rows only
Statement processed.
select * from dbms_xplan.display()
PLAN_TABLE_OUTPUT | Plan hash value: 1088673635 | -------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 5070 | 3 (0)| 00:00:01 | | |* 1 | VIEW | | 10 | 5070 | 3 (0)| 00:00:01 | | |* 2 | WINDOW NOSORT STOPKEY | | 10 | 1380 | 3 (0)| 00:00:01 | | | 3 | TABLE ACCESS BY INDEX ROWID| T | 52589 | 7087K| 3 (0)| 00:00:01 | | | 4 | INDEX FULL SCAN DESCENDING| IX | 10 | | 2 (0)| 00:00:01 | | -------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10 AND | "from$_subquery$_002"."rowlimit_$$_rownumber">5) | 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T"."OBJECT_ID") | DESC )<=10) |
---|