CTEs und NL Cardinalities

Dass CTEs (also Oracles subquery factoring mit einer WITH-clause) zu seltsamen Costing Effekten führen können, ist keine neue Beobachtung. Randolf Geist hat z.B. mehrere Artikel zum Thema geschrieben, in denen er u.a. auf der Basis von CBO Traces aufzeigt, dass für Queries mit CTEs bestimmte Transformationen nicht durchgeführt werden können (im Trace findet sich dann der Hinweis "CBQT: copy not possible on query block [...] because linked to with clause"). Einige der angesprochenen Probleme wurden offenbar in 11.2.0.3 behoben. Aber auf diesem Release-Stand bin ich noch nicht angekommen.

In 11.1.0.7 ist mir gestern folgender Effekt begegnet:

drop table test_dim;
drop table test_fact;

create table test_dim
as
with
generator as (
select to_date('01.01.2012', 'dd.mm.yyyy') - 1 + rownum a_date
  from dual
connect by level <= 366
)
select to_char(trunc(a_date, 'mm'), 'mm') a_month
     , min(a_date) min_date
     , max(a_date) max_date
  from generator
 group by trunc(a_date, 'mm')
 order by trunc(a_date, 'mm')
;

create table test_fact
as
with 
generator as (
select to_date('01.10.2012', 'dd.mm.yyyy') - 1 + rownum a_date
  from dual
connect by level <= 92
)
,
facts as (
select 1000 val
  from dual
connect by level <= 10000
)
select a_date
     , 1000 col1
  from generator
     , facts;

exec dbms_stats.gather_table_stats(user, 'test_dim')
exec dbms_stats.gather_table_stats(user, 'test_fact')

Also eine Tabelle test_dim mit 12 Sätzen (je einer für jeden Monat im Jahr 2012), einem Monatsnamen und dem ersten ersten und letzten Tag des Monats und eine Tabelle test_fact mit jeweils 10000 Sätzen für jeden Tag im letzten Quartal des Jahres - insgesamt also 92 * 10000 = 920000 rows - und einer bedeutungslosen Kennzahl col1. Dazu gibt's dann eine Query, die einige Angaben aus test_dim in einer CTE abruft und mit den Fakten joint:

-- Fall 1: materialize
explain plan for
with
date_range as (
select /*+ materialize */
       a_month
     , min_date
     , max_date
  from test_dim
 where a_month >= 10
)
select a_month
     , count(*)
  from date_range
  join test_fact
    on (test_fact.a_date between date_range.min_date and date_range.max_date)
 group by a_month
;

select * from table(dbms_xplan.display);

-- Fall 2: inline
explain plan for
with
date_range as (
select /*+ inline */
       a_month
     , min_date
     , max_date
  from test_dim
 where a_month >= 10
)
select a_month
     , count(*)
  from date_range
  join test_fact
    on (test_fact.a_date between date_range.min_date and date_range.max_date)
 group by a_month
;

select * from table(dbms_xplan.display);

Der Unterschied der beiden Queries liegt in der Behandlung der CTE, die in Fall 1 im temporary tablespace materialisiert und in Fall 2 textuell an der vorgesehenen Stelle integriert wird. Die beiden Varianten liefern folgende Pläne:

