Costing für FIRST_ROWS(n)

In Cost Based Oracle steht meiner Erinnerung nach nichts dazu, aber der Fall ist relativ einfach, so dass man dazu den Herrn Lewis wohl nicht benötigt [dafür aber den Herrn Geist - s. den Nachtrag vom 30.06.]:

• der traditionelle FIRST_ROWS-Hint ("first_rows is avaiable only for backwards compatibility") hat offenbar keinen Einfluß auf die Cost-Berechnung
• im Fall von FIRST_ROWS(n) scheint in etwa folgende harmlose Formel zu gelten:

(n/number_of_rows_in_table) * initial_cost

Hinweis: wie Randolf Geist in seinem Kommentar erwähnt - und in seiner unten verlinkten Präsentation näher erläutert - ist dieser einfache "proration factor" (= n / total_number_of_rows) nur ein Element innerhalb eines deutlich komplexeren Kalküls.

Dazu noch der Test, auf dem meine Annahmen basieren:

create table test_first_rows_hint
as
select rownum id
  from dual
connect by level <= 1000000;
 
exec dbms_stats.gather_table_stats(user, 'TEST_FIRST_ROWS_HINT', estimate_percent => 100)
 
select * from test_first_rows_hint;
 
1000000 Zeilen ausgewählt.
 
Abgelaufen: 00:00:03.96
 
Ausführungsplan
----------------------------------------------------------
Plan hash value: 3818872010
 
------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |  1000K|  4882K|   780   (0)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TEST_FIRST_ROWS_HINT |  1000K|  4882K|   780   (0)| 00:00:04 |
------------------------------------------------------------------------------------------

In den folgenden Testläufen wurde die Query dann jeweils mit einem Hint versehen:

• first_rows: 780 (wie im Fall ohne Hint)
• first_rows(1): 2
• first_rows(1000): 3
• first_rows(10000): 9
• first_rows(100000): 80 (ca. 10% der ursprünglichen Kosten bei Optimierung für 10% der Sätze)
• first_rows(500000): 391 (ca. 50% der ursprünglichen Kosten bei Optimierung für 50% der Sätze)

Jonathan Lewis und Charles Hooper haben zum Thema übrigens noch ein paar interessante Punkte gesammelt, die über den Rahmen meiner Miniatur-Analyse hinaus gehen:

• http://hoopercharles.wordpress.com/2011/03/10/what-is-the-difference-between-the-first_rows-hint-and-rownum-in-the-where-clause/
• http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/

Nachtrag 30.06.2011: meine Annahme, dass das Costing für den FIRST_ROWS(n) Hint relativ simplen Regeln folgt, hat sich als deutlich zu optimistisch erwiesen, wie Randolf Geists Kommentar zu diesem Blog-Eintrag zeigt. Eine extrem detaillierte Beschreibung der Effekte der FIRST_ROWS%-Hints und ihrer sinnvollen Verwendung findet man in seiner Präsentation im Rahmen der UKOUG 2009: Everything You Wanted To Know About FIRST_ROWS_n But Were Afraid To Ask