On 12th of April the next stable Release of SQLDEVELOPER is available
Infos about the new Features included.
A lot of Basic Infos about SQLDeveloper can be found by Jeff Smith
On 12th of April the next stable Release of SQLDEVELOPER is available
Infos about the new Features included.
A lot of Basic Infos about SQLDeveloper can be found by Jeff Smith
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?
— 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;
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;