FIRST_ROWS Hint in Oracle

I shared several post on performance recently. Whenever we talk bout performance first_rows can be of significant use. On many occasion it is worthy to use this hint to gain comprehensive performance on a query.

The first_rows_n optimizer mode is used in online transaction systems to gather data with an index, to quickly start returning rows back to the application. 

The first-N rows optimization instructs the query optimizer to choose a query execution plan that minimizes the response time to produce the first N rows of query results.

First_rows optimization algorithm do not limit the scope of the row retrieval. Since Oracle 9i release 2 first_rows is announced to be obsolete and introduced first_rows_n instead, However first_rows is used still for backward compatibility and plan stability.

• FIRST_ROWS_1

• FIRST_ROWS_10

• FIRST_ROWS_100

• FIRST_ROWS_1000

Lets see this with a use case:

Create table ALL_CUSTOMERS

create table ALL_CUSTOMERS
(
 id NUMBER not null,
 first_name VARCHAR2(100) not null,
 last_name VARCHAR2(100) not null,
 description VARCHAR2(100)
)

create index X_CUSTOMER_ID_NAME on ALL_CUSTOMERS (ID);


----------------------------------------------------------------------
create sequence customersequence  INCREMENT BY   1
 NOCACHE
 NOCYCLE;

---------------------------------------------------------------------

BEGIN
FOR i in 1..20000 LOOP
INSERT/*+APPEND*/ INTO ALL_CUSTOMERS 
         (ID, 
         FIRST_NAME, 
         LAST_NAME,
         DESCRIPTION)
 VALUES(customersequence.nextval,
        dbms_random.string('A',10),
        dbms_random.string('A',15),
        dbms_random.string('A',20));
END LOOP;
END;

BEGIN
dbms_stats.Gather_table_stats('HR', 'ALL_CUSTOMERS',
method_opt => 'for all columns size skewonly');
END;

Do a simple select on ALL_CUSTOMERS and check its execution plan

EXPLAIN PLAN FOR SELECT * FROM ALL_CUSTOMERS;
Plan hash value: 2424877197

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               | 20000 |  1015K|    68   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ALL_CUSTOMERS | 20000 |  1015K|    68   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

EXPLAIN PLAN FOR SELECT /*+ FIRST_ROWS(1) */ * from ALL_CUSTOMERS;
Plan hash value: 2424877197

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    52 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ALL_CUSTOMERS |     1 |    52 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Compare both the PLAN we can see the cost of the second is less.

Lets see the affect on order by clause

EXPLAIN PLAN FOR SELECT * FROM all_customers order by id;
Plan hash value: 1542527497

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    | 20000 |  1015K|   205   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALL_CUSTOMERS      | 20000 |  1015K|   205   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | X_CUSTOMER_ID_NAME | 20000 |       |    45   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Explain plan for select /*+FIRST_ROWS(1)*/ *  from all_customers order by id;
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    52 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALL_CUSTOMERS      | 20000 |  1015K|     3   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | X_CUSTOMER_ID_NAME |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

it is clearly evident that cost is significantly decreased.

This hint can be very useful while dealing with complex views and large tables.

It can be used at system level and session level as well apart from the query level.

alter system set optimizer_mode = first_rows_100;
alter session set optimizer_mode = first_rows_100;           

Next read Parallel_Index_hint.