Oracle - Windows Sort Pushed Rank access

Dopo una attenta lettura di un'ottimo post nel blog di Cristian, mi sono incuriosito per uno strano comportamento di un explain plan di un test, per la funzione, WINDOW SORT PUSHED RANK in Oracle 12c.
Seguendo un articolo di Tom Kyte dove veniva illustrato la funzione fetch first X rows (only top n query) e cercando di riprodurre lo stesso comportamento nella nostri Oracle 12c ci siamo imbattuti in qualche stranezza.

create table t2 as select * from all_objects;

create index idx_t2 on t2(owner,object_name);

SET AUTOTRACE ON
select  owner,object_name,object_id from t2
order by owner,object_name fetch first 5 rows only;

Autotrace Enabled
Shows the execution plan as well as statistics of the statement.


OWNER           OBJECT_NAME                     OBJECT_ID
APEX_040200 APEX                         88901
APEX_040200 APEX$ARCHIVE_CONTENTS         89728
APEX_040200 APEX$ARCHIVE_CONTENTS_IDX1 89732
APEX_040200 APEX$ARCHIVE_HEADER         89726
APEX_040200 APEX$ARCHIVE_HISTORY         89733

Plan hash value: 3975347511
 
-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      | 86477 |    23M|       |  3191   (1)| 00:00:01 |
|*  1 |  VIEW                    |      | 86477 |    23M|       |  3191   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      | 86477 |    11M|    13M|  3191   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T2   | 86477 |    11M|       |   412   (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 "OWNER","OBJECT_NAME")<=5)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

   Statistics
-----------------------------------------------------------
               2  CCursor + sql area evicted
              83  CPU used by this session
              83  CPU used when call started
              87  DB time
              25  Number of read IOs issued
              88  Requests to/from client
              88  SQL*Net roundtrips to/from client
              63  buffer is not pinned count
            1104  bytes received via SQL*Net from client
           35629  bytes sent via SQL*Net to client
               8  calls to get snapshot scn: kcmgss
              13  calls to kcmgcs
        15081472  cell physical IO interconnect bytes
            1585  consistent gets
            1510  consistent gets direct
               2  consistent gets examination
               2  consistent gets examination (fastpath)
              75  consistent gets from cache
              73  consistent gets pin
              22  consistent gets pin (fastpath)
               2  enqueue releases
               2  enqueue requests
               6  execute count
            1074  file io wait time
             331  free buffer requested
               2  index scans kdiixs1
          614400  logical read bytes from cache
            1573  no work - consistent read gets
             164  non-idle wait count
               1  non-idle wait time
               6  opened cursors cumulative
               2  opened cursors current
               2  parse count (hard)
               4  parse count (total)
               1  parse time cpu
               2  parse time elapsed
              76  physical read IO requests
        15081472  physical read bytes
              76  physical read total IO requests
        15081472  physical read total bytes
              25  physical read total multi block requests
            1841  physical reads
             331  physical reads cache
             280  physical reads cache prefetch
            1510  physical reads direct
               7  recursive calls
               1  recursive cpu usage
              -1  session cursor cache count
               3  session cursor cache hits
            1585  session logical reads
              51  shared hash latch upgrades - no wait
               3  sorts (memory)
           90762  sorts (rows)
            1571  table scan blocks gotten
           93514  table scan rows gotten
               1  table scans (direct read)
               2  table scans (short tables)
              89  user calls
               5  workarea executions - optimal

Ci siamo chiesti come mai nell'esempio proposto venisse utilizzato l'indice appositamente creato mentre nel nostro no. Nemmeno aggiornando le statistiche la situazione non cambiava. Sono andato a verificare in v$parameter il parametro dell'optimizer e l'ho trovato ALL_ROWS la cosa mi ha subito insospettito quindi ho provato con

alter session set OPTIMIZER_MODE = FIRST_ROWS

SET AUTOTRACE ON
select  owner,object_name,object_id from t2
order by owner,object_name fetch first 5 rows only; 


Autotrace Enabled
Shows the execution plan as well as statistics of the statement.

OWNER           OBJECT_NAME                     OBJECT_ID
APEX_040200 APEX                         88901
APEX_040200 APEX$ARCHIVE_CONTENTS         89728
APEX_040200 APEX$ARCHIVE_CONTENTS_IDX1 89732
APEX_040200 APEX$ARCHIVE_HEADER         89726
APEX_040200 APEX$ARCHIVE_HISTORY         89733

Plan hash value: 2588503356
 
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        | 89906 |    24M| 69551   (1)| 00:00:03 |
|*  1 |  VIEW                         |        | 89906 |    24M| 69551   (1)| 00:00:03 |
|*  2 |   WINDOW NOSORT STOPKEY       |        | 89906 |  3160K| 69551   (1)| 00:00:03 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2     | 89906 |  3160K| 69551   (1)| 00:00:03 |
|   4 |     INDEX FULL SCAN           | IDX_T2 | 89906 |       |   524   (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 "OWNER","OBJECT_NAME")<=5)

   Statistics
-----------------------------------------------------------
              19  Requests to/from client
              19  SQL*Net roundtrips to/from client
               4  buffer is not pinned count
               9  buffer is pinned count
             463  bytes received via SQL*Net from client
           32492  bytes sent via SQL*Net to client
               2  calls to get snapshot scn: kcmgss
               2  calls to kcmgcs
               5  consistent gets
               2  consistent gets examination
               2  consistent gets examination (fastpath)
               5  consistent gets from cache
               3  consistent gets pin
               3  consistent gets pin (fastpath)
               1  cursor authentications
               2  execute count
               1  index scans kdiixs1
           40960  logical read bytes from cache
               3  no work - consistent read gets
              19  non-idle wait count
               2  opened cursors cumulative
               1  opened cursors current
               2  parse count (total)
               5  session logical reads
               1  sorts (memory)
             855  sorts (rows)
               6  table fetch by rowid
              20  user calls
               3  workarea executions - optimal

Adesso l'indice viene utilizzato correttamente e il piano di esecuzione è migliorato sensibilmente. Presumo che nell'esempio proposto da Tom Kyte questo parametro fosse impostato con appunto FIRST_ROWS.