FIRST_ROWS in 9i


Before 9i, the optimizer methods available were

(You could argue that CHOOSE is a 'mode', but in reality is a simply a preliminary decision on whether to use ALL_ROWS or RULE).

In 9i, some more flexibility between the two extremes of ALL_ROWS and FIRST_ROWS have been introduced. These are:

However, there are some important distinctions to make here. The traditional 'FIRST_ROWS' parameter is not a genuine cost-based option, more a hybrid between the rule and cost. This can be observed via the following example:

SQL> select * from demo where x < 100;

The explain plan of which is:

PLAN
-------------------------------------------------------------
  SELECT STATEMENT  (OPTIMIZER=CHOOSE)   (COST=62) (ROWS=99)
    TABLE ACCESS FULL DEMO (COST=62) (ROWS=99)

SQL> alter session set optimizer_goal = first_rows;

SQL> select * from demo where x < 100;

The explain plan is now transformed to:

PLAN
-----------------------------------------------------------------
  SELECT STATEMENT  (OPTIMIZER=FIRST_ROWS)   (COST=102) (ROWS=99)
    TABLE ACCESS BY INDEX ROWID DEMO (COST=102) (ROWS=99)
      INDEX RANGE SCAN DEMO_IX (COST=2) (ROWS=99)

Notice that the optimizer has chosen a path with a HIGHER cost, so in the case of first_rows, it is not solely the cost that plays a part as a determining factor.

Let us move onto 9i then. You could quite naturally assume that the FIRST_ROWS_1 is the same as FIRST_ROWS, but this is NOT the case. All of the new optimizer modes use the cost calculation and no other heuristics. In fact, it would appear that the optimizer simply uses the 'n' in FIRST_ROWS_n as an important driver in determining cardinalities for query result sets.

For example, lets look back at the original query. (I've lowered db_file_multiblock_read_count to lean toward indexes in all cases to give some consistency to the output).

SQL> alter session set optimizer_goal = choose;

SQL> select * from demo where x < 200;

PLAN
---------------------------------------------------------------
  SELECT STATEMENT  (OPTIMIZER=CHOOSE)   (COST=202) (ROWS=199)
    TABLE ACCESS BY INDEX ROWID DEMO (COST=202) (ROWS=199)
      INDEX RANGE SCAN DEMO_IX (COST=2) (ROWS=199)

In my case, the cardinality of 199 is pretty accurate since I've got 1 row per value of X (from 1 to 1000). Now lets look at what happens with the new optmizer modes:

SQL> alter session set optimizer_mode = first_rows_10;

SQL> select * from demo where x < 200;

PLAN
----------------------------------------------------------------
  SELECT STATEMENT  (OPTIMIZER=FIRST_ROWS)   (COST=13) (ROWS=10)
    TABLE ACCESS BY INDEX ROWID DEMO (COST=13) (ROWS=10)
      INDEX RANGE SCAN DEMO_IX (COST=2) (ROWS=10)

SQL> alter session set optimizer_mode = first_rows_100;

SQL> select * from demo where x < 200;

PLAN
-------------------------------------------------------------------
  SELECT STATEMENT  (OPTIMIZER=FIRST_ROWS)   (COST=103) (ROWS=100)
    TABLE ACCESS BY INDEX ROWID DEMO (COST=103) (ROWS=100)
      INDEX RANGE SCAN DEMO_IX (COST=2) (ROWS=100)

So we can start to see the methodology at work here. The optimizer is "taking our word" in that, (independent of what the table statistics imply), it will only need to use the first 'n' rows in the relevant source. It is a true cost adjustment rather than a change to the optimization scheme (as the original FIRST_ROWS is)