Fehler beim Mview-Refresh mit Lateral Join in 12.2

Da ich in den ODC Foren (früher OTN Foren) kaum etwas wiederfinde, hier ein Link auf eine Frage, die ich zu einem Verhalten gestellt habe, das mir zuletzt beim Refresh einer Materialized View mit Lateral Join begegnet war:

-- 12.2.0.1 SE
create table t(
   col1 number
 , col2 varchar2(4000)
);

insert into t(col1, col2) values(1, '[{"type":1,"target":42}]');
insert into t(col1, col2) values(2, '[{"type":1,"target":42},{"type":2,"target":43}]');

create materialized view t_mv
as
select t.col1, t.col2, r.val
  from t,
  LATERAL (SELECT MIN(val) AS val
             FROM JSON_TABLE ( t.col2, '$[*].target'
                               COLUMNS (val NUMBER PATH '$')
                              )
           ) r;

SQL> create materialized view t_mv
  2  as
  3  select t.col1, t.col2, r.val
  4    from t,
  5    LATERAL (SELECT MIN(val) AS val
  6               FROM JSON_TABLE ( t.col2, '$[*].target'
  7                                 COLUMNS (val NUMBER PATH '$')
  8                                )
  9             ) r;

Materialized view created.

SQL> exec dbms_mview.refresh('t_mv')

BEGIN dbms_mview.refresh('t_mv'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2952
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2370
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2352
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2908
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3191
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3221
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15
ORA-06512: at line 1

Die Anlage der MView ist also zunächst erfolgreich, aber der complete refresh scheitert mit einem unerwarteten ORA-942-Fehler - obwohl die Tabelle im gleichen Schema liegt wie die MView. Ein Blick ins SQL Trace zur Operation zeigt, dass der Fehler offenbar die Statistikerstellung für ein transientes Objekt betrifft: eine VW_LAT, die natürlich nicht persitiert wird:

kkzfThrowError:  error_pos = kkzfGetNumRows:OCIStmtPrepare2 errcode = 942  msgbuf =ORA-00942: table or view does not exist

kkzfPrintError: error = 31933 btm = 0  emsglen = 137
ORA-31933: error occurred during refresh statistics processing at kkzfGetNumRows:OCIStmtPrepare2
ORA-00942: table or view does not exist
kkzfSetupStatsCtxDrv:1: stats is disabled on error 31933
kkzfSetupStatsCtxDrv:1: error = 31933 is cleared
kkzdQueryObjNumByName:------ORA-1403 data not found ---
kkzdQueryObjNumByName:usrid= 104, oname=VW_LAT_9DCD9C42
kkzdQueryObjNumByName:---------------------------------

Im Forum gab es - wie üblich - ein paar gute Hinweise:

• Andrew Sayer lieferte den Vorschlag, einen optimizer_features_enable Hint zu ergänzen, um das Optimizer-Verhalten auf eine andere Version zu setzen (mit 11.1.0.7 funktioniert der refresh).
• Dontatello Settembrino merkte an, dass der refresh funktioniert, wenn man die Operation als SYS ausführt (was kein workaround ist, aber eine interessante Beobachtung).
• Mustafa Kalayci fand heraus, dass der Refresh in 12.2 funktioniert, wenn man ihn in eine skalare Subquery einbaut.

Damit habe ich ausreichend viele Varianten, um dem Problem aus dem Weg zu gehen.

Partitionierung mit Postgres 12

Daniel Westermann hat im dbi Services Blog eine interessante Serie begonnen, die sich mit den aktuellen Möglichkeiten der Partitionierung in Postres 12 (das sich noch im Beta-Status befindet) beschäftigt:

• PostgreSQL partitioning (1): Preparing the data set: beschreibt die Erzeugung einer Testtabelle aus einer öffentlich verfügbaren Datenquelle der US-Regierung. Die Ladeoperation erfolgt über ein copy-Kommando. Zu dieser Tabelle wird eine Materialized View erzeugt. Wenn eine MV einen unique index besitzt kann der Refresh concurrently erfolgen (also parallele Zugriffe während des Refreshs erlauben).
• PostgreSQL partitioning (2): Range partitioning: zeigt das Vorgehen zur Anlage einer Tabelle mit Range-Partitionierung auf Jahresbasis, bei dem zunächst die partitionierte Tabelle und anschließend separat die Partitionen angelegt werden. Die ranges werden in der Definition der Partition explizit angegeben - also nicht über ein Pattern. Außerdem muss die Obergrenze bereits den ersten Tag des Folgejahres angeben, da die "to" Angabe eine exklusives Limit darstellt. Zusätzlich zu den definierten range-Partitionen kann eine default Partition erzeugt werden, die alle Datensätze aufnimmt, die keinem der definierten ranges entsprechen.
• PostgreSQL partitioning (3): List partitioning: zeigt das Verhalten von List Partitionierung, dass anscheinend keine besonderen Überraschungen bietet.
• PostgreSQL partitioning (4): Hash partitioning: beschäftigt sich mit Hash Partitionierung. Dazu wird in der Partitions-Definition eine modulus-Funktion auf einen Spaltenwert verwendet, um die Zuordnung zu bestimmen. In diesem Fall ist die Anlage einer default-Partition nicht möglich (oder sinnvoll). Da die Verteilungsfunktion manuell definiert wird, liegt es beim Verwender, dafür zu sorgen, dass sich eine plausible und gleichmäßige Verteilung auf die Partitionen ergibt. Hier scheint das Verfahren noch nicht allzu elaboriert zu sein.
• PostgreSQL partitioning (5): Partition pruning: behandelt das Partition Pruning, also die Möglichkeit des Optimizers, für eine Abfrage irrelevante Partitionen beim Zugriff ausklammern zu können. In Postgres 10 funktionierte das nur, wenn eine Einschränkung bereits in der Planning Phase bestimmbar war. In Postgres 11 kann das Pruning auch erfolgen, wenn die Einschränkung erst bei der Execution erkennbar wird.
• PostgreSQL partitioning (6): Attaching and detaching partitions: zeigt, wie man Partitionen über attach in eine partitionierte Tabelle eingliedern - bzw. über deatch daraus lösen kann. Die abgehängte Tabelle kann dann nach belieben weiter verwendet werden.
• PostgreSQL partitioning (7): Indexing and constraints: erklärt, wie die Beschränkungen der Partitionierung reduziert werden konnten. In Postgres 10 konnte man keinen Primary Key auf einer partitionierten Tabelle anlegen, was inzwischen möglich ist. Möglich ist auch die Anlage eines Index, der auf eine einzelne Partition beschränkt bleibt. Was noch nicht funktioniert ist die Anlage eines partitionierten Index (in Oracle wäre das ein lokaler Index) mit der Option concurrently. Man kann den Index aber beschränkt auf der Ebene der partitionierten Tabelle anlegen, was ihn in einem invaliden Zustand bringt, und dann ein create index concurrently auf Partitionsebene starten. Anschließend können diese Index-Partitionen an den partitionierten Index attached werden (was diesen in den Zustand valid überführt). Auch die Anpassung von Constraints (etwa not null) kann individuell auf Partitions-Ebene erfolgen.

 Die Serie wird fortgesetzt und ich versuche - wie üblich - die folgenden Artikel hier zu ergänzen.