12c row limiting clause woes
From: Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk>
Date: Mon, 5 Sep 2016 09:27:28 +0100
Message-Id: <F55F0314-ACF4-4EDD-B86C-C8EA1B57FF9C_at_strychnine.co.uk>
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO', cascade => true); PL/SQL procedure successfully completed.
2 select a from foo order by a asc offset 5 rows fetch next 5 rows only; Explained.
SQL>
SQL> spool off
Date: Mon, 5 Sep 2016 09:27:28 +0100
Message-Id: <F55F0314-ACF4-4EDD-B86C-C8EA1B57FF9C_at_strychnine.co.uk>
So, to sum this up, and please let me know if I've missed anything
- not 'estimating' 5 rows returned, but 999K
- no use of the index or even solely just scanning the index, but instead a full table scan
- swap out to tmpspc
- 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_OUTPUT18 rows selected.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)
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
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Sep 05 2016 - 10:27:28 CEST