Materialized Views und ORA-32036

Vor längerer Zeit hatte ich erwähnt, dass die Verwendung von Queries mit subquery factoring (CTEs, WITH-Klauseln) als Datengrundlage für das SSAS-Prozessing oder auch für SSRS-Berichte den (nicht unbedingt selbsterklärenden) Fehler "ORA-32036: Nicht unterstützte Schreibweise für Inlining von Abfrage-Name in WITH-Klausel" hervorrufen kann. Dabei konnte man die Queries über sqlplus oder SQL Developer problemlos ausführen, aber beim Zugriff der Microsoft-Systeme ergaben sich die Probleme, die sich nur durch Refakturierung des Codes oder größere Umwege (z.B. die Definition entsprechender table functions) umgehen ließen.

Gestern ist mir ORA-32036 wieder begegnet: diesmal bei der Anlage einer Materialized View. Und in diesem Fall lässt sich das Verhalten sehr leicht reproduzieren:

-- Anlage von zwei - sehr simplen - Basistabellen
create table test_base_mpr
as
select rownum id
     , mod(rownum, 2) col1
  from dual
connect by level <= 100;

create table test_add_mpr
as
select rownum col1
  from dual
connect by level <= 10;

create materialized view test_mpr
as
with
basedata as (
select test_base_mpr.id
     , test_base_mpr.col1
  from test_base_mpr
  left outer join 
       test_add_mpr
    on (test_base_mpr.col1 = test_add_mpr.col1)
)
,
basedata_filtered as (
select * 
  from basedata
 where id <= 50
)
,
basedata_grouped as (
select col1
     , count(*) count_id
  from basedata_filtered
 group by col1
)
select basedata_filtered.col1
     , basedata_grouped.count_id
  from basedata_filtered
  left outer join
       basedata_grouped
    on basedata_filtered.col1 = basedata_grouped.col1
 order by basedata_filtered.col1;
 
FEHLER in Zeile 1:
ORA-32036: Nicht unterstützte Schreibweise für Inlining von Abfrage-Name in WITH-Klausel

-- wenn man die CTE basedata in eine inline-View umwandelt, gibt es keine Probleme:
create materialized view test_mpr
as
with
basedata_filtered as (
select *
  from (select test_base_mpr.id
             , test_base_mpr.col1
          from test_base_mpr
          left outer join
               test_add_mpr
            on (test_base_mpr.col1 = test_add_mpr.col1)
        ) basedata
 where id <= 50
)
,
basedata_grouped as (
select col1
     , count(*) count_id
  from basedata_filtered
 group by col1
)
select basedata_filtered.col1
     , basedata_grouped.count_id
  from basedata_filtered
  left outer join
       basedata_grouped
    on basedata_filtered.col1 = basedata_grouped.col1
 order by basedata_filtered.col1;

Materialized View wurde erstellt.

Auch in diesem Fall lässt sich die ursprüngliche Query via sqlplus problemlos ausführen (über die Semantik der Query und den Inhalt der Ergebnismenge - je 25 mal die Tupel (0, 25) und (1, 25) - muss man nicht weiter nachdenken). Auch kann man auf der Basis der Definition eine (nicht materialisierte) View anlegen. Aber die MV stellt offenbar ein Problem dar. Beim Vergleich der Pläne für die SELECTs (inklusive "Column Projection Information") sehe ich - abgesehen von generierten Query-Block- und temp-table-transformation-Namen - keine Unterschiede. Dabei sollte das SQL der Query dem des MV-Aufbaus entsprechen: zumindest im Fall des erfolgreichen Aufbaus trifft dies zu, hier scheint nur das übliche

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."TEST_MPR"

vor der Definitions-Query.

Mein Versuch, die exakten Voraussetzungen des Problems zu bestimmen, bleibt erst einmal unvollständig. Ursprünglich nahm ich an, dass die Verwendung einer CTE an mehreren Stellen der Query genügen würde, um den Fehler hervorzurufen, aber auch die funktionierende Version verwendet die CTE basedata_filtered als Basis für basedata_grouped und als Element des Outer Joins im Haupt-SELECT. Möglicherweise spielt tatsächlich nur die CTE-Verschachtelungstiefe eine Rolle. In jedem Fall ist das Problem ziemlich unerfreulich, da es dazu zwingt, den durch CTEs verständlich gegliederten SQL-Code wieder in eine Form zu bringen, die der CBO offenbar besser versteht - die aber für den Entwickler in vielen Fällen schwerer zu durchschauen sein dürfte. Falls mir (oder jemandem, der hier vorbei kommt) jenseits der Phänomenologie eine plausible Erklärung dazu einfällt, liefere ich sie nach.

Aus Gründen der Vollständigkeit hier auch noch mal der Link auf Dom Brooks Überlegungen zum Thema - interessant sind dort auch die Kommentare.