12c row limiting clause woes

  • From: Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics@xxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 5 Sep 2016 09:27:28 +0100


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


--
http://www.freelists.org/webpage/oracle-l


Other related posts: