Data warehouse Design Using Oracle

Created Feb. 7, 2024 by Girija Narasimhan

Unit Objective

This unit covers materialized view creation and alteration, various refresh method. It also covers mode of materialized view , build method of materialized view, status and comments of materialized view.

2.1 Introduction

Using materialized views, it is possible to pre calculate complex joins and execute the aggregate operation i.e summary data and also stores the result set of the table in the database. In future the end users may query the table and views the detail information. The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables. This technique improves the query execution time and performance.

2.2 Base Table vs. View Table

Before creating view or materialized view -create and insert values in the base table. Here Item table is a base table.

create table item(itemno number(3) primary key ,iname varchar2(10),price number(4));

insert into item values(1,'milk',20);

insert into item values(2,'Bread',30);

insert into item values(3,'Juice',40);

Base table vs. View

A view is a logical entity or virtual table. The view is attached with SQL statement or query, which will store in the database in the system table space. It is used as similar like database table or base table (i.e item table). Whenever query is fired against the database table or base table (i.e. item table), The view “v” execute the stored SQL statement ( i.e. select * from item) and creates a temporary table in the memory. It do not stores the query result, it only execute and return the result /output. It means it will store only query not result.

Both base table and view table are identical, since both the base table and view table “Rowid’s” are same. Rowid is the physical address for each record in the database and it is a fixed-length binary data. So, it returns the same value and also any one table is updated the other table automatically returns the exact same result.

Rowid of Base table and View table

2.3 View vs. Materialized View (MV)

A materialized view is a database object. It is physically stored in the database. Because materialized view tables are having local copies of data suppose base table is located remotely. It is also useful to create summary table based on aggregation of the base table data. The materialized view stores both query and the result of a query.

creating mv

Whenever update the base table (item table), the view also automatically update the change. Otherwise update the view (“v”), the base table (item table) also automatically modifies the changes. For example after the update, the view data matches the table data but the materialized view data does not.

base table matches with view table

If you update or modify view table it will automatically reflects the base table, as earlier explained because both the rowid's are same.

View table matches base table

The reason for materialized view is not reflecting the update of base table like view is, materialized view storing the copy of data in a separate physically place in the database. The given below table clearly shows base table rowid and materialized view rowid are not same.

Different Rowid of Base table and MV table

Then solution for synchronize base table and Materialized view is refresh the materialized view.

refresh

2.4 Mode of Refresh Technique

Mode of Refresh

There is two mode of refresh technique is available, one is “ON DEMAND” and other one is “ON COMMIT”.

Refresh on demand

For matching updating of data in materialized view table (mv) and base table (item), the refresh procedures are available in the DBMS_MVIEW package. When execute the refresh procedure, the materialized view table (mv) synchronized with base table (item)

DBMS_MVIEW

The other method is called “ON COMMIT”, Unlike manual refresh method, it doesn’t need “DBMS_MVIEW.REFRESH” to execute.

on commit

Insert into item values(5,’rice’)

Commit;

on commit display

It automatically refreshed, i.e it is synchronized with base table data just by “COMMIT” statement.

2.5 Refresh option

Refresh option

2.5.1 Refresh Complete

create materialized view mv2 refresh complete as select * from item;

refresh complete

When a refresh complete occurs in the materialized view's defining query is executed and the complete result set replaces the data currently existed in the materialized view. As a result, it completely re-creates the result set of the materialized view. The rowids after execute differs from the before execute, even though the data in base table ITEM was unchanged.

execute DBMS_MVIEW.REFRESH(LIST => 'MV2', METHOD => 'C' );

The "list" parameter accepts a list of materialized views to refresh (i.e “mv2”) and the "method" parameter accepts a "C", for Complete refresh. (i.e ‘C’ means Complete refresh)

The limitation of refresh complete is , suppose if a materialized view have many rows and the base tables values are changed infrequently then refresh complete is time-consuming method or over slow.

Another issue is due to bad connections sometimes the refresh completes never finishing their process.

2.5.1.1. Refresh complete using refresh option "F"

Even use “Refresh complete” in the materialized view creation, even though call the method “F” it will do “refresh fast”.

The notable point is “Rowid” before and after executing refresh is not changed.

execute DBMS_MVIEW.REFRESH(LIST => 'MV2', METHOD =>'F' );

Refresh complete using "F" refresh option

