Materialized View with refresh fast Example

At first create a Table as basis for the Materialized view:


create table matviewtab as
with resNmb(iNmb) as
(select 1 as iNmb from dual
union all
select iNmb + 1 from resNmb where iNmb < 1005 ) select iNmb,'SomeText' as num_ber from resNmb;

Add a primary Key.

alter table matviewtab add constraint matviewtab_pk primary key (iNmb);

And create a Materialized View Log for capturing the operations on the basis-table.

CREATE MATERIALIZED VIEW LOG ON matviewtab WITH PRIMARY KEY, ROWID;

Then lets create the Materialized View with fast refresh option on commit:

CREATE MATERIALIZED VIEW matviewview
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * from matviewtab;

Check the content:

select * from matviewview order by inmb desc;

INMB NUM_BER
1005 SomeText
1004 SomeText
1003 SomeText
1002 SomeText
1001 SomeText
1000 SomeText
999 SomeText
998 SomeText
997 SomeText
996 SomeText

Insert a row and commit and check the content again:

insert into matviewtab values(1006,'OtherTxt');
commit;


select * from (select * from matviewview order by inmb desc) where rownum < 11 order by inmb desc;

INMB NUM_BER
1006 OtherTxt
1005 SomeText
1004 SomeText
1003 SomeText
1002 SomeText
1001 SomeText
1000 SomeText
999 SomeText
998 SomeText
997 SomeText

Insert another Row and check for changes in the DB-Objects user_tab_modifications and MLOG$_matviewtab (MLOG$_<>):

insert into matviewtab values(1007,'OtherTxt');


select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS
BIN$TPmrAsfFAN7gU6wZ4BSJ9w==$0     2 0 0 12.04.2017 16:00:54 NO 0


select * from MLOG$_matviewtab;

INMB M_ROW$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$
1007 AAFaYQAQAAAAAA9AAB 01.01.4000 00:00:00 I N FE 3096379362658352


commit;


select * from (select * from matviewview order by inmb desc) where rownum < 11 order by inmb desc;

INMB NUM_BER
1007 OtherTxt
1006 OtherTxt
1005 SomeText
1004 SomeText
1003 SomeText
1002 SomeText
1001 SomeText
1000 SomeText
999 SomeText
998 SomeText


drop MATERIALIZED VIEW LOG on matviewtab;
drop MATERIALIZED VIEW matviewview;
drop table matviewtab;

Additional Infos:
Materialized Views: how can I find the number of updates, inserts, and deletes applied during refresh?

SQL: Densification or Condensing of Date Ranges or History

— Verdichten/Densification von Zeitranges mit Berücksichtigung der Reihenfolge

Condensing/Densification or Merging of Date Ranges in one table with considering the chronology.
This is necessary if the Table is a subset of columns of the original table
There can be changes in attributes which are not in the table to condense causing the generation of new versions.
We now need to condense or merge the date ranges so we get as a result for every change in our table only one row.

/*
Verdichtet eine Tabelle mit BK und einer History,
falls UNMITTELBAR AUFEINANDER FOLGEND gleiche Attributsauspraegungen vorhanden sind.
Diese fallen dann zusammen.
—-
This logic condenses a table concerning consecutively history-rows with the same attributes.
These Rows are combined.
History-holes are considered!
*/

–drop table CONDENSING_TEST purge;

CREATE TABLE CONDENSING_TEST
(
H_KONTO_S_CID NUMBER, — = Primary key
H_KONTO_CID NUMBER, — = Businesskey
C_START_DT DATE,
C_END_DT DATE,
TESTKONTO_FLG NUMBER,
SONDERKONTO_FLG NUMBER,
EXISTIERT_FLG NUMBER,
BEZUG_FLG NUMBER,
BARZAHLUNG_FLG NUMBER
)
TABLESPACE CORE_DTA;


