ORDER BY and FIRST_ROWS

Recently an interesting question came up on a French oracle forum about a costly order by operation. The original poster said that he has a query like this:

select *
from t1 where id in (select id from t2 where x1 = :b1) ;

which is returning 39 records in a very acceptable response time. However, when he adds a simple order by to that query, the response time become very nasty and not acceptable at all. When I asked about the value of the optimizer mode he is using, I was not really surprised when he answered “I am running under FIRST_ROWS mode”.   The aim of this blog is exactly to explain the nasty side effect the FIRST_ROWS optimizer can produce particularly in presence of an order by clause.

The model (borrowed from Jonathan Lewis) is:

mhouri.world> create table t1
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(1,1000))    n1,
 12      lpad(rownum,10,'0') small_vc,
 13      rpad('x',100)       padding
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;
Table created.

mhouri.world> create index t1_n1 on t1(id, n1);
Index created.

mhouri.world> create table t2
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(10001,20001))   x1,
 12      lpad(rownum,10,'0') small_vc,
 13      rpad('x',100)       padding
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;
Table created.

mhouri.world> create index t2_i1 on t2(x1);
Index created.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

mhouri.world> show parameter optimiz%
NAME                                 TYPE        VALUE                                                                 
------------------------------------ ----------- ------------------------                
optimizer_mode                       string      ALL_ROWS                  
                                             

mhouri.world> select *
  2  from t1 where id in (select id from t2 where x1 = 17335)
  3  order by id
  4  ;

107 rows selected.

mhouri.world> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT                                                                                                      
-----------------------------------------------------------------------------------------
SQL_ID  d2wym19g2n196, child number 1                                                                                  
-------------------------------------                                                                                  
select * from t1 where id in (select id from t2 where x1 = 17335) order by id                                          
Plan hash value: 153456901                                                                                             
-----------------------------------------------------------------------------------------                              
| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                              
-----------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT                |       |       |       |   255 (100)|          |                              
|   1 |  SORT ORDER BY                  |       |   100 | 13000 |   255   (1)| 00:00:02 |                              
|   2 |   TABLE ACCESS BY INDEX ROWID   | T1    |     1 |   120 |     3   (0)| 00:00:01 |                              
|   3 |    NESTED LOOPS                 |       |   100 | 13000 |   254   (1)| 00:00:02 |                              
|   4 |     SORT UNIQUE                 |       |   100 |  1000 |   103   (0)| 00:00:01 |                              
|   5 |      TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   103   (0)| 00:00:01 |                              
|*  6 |       INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |                              
|*  7 |     INDEX RANGE SCAN            | T1_N1 |     1 |       |     2   (0)| 0:00:01                                 
-----------------------------------------------------------------------------------------                              
Predicate Information (identified by operation id):                                                                    
---------------------------------------------------                                                                    
   6 - access("X1"=17335)                                                                                              
   7 - access("ID"="ID")                                                                                         
25 rows selected.

Ok that’s nice and acceptable response time. But let’s try the same query under first_rows mode

mhouri.world> alter session set optimizer_mode=FIRST_ROWS;
Session altered.

mhouri.world> select *
  2  from t1 where id in (select id from t2 where x1 = 17335)
  3  order by id
  4  ;

107 rows selected.

mhouri.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                                                                      
--------------------------------------------------------------------------------------
SQL_ID  d2wym19g2n196, child number0                                                                                   
-------------------------------------                                                                                  
select * from t1 where id in (select id from t2 where x1 = 17335) order by id                               
Plan hash value: 3283237002                                                                                            

--------------------------------------------------------------------------------------                                 
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                 
--------------------------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT             |       |       |       |   102M(100)|          |                                 
|   1 |  NESTED LOOPS SEMI           |       |   101 | 13130 |   102M  (1)|168:20:17 |                                 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   114M| 20385   (1)| 00:02:01 |                                 
|   3 |    INDEX FULL SCAN           | T1_N1 |  1000K|       |  2800   (1)| 00:00:17 |                                 
|*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   102   (0)| 00:00:01 |                                 
|*  5 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     2   (0)| 00:00:01 |                                 
--------------------------------------------------------------------------------------                                 
Predicate Information (identified by operationid):                                                                     
---------------------------------------------------                                                                    
   4 - filter("ID"="ID")                                                                                               
   5 - acces("X1"=17335)                                                                                               
23 rows selected.

Woo!!! Note the apparition of the costly operation 3 INDEX FULL SCAN, the enormous cost difference between all_rows and first_rows and the absence of the order by operation. Under the later mode, Oracle optimizer prefers using a costly INDEX FULL SCAN operation instead of doing a sort operation which in this case will generate a catastrophic response time.