Even use “Refresh complete” in the materialized view creation, even though call the method “F” it will do “refresh fast”. The notable point is “Rowid” before and after executing refresh is not changed.

2.6 Refresh Fast

REFRESH FAST clause used in the CREATE MATERIALIZED VIEW command tells the oracle suppose no need to mention refresh option in the execution.

create materialized view MV refresh fast as select * from item;

Execute dbms_mview.refresh( 'MV' );

The above statement is not mentioning any “method =>’F’, but create materialized view “mv” is using refresh fast. So, the default refresh method in the execute statement refresh fast only.

Refresh Fast

The benefit of using REFRESH FAST is, it don’t create entire new result set using new rowid like REFRESH COMPLETE. The value was updated in the materialized view without changing the “Rowid”.

2.7 Refresh Force

REFRESH FORCE” clause to decide which performance is most appropriate for query rewrite using DML operation either refresh fast or refresh complete based on the Operation. Do update operation in the materialized view and then execute it. Now it changes the rowid; here refresh force performs the refresh complete method.

Refresh Force Method

Now execute the Update operation and check the Rowid, Rowid is not changing. Then refresh force is performing the “Refresh fast here.

Refresh Force using Refresh fast

2.8 Never Refresh Using ALTER Refresh Complete

Oracle Database will ignore any REFRESH statement on the materialized view and prevents any refresh method such as FAST or COMPLETE by using NEVER REFRESH in create materialized view statement.

never refresh using refresh complete

Alter the materialized view using refresh complete and then execute the materialized view. then, it don’t give error message.

never refresh alter refresh complete

2.9 Never Refresh Using ALTER Refresh Fast

Instead of altering materialized view using refresh complete, alter the materialized view using refresh fast. Then try any DML operations, it gives the error. The reason is, it alters the materialized view as “read-only" type. So, it doesn’t allow any DML operation. In this case, call “refresh complete” for execution. Complete refresh option creates the entirely new set.

alter mv refresh fast

2.10 Never Refresh using Alter Refresh Force

alter refresh force

Alter the materialized view from never fresh to refresh force, now execute the update operation. In the execute statement, the method “?” means refresh force. But refresh force applies only refresh complete method, not refresh fast. It shows in the result set that “rowid” was changed.

2.11 Create Materialized view Log MLOG$

Complete refresh is time-consuming and occupying storage space because every execution it creates new result set. These drawbacks are rectified by applying Materialized View Log. The materialized view captures only the changed rows occur in the base table, it don’t captures the entire value. This technique is called Materialized View Log and “incremental" refreshing. Usually, a Materialized View Log takes lesser time than a complete refresh

create mlog$

In this materialized view log statement there is no need to mention materialized view name. For example in the previous materialized view method name of the materialized view is mandatory like “mv”, “mv2" . Every Base table has only one materialized view log object. The log file automatically created while executing the "create materialized log ...statement". Once materialized view log was created, it automatically generates a log file called MLOG$_<base table>. Every materialized view log has "MLOG$_" is a prefix followed by base table name i.e "MLOG$_ITEM". Here ITEM is a base table given in "create materialized log on "- after "on" is base table name.

describe mlog$

The MLOG$_item.itemno column copy the base table’s primary key column item.itemno. The MLOG$ also uses in the primary key column data type and size is as same as base table item i.e data type is NUMBER(3). The other MLOG$ columns are system generated for example SNAPTIME$$, DMLTYPE$$, OLDNEW$$, CHANGE_VECTOR$$.

2.12 Materialized view Log DMLTYPE$$

Materialized view log is initially empty.

DMLTYPE$$

Rows are automatically added to MLOG$_ITEM when base table ITEM is executed by any DML operation such that UPDATE, DELETE or INSERT.

The result of the MLOG$_item, shows number of changes occur in the base table. Here “U” – update operation in itemno 2 and “I”- insert operation occurred in itemno 4 in the base table item. "D" for delete operation in dmltype$$. Suppose, if rollback all the DML operation executed in the base table, the MLOG$_ITEM is again empty. The changes are not committed in the base table item.

rollback ;

select itemno, dmltype$$ from MLOG$_item;

no rows selected

The materialized view log is using “on commit” by default. That is a reason it don’t need execute any “DBMS_MVIEW.REFRESH” procedure to initiate the refresh in the materialized view.

2.13 Materialized view Log WITH Clause summary

mlog$ with