SET DEFINE OFF;
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('28.06.1984 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('06.02.2003 11:49:47','DD.MM.RRRR HH24:MI:SS'), '1','-1','1','1','1');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('06.02.2003 11:49:48','DD.MM.RRRR HH24:MI:SS'),to_date('13.08.2004 14:23:09','DD.MM.RRRR HH24:MI:SS'), '2','-1','1','1','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43482',to_date('03.03.1994 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.2009 23:59:59','DD.MM.RRRR HH24:MI:SS'), '3','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43482',to_date('03.03.1994 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.2009 23:59:59','DD.MM.RRRR HH24:MI:SS'), '4','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('13.08.2004 14:23:10','DD.MM.RRRR HH24:MI:SS'),to_date('13.08.2004 14:23:24','DD.MM.RRRR HH24:MI:SS'), '5','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('13.08.2004 14:23:25','DD.MM.RRRR HH24:MI:SS'),to_date('14.03.2005 09:57:42','DD.MM.RRRR HH24:MI:SS'), '6','-1','1','1','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('14.03.2005 09:57:43','DD.MM.RRRR HH24:MI:SS'),to_date('07.10.2005 09:59:31','DD.MM.RRRR HH24:MI:SS'), '7','-1','1','1','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('07.10.2005 09:59:32','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.9999 23:59:59','DD.MM.RRRR HH24:MI:SS'), '8','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43482',to_date('01.01.2010 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.9999 23:59:59','DD.MM.RRRR HH24:MI:SS'), '9','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43482',to_date('01.01.2010 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.9999 23:59:59','DD.MM.RRRR HH24:MI:SS'),'10','-1','1','0','0');
commit;

/*
Beruecksichtigte beschreibende Attribute:
Considered descriptive Attributes:
-TESTKONTO_FLG
-SONDERKONTO_FLG
-EXISTIERT_FLG
-BEZUG_FLG
-BARZAHLUNG_FLG
*/
select H_KONTO_CID
,C_START_DT
,C_END_DT
,TESTKONTO_FLG
,SONDERKONTO_FLG
,EXISTIERT_FLG
,BEZUG_FLG
,BARZAHLUNG_FLG
from condensing_test
order by h_konto_cid,C_START_DT;

H_KONTO_CID C_START_DT C_END_DT TESTKONTO _FLG SONDERKONTO _FLG EXISTIERT _FLG BEZUG _FLG BARZAHLUNG _FLG COMMENT
43481 28.06.1984 00:00:00 06.02.2003 11:49:47 0 -1 1 1 1
43481 06.02.2003 11:49:48 13.08.2004 14:23:09 0 -1 1 1 0
43481 13.08.2004 14:23:10 13.08.2004 14:23:24 0 -1 1 0 0
43481 13.08.2004 14:23:25 14.03.2005 09:57:42 0 -1 1 1 0 Row 4/5 have to condense
43481 14.03.2005 09:57:43 07.10.2005 09:59:31 0 -1 1 1 0 Row 4/5 have to condense
43481 07.10.2005 09:59:32 31.12.9999 23:59:59 0 -1 1 0 0
43482 03.03.1994 00:00:00 31.12.2009 23:59:59 0 -1 1 0 0 Row 7/8/9/10 have to condense
43482 03.03.1994 00:00:00 31.12.2009 23:59:59 0 -1 1 0 0 Row 7/8/9/10 have to condense
43482 01.01.2010 00:00:00 31.12.9999 23:59:59 0 -1 1 0 0 Row 7/8/9/10 have to condense
43482 01.01.2010 00:00:00 31.12.9999 23:59:59 0 -1 1 0 0 Row 7/8/9/10 have to condense

— H_KONTO_CID/HUB_CID entspricht dem Businesskey aus der Business-View
— H_KONTO_CID/HUB_CID correlates to the Businesskey, coming from the Business-View-Logic

with res01 as
(select H_KONTO_CID as hub_cid
,C_START_DT as C_START_DT
,C_END_DT as C_END_DT
,testkonto_flg as attrib1
,lag ( testkonto_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib1_LAG
,lead( testkonto_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib1_LEAD
,sonderkonto_flg as attrib2
,lag ( sonderkonto_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib2_LAG
,lead( sonderkonto_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib2_LEAD
,existiert_flg as attrib3
,lag ( existiert_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib3_LAG
,lead( existiert_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib3_LEAD
,bezug_flg as attrib4
,lag ( bezug_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib4_LAG
,lead( bezug_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib4_LEAD
,barzahlung_flg as attrib5
,lag ( barzahlung_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib5_LAG
,lead( barzahlung_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib5_LEAD
from condensing_test
where h_konto_cid in (43481,43482)
)
,
res02 as
(-- nimm die Zeile, wenn aktuelle Auspraegung nicht der Vorgaengerversion entspricht
-- take the row if current occurrence not equal to the previous occurrence
select HUB_CID
,C_START_DT as CHANGE
,attrib1
,attrib2
,attrib3
,attrib4
,attrib5
,1 as TAKEROW
from res01
where (attrib1_LAG != attrib1 or attrib1_LAG is null)
or (attrib2_LAG != attrib2 or attrib2_LAG is null)
or (attrib3_LAG != attrib3 or attrib3_LAG is null)
or (attrib4_LAG != attrib4 or attrib4_LAG is null)
or (attrib5_LAG != attrib5 or attrib5_LAG is null)
union all
-- nimm die Zeile NICHT, wenn sich danach was geaendert hat, d.h.
-- wenn aktuelle Auspraegung nicht der Nachfolgeversion entspricht
-- don't take the row, if current occurrence is not equals to the previous occurrence
select HUB_CID
,C_END_DT as CHANGE
,attrib1
,attrib2
,attrib3
,attrib4
,attrib5
,0 as TAKEROW
from res01
where (attrib1_LEAD != attrib1 or attrib1_LEAD is null)
or (attrib2_LEAD != attrib2 or attrib2_LEAD is null)
or (attrib3_LEAD != attrib3 or attrib3_LEAD is null)
or (attrib4_LEAD != attrib4 or attrib4_LEAD is null)
or (attrib5_LEAD != attrib5 or attrib5_LEAD is null)
)
,
res03 as
(
select HUB_CID
,CHANGE as C_START_DT
,lead( CHANGE, 1 ) over (partition by HUB_CID order by CHANGE) as C_END_DT --HUB_CID anstatt TABLE_CID
,attrib1
,attrib2
,attrib3
,attrib4
,attrib5
,TAKEROW
from res02)
select HUB_CID as H_KONTO_CID
,C_START_DT as C_START_DT
,C_END_DT as C_END_DT
,0 AS c_audit_cid
,attrib1 as testkonto_flg
,attrib2 as sonderkonto_flg
,attrib3 as existiert_flg
,attrib4 as bezug_flg
,attrib5 as barzahlung_flg
from res03
where TAKEROW = 1
order by HUB_CID, C_START_DT;

EXECUTE IMMEDIATE with USING and OUT Parameter

If you want to use an OUT – Parameter in an EXECUTE IMMEDIATE – Statement including an anonymous PL/SQL-Block: here is an example:

create table in_out_test ( num_ber number);


set serveroutput on;
DECLARE
vc_sql CLOB;
p_insert_count NUMBER;
BEGIN
vc_sql := '
DECLARE
cursor curData is
with resNmb(iNmb) as
(select 1 as iNmb from dual
union all
select iNmb + 1 from resNmb where iNmb < 1005
)
select iNmb as num_ber from resNmb;
type typeTblData is table of curData%ROWTYPE;
tblData typeTblData;
iterator NUMBER := 0;
insert_count NUMBER;
begin
open curData;
loop
fetch curData bulk collect into tblData limit 100;
exit when tblData.COUNT = 0;
forall ii in tblData.FIRST..tblData.LAST
INSERT INTO in_out_test
values tblData(ii) ;
insert_count := (iterator*100)+Sql%Rowcount;
:1 := insert_count;
commit;
iterator := iterator +1;
end loop;
close curData;
exception
when others then
rollback;
if curData%ISOPEN then close curData; end if;
raise;
end;
';
execute immediate vc_sql using out p_insert_count;
dbms_output.put_line('IN OUT PARAMETER: '||p_insert_count||' rows inserted');
END;
/

>> PL/SQL procedure successfully completed.
>> IN OUT PARAMETER: 1005 rows inserted

drop table in_out_test purge;