Materialized view

Using materialized views (materialized view) in Oracle Database allows you to simultaneously manage summary information in a data warehouse and speed up query execution.
Let's create a materialized view step by step:

Grant the necessary privileges.

-- create MV
grant create materialized view to ALEX;
— optimize queries for MV
grant query rewrite to ALEX;

2) Create test tables with data – stores and sales. The materialized view will provide the total sales for each store.

create table SHOPS(
  id_shop number(5) PRIMARY KEY,
  name_shop varchar2(45) not null
  );
create table SALES(
  id_shop number(5),
  id_good number(5),
  sales_good decimal(8,2)
  )
alter table SALES
add constraint PK_SALES primary key(id_shop, id_good)
add constraint FK_SHOPS_SALES foreign key (id_shop)
references SHOPS(ID_SHOP)

3) Create MV

CREATE MATERIALIZED VIEW MV_SALES
BUILD IMMEDIATE
REFRESH
COMPLETE
ON COMMIT
AS
select sh.id_shop, sum(sa.sales_good) 
       from SHOPS sh,
            SALES sa
       where sh.id_shop = sa.id_shop
       group by sh.id_shop;

BUILD IMMEDIATE populates the materialized view at the time of its creation (this is the default value). The alternative clause BUILD DEFERRED creates only the structure; the materialized view can be populated later using the DBMS_MVIEW package.
REFRESH specifies how Oracle updates the data in the materialized view. In the example provided, complete (COMPLETE) refreshes are the only option. Incremental (FAST ) refreshes are also possible, but they come with a number of limitations and caveats.
ON DEMAND – data is refreshed only when explicitly updated using the DBMS_MVIEW package. ON COMMIT – data is refreshed every time a transaction committing occurs for the underlying tables of the materialized view.
Update the optimizer statistics and the materialized view data (respectively).

dbms_stats.gather_table_stats(USER, 'MV_SALES') ;
dbms_mview.refresh('MV_SALES', 'A');

Check out:

SQL> select * from Alex.Mv_Sales
  2  /
 
ID_SHOP SUM(SA.SALES_GOOD)
------- ------------------
      1              41,15
      2              41,57
      3              66,51

Let's change data and come again:

SQL> update SALES
  2  set sales_good = sales_good + 100
  3  where id_shop = 1 and id_good = 1
  4  /
 
1 row updated
 
SQL> select * from Alex.Mv_Sales
  2  /
 
ID_SHOP SUM(SA.SALES_GOOD)
------- ------------------
      1             141,15
      2              41,57
      3              66,51

ceo@thomsonslegacy.com

We customize Oracle e-Business Suite R12 modules with Oracle Applications Framework and providing Oracle ADF based solutions

© Thomsonslegacy.com, 2025.
If you publish the materials of the site, the reference to the source is obligatory. The site uses cookies.