2.13.1 Materialized view Log WITH Clause Primary Key

Any way by default, the MLOG$ includes the primary key of the base table. To include the base table's primary key column in a materialized view log the WITH PRIMARY KEY clause can be specified.

Create materialized view log on item WITH PRIMARY KEY

Create materialized view log on item

with pk

2.13.2 Materialized view Log WITH Clause ROWID

ROWID to be specified in the materialized view log using “with” clause . Using “WITH” clause include the ROWID in the materialized log view, instead of primary key column ITEMNO it is substituted by M_ROW$$ column.

with rowid

It is also possible to include both rowid and primary key in MLOG$ table, by using WITH clause,

with rowid, pk

Another alternative method for include rowid and primary in the MLOG$ table without dropping the existing MLOG$ table. In other words, say that there is no need to recreate MLOG$ table.

add rowid

2.13.3 Materialized view Log WITH Clause COLUMN NAME

The WITH clause can also contain a list of specific base table columns. For example include the Iname column from the base table. Here, it will use data type and size of column is as mentioned in the base table such as “VARCHAR2(10)”.

with column name

2.13.4 Materialized view Log WITH Clause SEQUENCE$$

Using “WITH SEQUENCE” in the materialized view log statement includes SEQUENCE$$ column in MLOG$ table.

sequence$$

This SEQUENCE$$ column is help oracle to keep storing exact ordering information of mixed combination of DML operations used in the multiple base table. For example insert, update and delete operations are executed on multiple base tables in different order.

with sequence$$

2.13.5 Materialized view Log OLD_NEW$$

The OLD_NEW$$ column in the MLOG$ stores the values before updating the base table value.

It is helpful in future for verifying the previous value in the same column and also indicates the base table values were refreshed.

old_new$$

2.13.6 Materialized view Log INCLUDING NEW VALUES

By default the materialized view log exclude the new value. Specify EXCLUDING to disable the recording of new values in the log. In some situations, it helps to identify both the old value and the new value explicitly saved in the materialized view log. INCLUDING NEW VALUES tells the oracle database to save old and new values in OLD_NEW$$ column for update DML operations in the materialized view log.

new values

There is no need to store the new value for an update because it can be derived by applying the change vector (a RAW value stored in CHANGE_VECTOR$$, which Oracle uses internally during refreshes) to the old value.

2.14 COMMENTS ON Materialized view

It is possible to add comment in materialized view. This comment statement added into the data dictionary for the already existing materialized view table. The comment table has three columns in the data dictionary regarding the materialized view:

Owner - owner of the materialized view

Mview_name - materialized view name

Comments -Comment on the materialized view

SQL> comment on materialized view mv is 'ITEM Information';

Comment created.

SELECT MVIEW_NAME, COMMENTS FROM USER_MVIEW_COMMENTS WHERE MVIEW_NAME = 'MV';

MVIEW_NAME COMMENTS

------------------------------------------------

MV ITEM Information

There is three types of comment view are available in the data dictionary for materialized view.

•USER_MVIEW_COMMENTS display the current user comments on the materialized views. This view does not display the OWNER column. It will display only Mview_name, comments column.

•DBA_MVIEW_COMMENTS displays the database comments on the materialized views. It will display all the three columns such as owner, mview_name, comments.

•ALL_MVIEW_COMMENTS displays all the current user comments on the materialized view. It will display all the three columns such as owner, mview_name, comments.

SELECT OWNER, MVIEW_NAME, COMMENTS FROM ALL_MVIEW_COMMENTS;

OWNER MVIEW_ NAME COMMENTS

----------------------------------------------------------

SCOTT MV1 Snapshot table for snapshot SCOTT.MV1

SCOTT MV ITEM Information

SCOTT MV2 ITEM DESCRIPTION

2.14.1 Drop comments on materialized view

Comment on materialized view <name of the materialized view> is ‘< within single code give blank space>‘;

SQL>comment on materialized view mv is ' ';

2.15 Materialized view Using “For Update”

By default materialized view tables are read-only. Suppose tried with DML operation in the materialized view table causes the error message.

for update error

Suppose materialized view is created using FOR UPDATE clause, then it enables DML operations directly in the materialized view. This type of materialized view is called updatable materialized view.

SQL> drop materialized view mv;

SQL> create materialized view mv for update as select itemno,iname from item;

base table vs. updatble

