Costing für ROWNUM-Queries

Mit dem folgenden Eintrag bin ich eher weniger glücklich, weil es mir nicht gelungen ist, das beobachtete Verhalten in einem Test nachzustellen. Leider habe ich zur Zeit auch nur den Execution Plan, aber keinen Zugriff auf das System, in dem ich das Verhalten beobachtet habe. Aber vielleicht sollte ich erst mal erklären, wovon ich überhaupt rede: dieser Tage wurde mir eine Query mit einer Einschränkung WHERE rownum < 10 gezeigt, bei der der CBO in 11.1.0.7 ganz offenbar zu einer recht abwegigen Ausführungsstrategie gelangt war:

select *
  from view_xyz
 where rownum < 10;

-- dazu der Plan (anonymisiert und ohne Time-Angabe)
-----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |    9 |  1440 |    36   (3)|       |       |
|   1 |  PARTITION LIST SINGLE                |         |    2 |    52 |  9534   (1)|     1 |     1 |
|*  2 |   TABLE ACCESS FULL                   | DIM1    |    2 |    52 |  9534   (1)|     1 |     1 |
|*  3 |  COUNT STOPKEY                        |         |      |       |            |       |       |
|   4 |   NESTED LOOPS OUTER                  |         |   10 |  1600 |    36   (3)|       |       |
|   5 |    NESTED LOOPS OUTER                 |         |    5 |   765 |    32   (4)|       |       |
|   6 |     NESTED LOOPS OUTER                |         |    5 |   610 |    17   (6)|       |       |
|*  7 |      HASH JOIN OUTER                  |         |    5 |   555 |     7  (15)|       |       |
|   8 |       PARTITION LIST ALL              |         |    5 |   505 |     2   (0)|     1 |  1523 |
|   9 |        TABLE ACCESS FULL              | FAKT1   |    5 |   505 |     2   (0)|     1 |  1523 |
|  10 |       TABLE ACCESS FULL               | DIM2    |  171 |  1710 |     4   (0)|       |       |
|  11 |      TABLE ACCESS BY INDEX ROWID      | DIM3    |    1 |    11 |     2   (0)|       |       |
|* 12 |       INDEX RANGE SCAN                | DIM3_IX |    1 |       |     1   (0)|       |       |
|  13 |     PARTITION LIST SINGLE             |         |    1 |    31 |     3   (0)|     1 |     1 |
|  14 |      TABLE ACCESS BY LOCAL INDEX ROWID| DIM4    |    1 |    31 |     3   (0)|     1 |     1 |
|* 15 |       INDEX RANGE SCAN                | DIM4_IX |    1 |       |     2   (0)|     1 |     1 |
|  16 |    VIEW                               |         |    2 |    14 |     1   (0)|       |       |
|  17 |     CONCATENATION                     |         |      |       |            |       |       |
|  18 |      TABLE ACCESS BY INDEX ROWID      | DIM5    |    1 |    19 |     2   (0)|       |       |
|* 19 |       INDEX RANGE SCAN                | DIM5_IX |    1 |       |     1   (0)|       |       |
|  20 |      TABLE ACCESS BY INDEX ROWID      | DIM5    |    1 |    19 |     2   (0)|       |       |
|* 21 |       INDEX UNIQUE SCAN               | DIM5_IX |    1 |       |     1   (0)|       |       |
-----------------------------------------------------------------------------------------------------

Das entscheidende Problem dabei ist der Zugriff auf die Faktentabelle FAKT1 in step 8 und 9: die Tabelle wird ohne jede Einschränkung als BUILD table für den HASH JOIN OUTER verwendet, muss also komplett in den Speicher gelesen werden, ehe der Zugriff auf die Dimensionstabelle DIM2 erfolgt. Da die Faktentabelle 1523 Partitionen umfasst und mehr als 140M rows enthält, kann man auf die gewünschten 9 Ergebniszeilen lange warten. Dabei ergibt sich die cardinality 5 offenbar aus der halbierten Rownum-Einschränkung (9 geteilt durch die 2 aus step 16). Anscheinend vergisst der CBO in diesem Fall an irgendeiner Stelle, dass die rownum-Einschränkung im Fall eines HASH JOINs, bei dem die komplette Faktentabelle (PARTITION LIST ALL) in den Speicher gelesen werden muss, ehe der folgende Operation beginnen kann, nicht wirklich viel bringt.

Ein interessanter Fall, aber leider einer, den ich nicht reproduzieren konnte. Möglicherweise kann ich gelegentlich noch mal einen Blick auf das fragliche System werfen. Bis dahin bleibt's erst mal eine Geschichte mit offenem Schluss.