- In 12.1.3 where we create a materialized views with simple CREATE statement but in 12.2.x, we need to do below steps –
- Create a logical view
- Use ad_zd_mview upgrade script to create a materialized view.
- Oracle internally creates required edition materialized view.
e.g.
- Create a logical view. Basically, create a normal view but suffixed by the #
CREATE OR REPLACE VIEW APPS.XYZ_VIEW_NAME# AS
<query>;
- Upgrade to materialized view. The first parameter is the schema name and second is the view name without #
BEGIN
AD_ZD_MVIEW.UPGRADE('APPS', 'XYZ_VIEW_NAME');
END;
- Verify all components
SELECT * FROM dba_objects WHERE object_name LIKE 'XYZ_VIEW_NAME%';
You should see below 3 components
- XYZ_VIEW_NAME# : LOGICAL VIEW
- XYZ_VIEW_NAME : TABLE
- XYZ_VIEW_NAME : MATERIALIZED VIEW
To access the materialized view just query on XYZ_VIEW_NAME (without # suffix)
SELECT * FROM XYZ_VIEW_NAME;