The data in the materialized view table “mv” and base table “item” are identical. Now insert one record only in materialized view not in the base table “item”

for update

The materialized view table “mv” is not matching with “item” base table. Because materialized view stores the copy of the result i.e ROWID is different than ROWID of the item table. So, it is not matching with base table result. To overcome this difficulty, it is necessary to execute refresh method in materialized view.

2.16 Materialized view Using Aggregate Function

In the data warehousing environment the materialized views normally include aggregates. In the materialized view containing aggregates is possible after any type of DML to the base tables. In this example using MIN, MAX, SUM, AVG, COUNT aggregate function.

aggregate function

2.17 Join Materialized view

The Join has two tables. In the example used two tables namely Item and Supplier. The row in the one table always match with row of the another table. Here, no data will be lost i.e it is called lossless join. The table should have Primary key, Foreign Key and Not Null constraints on appropriate Join keys.

join key

2.18 Materialized view Status

Whenever materialized view and base table data’s are synchronized then it is considered that materialized view has fresh data otherwise it has the stale data (out-of-date data). For knowing the status of materialized view data, there is three types of view are available namely DBA_MVEIWS, ALL_MVIEWS, and USER_MVIEWS in the data dictionary. In the data dictionary the columns of the all the three views such as MVIEW_NAEM, STALNESS, LAST_REF, COMPILE_STATE are status are maintain automatically.

mv status

The STALENESS column display any one value like FRESH, STALE, UNUSABLE, UNKNOWN, UNDEFINED or NEEDS_COMPILE. Whenever NEEDS_COMPILE in these views shows NEEDS_COMPILE OR VALID status. Suppose the STALENESS column value has NEEDS_COMPILE then issue following alter materialized view statement for compile.

compile mv

2.19 Materialized view Build Methods

Mv Build method

For creating materialized view, there is two build methods are used in the materialized view. One is BUILD IMMEDIATE and another one is BUILD DEFERRED. The BUILD IMMEDIATE method, it creates the materialized view and adding the definition of the materialized view into the schema object in the data dictionary. And also search the base table according to the query given in the materialized view (i.e SELECT statement) and stores their result in the materialized view.

Build deferred

The build method clause is BUILD DEFERRED, it just creates the materialized view but it don’ search and store the result in the materialized view.

This method disables the query rewrite, so first time materialized view is executed by refresh complete method “C” using DBMS_MVIEW_REFRESH statement and then it automatically it will enable the materialized view by specifying the ENABLE QUERY REWRITE clause.

Enable query rewrite

Unit Summary

At the end of the unit the readers can understand difference between view and materialized view. Not only how to create materialized view and also know practically how to alter and various refresh options. It also explain various mode and build method, comments and status of the materialized view.

Practical Exercise

Create appropriate materialized view statement for the given below phone_plan table

Plan_id	Plan_name	Plan_amount	Expiry date	No_of_sms_free
11	Group members	1000	10-oct-2017	50
12	Monthly gold plan	1500	12-jan-2017	60
13	SMS users	800	10-apr-2017	100
14	Student plan	300	5-aug-2017	60

1. Create materialized view "pplan_mv" to display plan_id, plan_name, plan_amount, No_of_sms_free. Change plan_id=14 amount as “ 3000" in the phone_ plan table. Write appropriate statement, the phone_plan table modification reflects in the "pplan_mv".

2. Write appropriate statement to insert below information only in "pplan_mv1" materialized view not in phone_plan table.

Plan_id	Plan_name	Plan_amount	Expiry date	No_of_sms_free
15	Sliver plan	400	14-oct-2017	45

3. Create materialized view "pplan_mv2" using refresh complete and using phone_plan table, change plan_amount=900 in the plan_id=11 and use appropriate refresh option in the "pplan_mv2" after refresh also rowid should be same for plan_id=11.

4. Insert comment "this is my second mv" in the "pplan_mv2" and delete "pplan_mv"

5. Create materialized view log on phone_plan to display row_id, plan_id, plan_name, old and new value after updating No_of_sms_free =150 in plan_id=13. [hint: both create and display statement]

6. Create materialized view "pplan_mv3" using appropriate build method which don't have result set.

7. Display the status of all the materialized view created earlier and change pplan_mv3 status should be "valid" in the compile_state column.

8. Display the highest No_of_sms_free, plan_id, plan_name in "pplan_mv5" and delete the comment in pplan_mv2