By Franck Pachot

.
There are several bugs with the optimizer in FIRST_ROWS mode. Here is one I encountered during a 10.2.0.4 to 12.2.0.1 migration when a view had an ‘order by’ in its definition.

Here is the test case that reproduces the problem.

A big table:


SQL> create table DEMO1 (n constraint DEMO1_N primary key,x,y) as select 1/rownum,'x','y' from xmltable('1 to 1000000');
Table DEMO1 created.

with a view on it, and that view has an order by:


SQL> create view DEMOV as select * from DEMO1 order by n desc;
View DEMOV created.

and another table to join to:


SQL> create table DEMO2 (x constraint DEMO2_X primary key) as select dummy from dual;
Table DEMO2 created.

My query reads the view in a subquery, adds a call to a PL/SQL function, and joins the result with the other table:


SQL> explain plan for
    select /*+ first_rows(10) */ *
    from
    ( select v.*,dbms_random.value from DEMOV v)
    where x in (select x from DEMO2)
    order by n desc;
 
Explained.

You can see that I run it with FIRST_ROWS(10) because I actually want to fetch the top-10 rows when ordered by N. As N is a number and I have an index on it and there are no nulls (it is the primary key) I expect to read the first 10 entries from the index, call the function for each of them, then nested loop to the other tables.

In the situation I encountered it, this is what was done in 10g but when migrated to 12c the query was very long because it called the PL/SQL function for million of rows. Here is the plan in my example:


SQL> select * from dbms_xplan.display(format=>'+projection');
 
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2046425878
 
--------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     1 |    21 |       |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI      |          |     1 |    21 |       |     7   (0)| 00:00:01 |
|   2 |   VIEW                  | DEMOV    |   902 | 17138 |       |     7   (0)| 00:00:01 |
|   3 |    SORT ORDER BY        |          |   968K|    17M|    29M|  6863   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | DEMO1    |   968K|    17M|       |  1170   (1)| 00:00:01 |
|   5 |   VIEW PUSHED PREDICATE | VW_NSO_1 |     1 |     2 |       |     0   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN    | DEMO2_X  |     1 |     2 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("X"="V"."X")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) "V"."N"[NUMBER,22], "V"."X"[CHARACTER,1], "V"."Y"[CHARACTER,1]
   2 - "V"."N"[NUMBER,22], "V"."X"[CHARACTER,1], "V"."Y"[CHARACTER,1]
   3 - (#keys=1) INTERNAL_FUNCTION("N")[22], "X"[CHARACTER,1], "Y"[CHARACTER,1]
   4 - "N"[NUMBER,22], "X"[CHARACTER,1], "Y"[CHARACTER,1]

A full table scan of the big table, with a call to the PL/SQL function for each row and the sort operation on all rows. Then the Top-10 rows are filtered and the nested loop operates on that. But you see the problem here. The cost of the ‘full table scan’ and the ‘order by’ has been evaluated correctly, but the cost after the VIEW operation is minimized.

My interpretation (but it is just a quick guess) is that the the rowset is marked as ‘sorted’ and then the optimizer considers that the cost to get first rows is minimal (as if it were coming from an index). However, this just ignores the initial cost of getting this rowset.

I can force with a hint the plan that I want – index full scan to avoid a sort and get the top-10 rows quickly:


SQL> explain plan for
    select /*+ first_rows(10)       INDEX_DESC(@"SEL$3" "DEMO1"@"SEL$3" ("DEMO1"."N")) */ *
    from
    ( select v.*,dbms_random.value from DEMOV v)
    where x in (select x from DEMO2)
    order by n desc;
 
Explained.

This plan is estimated with an higher cost than the previous one and this is why it was not chosen:


SQL> select * from dbms_xplan.display(format=>'+projection');
PLAN_TABLE_OUTPUT
Plan hash value: 2921908728
 
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    21 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI            |          |     1 |    21 |     9   (0)| 00:00:01 |
|   2 |   VIEW                        | DEMOV    |   902 | 17138 |     9   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEMO1    |   968K|    17M|  8779   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN DESCENDING| DEMO1_N  |   968K|       |  4481   (1)| 00:00:01 |
|   5 |   VIEW PUSHED PREDICATE       | VW_NSO_1 |     1 |     2 |     0   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN          | DEMO2_X  |     1 |     2 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("X"="V"."X")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) "V"."N"[NUMBER,22], "V"."X"[CHARACTER,1], "V"."Y"[CHARACTER,1]
   2 - "V"."N"[NUMBER,22], "V"."X"[CHARACTER,1], "V"."Y"[CHARACTER,1]
   3 - "N"[NUMBER,22], "X"[CHARACTER,1], "Y"[CHARACTER,1]
   4 - "DEMO1".ROWID[ROWID,10], "N"[NUMBER,22]

This cost estimation is fine. The cost of getting all rows by index access is higher than with a full table scan, but the optimizer knows that the actual cost is proportional to the number of rows fetched and then it adjusts the cost accordingly. This is fine here because the VIEW has only non-blocking operations. The problem in the first plan without the hint, was because the same arithmetic was done, without realizing that the SORT ORDER BY is a blocking operation and not a permanent sorted structure, and must be completed before being able to return the first row.

In this example, as in the real case I’ve encountered, the difference in cost is very small (7 versus 9 here) which means that the plan can be ok and switch to the bad one (full scan, call function for all rows, sort them) with a small change in statistics. Note that I mentioned that the plan was ok in 10g but that may simply be related to the PGA settings and different estimation for the cost of sorting.