FIRST_ROWS vs. ALL_ROWS and ROWNUM predicate

A quite famous problem(especially on 9i-) on FIRST_ROWS vs. ALL_ROWS:

1. { Select … } query has an execution plan which performs index range scan.
2. But when same { Select … } is used as a part of DML statements like { Insert .. Select }, Oracle simply ignores to use effective index.

This was often due to the fact that Oracle always uses ALL_ROWS mode internally for DML statement. This means that even when you choose to use FIRST_ROWS mode on system level, your DML statements stick to ALL_ROWS mode.

This also means that when optimizer mode is ALL_ROWS, there should be no diffference between select statement and DML statement. This is a natural assumption, isn’t it?

But let’s see following non-intuitive result.

UKJA@ukja102> select * from v$version;
BANNER
--------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

UKJA@ukja102> show parameter optimizer_mode
NAME                     TYPE   VALUE
------------------------ ------ -------
optimizer_mode           string ALL_ROWS

UKJA@ukja102> create table t1 (
2    c1  varchar2(10),
3    c2  number,
4    c3  number,
5    constraint t1_pk primary key (c2, c1)
6  );

Table created.

UKJA@ukja102>

UKJA@ukja102> insert into t1
2  select
3    dbms_random.string('x', 10),
4    case when level <= 5000 then 1 else 0 end,
5    level
6  from dual
7  connect by level <= 10000
8  ;

10000 rows created.

UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't1');

PL/SQL procedure successfully completed.

UKJA@ukja102> -- default
UKJA@ukja102> explain plan for
2  select *
3  from t1
4  where c2 = 0 and rownum = 1
5  ;

Explained.

UKJA@ukja102> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    17 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    17 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_PK |       |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter(ROWNUM=1)
3 - access("C2"=0)

16 rows selected.

UKJA@ukja102> explain plan for
2  update t1 set c2 = 1
3  where c2 = 0 and rownum = 1
4  ;

Explained.

UKJA@ukja102> select * from table(dbms_xplan.display);
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     1 |    14 |    11   (0)| 00:00:01 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T1   |  5000 | 70000 |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------
 Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(ROWNUM=1)
3 - filter("C2"=0)
 
16 rows selected.

See that even under ALL_ROWS mode, the select statement and update statement generate different execution plans? What’s happening here?

This is due to the fact that Oracle converts ROWNUM predicate to first_rows mode internally. This behavior is controlled by _optimizer_rownum_pred_based_fkr hidden parameter, whose default value is true.

This means that even under ALL_ROWS mode, Oracle tries to stick to FIRST_ROWS mode when ROWNUM predicate being used.

Following test case clearly shows that with _optimizer_rownum_pred_based_fkr being false, select statement generates same execution plan with update statement.

 UKJA@ukja102> explain plan for
2  select /*+ opt_param('_optimizer_rownum_pred_based_fkr', 'false') */ *
3  from t1
4  where c2 = 0 and rownum = 1
5  ;

Explained.

UKJA@ukja102> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |    11   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  5000 | 85000 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------
 Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter(ROWNUM=1)
2 - filter("C2"=0)
 
15 rows selected.

Quite annoying, but this is how Oracle impoves the CBO.

The last question is how to make update statement choose efficient index range scan other than inefficient table full scan? Maybe the only reasonable solution is to use appropriate hints.

 UKJA@ukja102> explain plan for
2  update /*+ index(t1) */ t1 set c2 = 1
3  where c2 = 0 and rownum = 1
4  ;

Explained.

UKJA@ukja102> select * from table(dbms_xplan.display);
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |       |     1 |    14 |    23   (0)| 00:00:01 |
|   1 |  UPDATE            | T1    |       |       |            |          |
|*  2 |   COUNT STOPKEY    |       |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| T1_PK |  5000 | 70000 |    23   (0)| 00:00:01 |
----------------------------------------------------------------------------
 Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(ROWNUM=1)
3 - access("C2"=0)
 
16 rows selected.

I know that hints are ugly things, but there are situations when hints are the only applicable solution. This is a good example.