So, to sum this up, and please let me know if I've missed anything
a) not 'estimating' 5 rows returned, but 999K
b) no use of the index or even solely just scanning the index, but instead a
full table scan
c) swap out to tmpspc
d) a stupid cost
~
Mike
SQL>
SQL>
SQL>
SQL> create table foo as
2 select rownum a
3 from dual
4 connect by rownum<1000000;
Table created.
SQL>
SQL>
SQL> create unique index idxfoo on foo(a);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO',
cascade => true);
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> explain plan for
2 select a from foo order by a asc offset 5 rows fetch next 5 rows only;
Explained.
SQL>
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1308456731
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999K| 37M| | 3465 (3)|
00:00:01 |
|* 1 | VIEW | | 999K| 37M| | 3465 (3)|
00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 999K| 4882K| 11M| 3465 (3)|
00:00:01 |
| 3 | TABLE ACCESS FULL | FOO | 999K| 4882K| | 357 (5)|
00:00:01 |
-----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE WHEN (5>=0)
THEN 5 ELSE 0 END +5 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "A")<=CASE WHEN (5>=0) THEN 5 ELSE
0
END +5)
18 rows selected.
SQL>
SQL>
SQL> select * from product_component_version;
PRODUCT VERSION STATUS
---------------------------------------- ---------------
--------------------------------------------------------------------------------
NLSRTL 12.1.0.2.0 Production
Oracle Database 12c Enterprise Edition 12.1.0.2.0 64bit Production
PL/SQL 12.1.0.2.0 Production
TNS for Solaris: 12.1.0.2.0 Production
SQL>
SQL>
SQL> spool off
--
//www.freelists.org/webpage/oracle-l
