Tag Archives: DDL

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?

Foreign Key Constraints in a ORACLE DWH

There are several possibilities to have Foreign Key Constraints in a ORACLE-DWH-Environment.

ENABLE VALIDATE
ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)
ENABLE VALIDATE;

Enabled and validated means on every commit the constraint is validated to be correct.

ENABLE NOVALIDATE
ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)
ENABLE NOVALIDATE;

You can use this option if the tables are not correct corresponding to the constraint, but you need to have a enforced constraint for future inserts.
That means the current state of the existing data is not validated but you can’t insert data in the child-table without having a parent-key in the parent table.
That means that the constraint is checked on inserts/updates/deletes.

RELY DISABLE NOVALIDATE
ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)
RELY DISABLE NOVALIDATE;

This constraint is NOT used for data validation. If you know data is correct by running the ETLs than you can use this option.
DWH-Tools can use this info for retrieving information on this constraint. And advanced query-rewriting can be possible for materialized views.

  • No data validation -> no cpu-consumption.
  • No data validation -> inexpensive and no overhead on running dml.

More Infos can be found in the
ORACLE Database Data Warehousing Guide 11g or
ORACLE Database Data Warehousing Guide 12c.