Update and rownum oddity

Take a look at the following table:

SQL> create table codes
  2  (
  3     code varchar2(10),
  4     used number,
  5     constraint pk_codes primary key (used, code)
  6  ) organization index;

Table created.

SSQL> insert into codes
  2     select  dbms_random.string('x', 10),
  3             case when level <= 5000 then 1 else 0 end
  4             from dual
  5             connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'codes');

PL/SQL procedure successfully completed.

This table contains a set of codes with used column representing whether the code was already used (1) or not (0). We need to return one (random) unused code from the above table and mark this code as used. This is very easy to archive using the following update statement:

SQL> variable code varchar2(10);
SQL> set autot traceonly
SQL> update codes set used=1
  2     where used=0 and rownum=1
  3     returning code into :code;

1 row updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 1169687698

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT       |          |     1 |    13 |   117   (1)| 00:00:02 |
|   1 |  UPDATE                | CODES    |       |       |            |          |
|*  2 |   COUNT STOPKEY        |          |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| PK_CODES | 50000 |   634K|   117   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)
   3 - filter("USED"=0)


Statistics
----------------------------------------------------------
          1  recursive calls
          5  db block gets
         12  consistent gets
          0  physical reads
        124  redo size
        913  bytes sent via SQL*Net to client
        874  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)

However, the plan is not exactly what I would expect (I'm running this on 11.1.0.7)... Why do IFFS when we can do IRS to get only one row? This is exactly what regular select does, after all:

SQL> select *
  2     from codes
  3     where used=0 and rownum=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 802332609

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY    |          |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| PK_CODES |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   2 - access("USED"=0)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        590  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

10053 trace

Let's take a look at 10053 trace output for both select and update statements. I'm picking up relevant sections.

UPDATE:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: CODES  Alias: CODES
    #Rows: 100000  #Blks:  423  AvgRowLen:  13.00
Index Stats::
  Index: PK_CODES  Col#: 2 1
    LVLS: 1  #LB: 423  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 0.00
Access path analysis for CODES
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for CODES[CODES] 
  Table: CODES  Alias: CODES
    Card: Original: 100000.000000  Rounded: 50000  Computed: 50000.00  Non Adjusted: 50000.00
  Access Path: index (index (FFS))
    Index: PK_CODES
    resc_io: 116.00  resc_cpu: 20012369
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  116.81  Resp: 116.81  Degree: 1
      Cost_io: 116.00  Cost_cpu: 20012369
      Resp_io: 116.00  Resp_cpu: 20012369
OPTIMIZER PERCENT INDEX CACHING = 0


  Access Path: index (IndexOnly)
    Index: PK_CODES
    resc_io: 213.00  resc_cpu: 11516867
    ix_sel: 0.500000  ix_sel_with_filters: 0.500000 
    Cost: 213.47  Resp: 213.47  Degree: 1
  Best:: AccessPath: IndexFFS
  Index: PK_CODES
         Cost: 116.81  Degree: 1  Resp: 116.81  Card: 50000.00  Bytes: 0

***************************************

Nothing else is being tried and this is what optimizer selects as the best execution plan. Note how cardinalities are being reported (highlighted in red).

SELECT:

In addition to the above, has one more section:

***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
  Single Table Cardinality Estimation for CODES[CODES] 
  Table: CODES  Alias: CODES
    Card: Original: 2.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: index (index (FFS))
    Index: PK_CODES
    resc_io: 2.00  resc_cpu: 7461
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  2.00  Resp: 2.00  Degree: 1
      Cost_io: 2.00  Cost_cpu: 7461
      Resp_io: 2.00  Resp_cpu: 7461
OPTIMIZER PERCENT INDEX CACHING = 0


  Access Path: index (IndexOnly)
    Index: PK_CODES
    resc_io: 2.00  resc_cpu: 14443
    ix_sel: 0.500000  ix_sel_with_filters: 0.500000 
    Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: PK_CODES
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 13

First K Rows: unchanged join prefix len = 1
Join order[1]:  CODES[CODES]#0
***********************

Note how cardinalities has changed (highlighted in green) this time. What happened is rownum = 1 predicate resulted in fist_rows(1) mode (highlighted in blue), affecting how cardinalities were calculated.

We know that first_rows(n) hint is being ignored in update and delete statements, thus our update statement always goes in all_rows mode.

You can confirm that select behaves exactly the same way when in all_rows mode:

SQL> select /*+ all_rows */ *
  2     from codes
  3     where used=0 and rownum=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2682988822

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    13 |   117   (1)| 00:00:02 |
|*  1 |  COUNT STOPKEY        |          |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_CODES | 50000 |   634K|   117   (1)| 00:00:02 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   2 - filter("USED"=0)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        590  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Well, I guess here goes my next wish for CBO improvement regarding how update and delete statements are handled with predicates involving rownum...