-- Fall 1: Materialize
----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     1 |    29 |   580   (2)| 00:00:07 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | TEST_FACT                   |       |       |            |          |
|*  3 |    TABLE ACCESS FULL       | TEST_DIM                    |     1 |    19 |     3   (0)| 00:00:01 |
|   4 |   HASH GROUP BY            |                             |     1 |    29 |   577   (2)| 00:00:07 |
|   5 |    NESTED LOOPS            |                             |  2300 | 66700 |   576   (2)| 00:00:07 |
|   6 |     VIEW                   |                             |     1 |    21 |     2   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6610_271DA033 |     1 |    19 |     2   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL      | TEST_FACT                   |  2300 | 18400 |   574   (2)| 00:00:07 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(INTERNAL_FUNCTION("A_MONTH")>=TO_DATE(' 2012-10-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   8 - filter("TEST_FACT"."A_DATE">="DATE_RANGE"."MIN_DATE" AND
              "TEST_FACT"."A_DATE"<="DATE_RANGE"."MAX_DATE")

-- Fall 2: inline
Plan hash value: 423103375

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     1 |    27 |   582   (3)| 00:00:07 |
|   1 |  HASH GROUP BY      |           |     1 |    27 |   582   (3)| 00:00:07 |
|   2 |   NESTED LOOPS      |           | 84235 |  2221K|   577   (2)| 00:00:07 |
|*  3 |    TABLE ACCESS FULL| TEST_DIM  |     1 |    19 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| TEST_FACT |   140K|  1096K|   574   (2)| 00:00:07 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(INTERNAL_FUNCTION("A_MONTH")>=TO_DATE(' 2012-10-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - filter("TEST_FACT"."A_DATE">="MIN_DATE" AND
              "TEST_FACT"."A_DATE"<="MAX_DATE")

Erwähnenswert ist dabei vielleicht auch noch, dass der CBO ohne Hint den inline-Plan verwendet, obwohl dessen Kosten geringfügig höher sind als die der Variante mit der Materialisierung (582 zu 580). Davon abgesehen sind die Cardinality-Schätzungen für test_fact in beiden Fällen ziemlich weit von den Ausführungs-Realitäten entfernt:

• tatsächlich werden alle 920.000 Sätze der Tabelle gelesen
• für die Version mit dem Materialize-Hint errechnet sich die Cardinality offenbar wieder einmal auf Grundlage der Standard-Arithmetik für Bound-Ranges, also jeweils 5% als Faktor für die untere und die oberer Grenze: 920000 * 0,05 * 0,05 = 2300.
• weniger klar ist mir, woher die 140K kommen.

Ein Blick ins CBO-Trace verrät zumindest, welche Werte in die Rechnung eingehen:

NL Join
  Outer table: Card: 3.19  Cost: 3.00  Resp: 3.00  Degree: 1  Bytes: 24
Access path analysis for TEST_FACT
  Inner table: TEST_FACT  Alias: TEST_FACT
  Access Path: TableScan
    NL Join:  Cost: 1720.93  Resp: 1720.93  Degree: 1
      Cost_io: 1693.00  Cost_cpu: 458426063
      Resp_io: 1693.00  Resp_cpu: 458426063

  Best NL cost: 1720.93
          resc: 1720.93  resc_io: 1693.00  resc_cpu: 458426063
          resp: 1720.93  resp_io: 1693.00  resc_cpu: 458426063
Join Card:  447155.913681 = = outer (3.185075) * inner (920000.000000) * sel (0.152599)

Die sel (0.152599) ist dabei offenbar der Faktor, der von der Satzanzahl zur cardinality von 140K führt: 920000 * 0,152599 = 140391,08. Dabei erscheint die cardinality-Angabe selbst nicht im Trace (weder als 140K noch als 140391). Aber was bedeutet diese selectivity? Bisher habe ich dafür noch keine Erklärung. Der Zusammenhang wird auch dadurch nicht klarer, dass die in der CTE verwendete Einschränkung auf a_month offenbar gar keine Auswirkung auf die selectivity-Bestimmung hat:

• a_month >= 1: tatsächlich: 920K; rows(test_fact) = 140K; rows(NL): 84235
• a_month >= 10: tatsächlich: 920K; rows(test_fact) = 140K; rows(NL): 84235
• a_month >= 12: tatsächlich: 310K; rows(test_fact) = 140K; rows(NL): 84235
• a_month >= 13: tatsächlich: 0; rows(test_fact) = 140K; rows(NL): 84235 !!
• a_month >= 20: tatsächlich: 0; rows(test_fact) = 140K; rows(NL): 84235 !!

Und auch die Auswirkung einer Änderung der Join-Bedingung zwischen CTE und test_fact ist nicht unmittelbar plausibel (bestimmt jeweils mit a_month >= 10 in der CTE, was wie gerade angesprochen vermutlich in keinem Fall relevant ist):

• (test_fact.a_date between date_range.min_date and date_range.max_date): rows(test_fact) = 140K
• (test_fact.a_date >= date_range.min_date): rows(test_fact) = 835K
• (test_fact.a_date > date_range.min_date): rows(test_fact) = 833K
• (test_fact.a_date < date_range.min_date): rows(test_fact) = 76326
• (test_fact.a_date <= date_range.min_date): rows(test_fact) = 78828

Insgesamt scheint die Behandlung von Range-Prädikaten an dieser Stelle etwas unübersichtlich zu sein - ein nachvollziehbares Muster sehe ich da zunächst nicht. Und auf Anhieb habe ich auch weder in Cost-Based Oracle noch im Netz eine Erklärung der Berechnung gefunden - wobei ich sicher bin, dass die Herren Lewis, Geist etc. diese Erklärung haben.