Dienstag, März 20, 2012

Materialized View Fast Refresh

In den letzten Jahren habe ich Materialized Views nur sehr sporadisch eingesetzt, obwohl ich sie für ein ausgesprochen nützliches Hilfsmittel halte. Da ich mir gar nicht mehr sicher bin, ob meine Erinnerungen noch so ganz zur Realität passen, hier ein kleiner Test in 11.1.0.7. Ausgangspunkt sei eine partitionierte Tabelle mit 12M Sätzen verteilt auf 12 Monate:

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:
Möglicherweise komme ich in nächster Zeit mal wieder auf das Thema zurück.

Keine Kommentare:

Kommentar veröffentlichen