create table test_tab (
haus number
, artikel number
, datum date
, umsatz number
)
partition by range (datum) (
PARTITION test_tab_p_201201 VALUES LESS THAN (TO_DATE('20120201','YYYYMMDD'))
, PARTITION test_tab_p_201202 VALUES LESS THAN (TO_DATE('20120301','YYYYMMDD'))
, PARTITION test_tab_p_201203 VALUES LESS THAN (TO_DATE('20120401','YYYYMMDD'))
, PARTITION test_tab_p_201204 VALUES LESS THAN (TO_DATE('20120501','YYYYMMDD'))
, PARTITION test_tab_p_201205 VALUES LESS THAN (TO_DATE('20120601','YYYYMMDD'))
, PARTITION test_tab_p_201206 VALUES LESS THAN (TO_DATE('20120701','YYYYMMDD'))
, PARTITION test_tab_p_201207 VALUES LESS THAN (TO_DATE('20120801','YYYYMMDD'))
, PARTITION test_tab_p_201208 VALUES LESS THAN (TO_DATE('20120901','YYYYMMDD'))
, PARTITION test_tab_p_201209 VALUES LESS THAN (TO_DATE('20121001','YYYYMMDD'))
, PARTITION test_tab_p_201210 VALUES LESS THAN (TO_DATE('20121101','YYYYMMDD'))
, PARTITION test_tab_p_201211 VALUES LESS THAN (TO_DATE('20121201','YYYYMMDD'))
, PARTITION test_tab_p_201212 VALUES LESS THAN (TO_DATE('20130101','YYYYMMDD'))
, PARTITION test_tab_p_max VALUES LESS THAN (maxvalue)
)
/
begin
for i in 1..12 loop
insert into test_tab(haus, artikel, datum, umsatz)
select mod(rownum, 30) haus
, round(dbms_random.value * 1000) artikel
, add_months(to_date('01.12.2011', 'dd.mm.yyyy'), i) datum
, round(dbms_random.value * 50) umsatz
from dual
connect by level <= 1000000
;
end loop;
end;
/
Für diese Tabelle lege ich nun eine MView mit fast refresh Option an. Anschließend führe ich ein complete refresh durch:
create materialized view test_mv
refresh fast
as
select datum
, haus
, sum(umsatz) umsatz
from test_tab
group by datum
, haus;
exec dbms_mview.refresh('test_mv', 'c')
Im 10046er Trace finde ich dafür (unter anderem) folgende Queries:
delete from "TEST"."TEST_MV"
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."TEST_MV"("DATUM","HAUS",
"UMSATZ") SELECT "TEST_TAB"."DATUM","TEST_TAB"."HAUS",
SUM("TEST_TAB"."UMSATZ") FROM "TEST_TAB" "TEST_TAB" GROUP BY
"TEST_TAB"."DATUM","TEST_TAB"."HAUS"
Die MView wird also komplett geleert und dann wieder über ein INSERT mit dem (nicht dokumentierten) BYPASS_RECURSIVE_CHECK-Hint gefüllt. Nun ein zweiter Versuch: ich führe ein Update für Daten einer Partition aus:
update test_tab set umsatz = 5 where datum = '01.12.2012' and haus = 1;
exec dbms_mview.refresh('test_mv', 'f')
Diesmal liefert tkprof folgende Queries:
DELETE FROM "TEST"."TEST_MV"
WHERE
( ( (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN') <= "DATUM" AND "DATUM" < TO_DATE(' 2013-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ) )
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."TEST_MV"SELECT /*+
X_DYN_PRUNE */ "TEST_TAB"."DATUM" , "TEST_TAB"."HAUS" ,
SUM("TEST_TAB"."UMSATZ") FROM "TEST_TAB" "TEST_TAB" WHERE ( ( (TO_DATE('
2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') <=
"TEST_TAB"."DATUM" AND "TEST_TAB"."DATUM" < TO_DATE(' 2013-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ) )GROUP BY
"TEST_TAB"."DATUM","TEST_TAB"."HAUS"
Demnach wird die Einschränkung auf das Datum korrekt erkannt und berücksichtigt - wobei ich nicht sehe, woher das System diese Information bekommt. Die Einschränkung auf das Haus spielt allerdings keine Rolle. Wenn ich den Test mit einer Einschränkung auf zwei Partitionen wiederhole, ergibt sich ein entsprechendes Ergebnis:
update test_tab set umsatz = 10 where datum in ('01.01.2012', '01.12.2012') and haus = 2;
exec dbms_mview.refresh('test_mv', 'f')
DELETE FROM "TEST"."TEST_MV"
WHERE
( ( ("DATUM" < TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')) OR (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') <= "DATUM" AND "DATUM" < TO_DATE('
2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
) )
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."TEST_MV"SELECT /*+
X_DYN_PRUNE */ "TEST_TAB"."DATUM" , "TEST_TAB"."HAUS" ,
SUM("TEST_TAB"."UMSATZ") FROM "TEST_TAB" "TEST_TAB" WHERE ( (
("TEST_TAB"."DATUM" < TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) OR (TO_DATE(' 2012-12-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') <= "TEST_TAB"."DATUM"
AND "TEST_TAB"."DATUM" < TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ) )GROUP BY "TEST_TAB"."DATUM",
"TEST_TAB"."HAUS"
Zur Beantwortung der Frage, warum das Refresh in dieser Weise erfolgt, liefert die mv_capabilities_table Antworten. Anlegen kann man diese Tabelle mit Hilfe des Scripts utlxmv.sql in RDBMS/ADMIN und bei Rob van Wijk findet man nähere Erläuterungen zu den Angaben in der Tabelle. Nach Anlage der Tabelle kann man die Prozedur DBMS_MVIEW.EXPLAIN_MVIEW für eine vorhandene MView aufrufen.
exec DBMS_MVIEW.EXPLAIN_MVIEW ('TEST_MV')
select * from mv_capabilities_table
CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------------------------------------------------
PCT Y
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE N
PCT_TABLE Y
REFRESH_FAST_AFTER_INSERT N Detail-Tabelle enthält kein Materialized View-Log
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) ohne COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N Siehe Grund, warum REFRESH_FAST_AFTER_INSERT deaktiviert ist
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) ist in SELECT-Liste nicht vorhanden
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) ohne COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N Siehe Grund, warum REFRESH_FAST_AFTER_ONETAB_DML deaktiviert ist
REFRESH_FAST_PCT Y
REWRITE_FULL_TEXT_MATCH N Neuschreiben von Abfragen bei Materialized View deaktiviert
REWRITE_PARTIAL_TEXT_MATCH N Neuschreiben von Abfragen bei Materialized View deaktiviert
REWRITE_GENERAL N Neuschreiben von Abfragen bei Materialized View deaktiviert
REWRITE_PCT N Neuschreiben von Abfragen bei Materialized View deaktiviert
PCT_TABLE_REWRITE Y
Demnach fehlt der Tabelle also ein MView Log und außerdem werden ein count(*) und ein count(expr) vermisst:
create materialized view test_mv
refresh fast
as
select datum
, haus
, sum(umsatz) umsatz
, count(*) cnt
, count(umsatz) cnt_umsatz
from test_tab
group by datum
, haus;
-- Anlage MView Log mit rowid (da ich keinen PK zur Tabelle habe) und mit den relevanten Spalten
create materialized view log on test_tab with rowid (haus, artikel, datum, umsatz) including new values;
-- nach dem Aufbau des View Logs muss ein complete refresh erfolgen, ehe das View Log für
-- ein fast refresh herangezogen werden kann
exec dbms_mview.refresh('test_mv', 'c')
CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------------------------------------------
PCT Y
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE N
PCT_TABLE Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT Y
REWRITE_FULL_TEXT_MATCH N Neuschreiben von Abfragen bei Materialized View deaktiviert
REWRITE_PARTIAL_TEXT_MATCH N Neuschreiben von Abfragen bei Materialized View deaktiviert
REWRITE_GENERAL N Neuschreiben von Abfragen bei Materialized View deaktiviert
REWRITE_PCT N Neuschreiben von Abfragen bei Materialized View deaktiviert
PCT_TABLE_REWRITE Y
Somit sind nun alle Voraussetzungen für ein REFRESH_FAST_AFTER_ANY_DML gegeben. Und tatsächlich führt ein Update für die Basistabelle nun im Fall des fast refresh nicht mehr zu einem delete -> insert, sondern zu einem merge auf Basis der Inhalte des MView Logs:
-- behutsam formatiert:
MERGE INTO "TEST"."TEST_MV" "SNA$"
USING (
SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */
"DLT$0"."DATUM" "GB0"
, "DLT$0"."HAUS" "GB1"
, SUM(DECODE("DLT$0"."DML$$", 'I', 1, -1)* DECODE(("DLT$0"."UMSATZ"), NULL, 0, 1)) "D0"
, SUM(DECODE("DLT$0"."DML$$", 'I', 1, -1)) "D1"
, NVL(SUM(DECODE("DLT$0"."DML$$", 'I', 1, -1)* ("DLT$0"."UMSATZ")), 0) "D2"
FROM (SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$
, "MAS$"."HAUS"
, "MAS$"."DATUM"
, "MAS$"."UMSATZ"
, DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$
FROM "TEST"."MLOG$_TEST_TAB" "MAS$"
WHERE "MAS$".SNAPTIME$$ > :1 ) AS OF SNAPSHOT (:2) "DLT$0"
GROUP BY "DLT$0"."DATUM","DLT$0"."HAUS")"AV$"
ON ( SYS_OP_MAP_NONNULL("SNA$"."DATUM")=SYS_OP_MAP_NONNULL("AV$"."GB0")
AND SYS_OP_MAP_NONNULL("SNA$"."HAUS")=SYS_OP_MAP_NONNULL("AV$"."GB1")
)
WHEN MATCHED THEN
UPDATE SET
"SNA$"."CNT_UMSATZ"="SNA$"."CNT_UMSATZ"+"AV$"."D0"
, "SNA$"."CNT"="SNA$"."CNT"+"AV$"."D1"
, "SNA$"."UMSATZ"= DECODE("SNA$"."CNT_UMSATZ"+"AV$"."D0",0,NULL,NVL("SNA$"."UMSATZ",0)
+"AV$"."D2")
DELETE WHERE ("SNA$"."CNT" = 0)
WHEN NOT MATCHED THEN INSERT (
"SNA$"."DATUM"
, "SNA$"."HAUS"
, "SNA$"."CNT_UMSATZ"
, "SNA$"."CNT"
, "SNA$"."UMSATZ")
VALUES (
"AV$"."GB0"
, "AV$"."GB1"
, "AV$"."D0"
, "AV$"."D1"
, DECODE ("AV$"."D0", 0, NULL, "AV$"."D2")) WHERE ("AV$"."D1" > 0)
Bemerkenswert an diesem MERGE finde ich:
- den (nicht dokumentierten) OPT_ESTIMATE-Hint, den Christo Kutrovsky gelegentlich näher erläutert hat.
- das "AS OF SNAPSHOT", zu dem ich im Netz nicht arg viel gefunden habe
- die (nicht dokumentierte) Funktion SYS_OP_MAP_NONNULL, zu der man z.B. in Eddie Awads Blog Informationen findet.
Die Suche nach den relevanten Schlüsselwörtern führt immer wieder zu den gleichen Webseiten, die ich hier im Blog auch schon früher verlinkt habe:
- David Aldridges Blog
- Alberto Dell'Eras Blog: mit dem Hinweis darauf, dass sich die Verfahren in 11.2 geändert haben
Möglicherweise komme ich in nächster Zeit mal wieder auf das Thema zurück.
Keine Kommentare:
Kommentar veröffentlichen