Oracle Materialized Views

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

Materialized View Log

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.

Privileges required

create materialized view
create any materialized view
drop any materialized view
delete any table
insert any table
lock any table
select any table
under any table
update any table
create table
create view

Syntax (Fast Refresh)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
BUILD IMMEDIATE
REFRESH <FAST | FORCE> ON <COMMIT | DEMAND>
<USING INDEX | USING NO INDEX>
INITRANS <integer>
STORAGE CLAUSE
AS (<SQL statement>);

Example:

CREATE MATERIALIZED VIEW mv_simple
TABLESPACE uwdata
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT * FROM servers;

Syntax (Force Refresh)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
BUILD IMMEDIATE
REFRESH <FAST | FORCE> ON <COMMIT | DEMAND> 
AS (<SQL statement>);

Example:

CREATE MATERIALIZED VIEW mv_force
TABLESPACE uwdata
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH ROWID AS
SELECT * FROM servers;

Syntax (Complete Refresh)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

Example:

CREATE MATERIALIZED VIEW mv_complete
TABLESPACE uwdata
REFRESH COMPLETESTART WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id, i.installstatus;

Syntax (Complete Refresh Using Index)

CREATE MATERIALIZED VIEW <schema.name>
[LOGGING] [CACHE]
PCTFREE <integer>
PCTUSED <integer>
USING INDEX
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

Example:

CREATE SNAPSHOT mv_w_index
LOGGING CACHE
PCTFREE 0 PCTUSED 99
TABLESPACE uwdata
REFRESH COMPLETE
AS SELECT s.srvr_id, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id;

Syntax (Prebuilt Table)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

Example:

CREATE TABLE mv_prebuilt (
month VARCHAR2(8),
state VARCHAR2(40),
sales NUMBER(10,2));

CREATE MATERIALIZED VIEW mv_prebuilt
ON PREBUILT TABLE WITH REDUCED PRECISION
AS SELECT t.calendar_month_desc AS month,
c.cust_state_province AS state,
SUM(s.amount_sold) AS sales
FROM times t, customers c, sales s
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
GROUP BY t.calendar_month_desc, c.cust_state_province;

Syntax (Enable Query Rewrite)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

Example:

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%rewrite%';

EXPLAIN PLAN FOR
SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
AND s.srvr_id = 502
GROUP BY s.srvr_id, i.installstatus;

SELECT * FROM TABLE(dbms_xplan.display);

CREATE MATERIALIZED VIEW mv_rewrite
TABLESPACE uwdata
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id, i.installstatus;

EXPLAIN PLAN FOR
SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
AND s.srvr_id = 502
GROUP BY s.srvr_id, i.installstatus;

SELECT * FROM TABLE(dbms_xplan.display);

-- if the base table may be updated then
ALTER SESSION SET query_rewrite_integrity = STALE_TOLERATED;

Materialized View Refresh and the ATOMIC_REFRESH parameter

When refreshing big materialized views in large data warehouses it is always good to check the parameter options available in the DBMS_MVIEW.REFRESH procedure.

Oracle changes the default parameters of its DBMS packages from release to release. I remember back at the times of Oracle 9i a complete refresh would truncate the materialized view, thus the only work that the database was actually doing in a complete refresh, was just an INSERT after the TRUNCATE.

Now in Oracle 10g and Oracle 11g parameters have changed. When there is a COMPLETE materialized view refresh, for the purposes of data preservation, a DELETE is done instead of a TRUNCATE! 

The reason for this is because Oracle "changed" the default parameter value of ATOMIC_REFRESH in the DBMS_MVIEW.REFRESH package. In earlier releases the parameter was set to FALSE by default but now it is set to TRUE, which forces a DELETE of the materialized view instead of  TRUNCATE, making the materialized view more "available" at refresh time.

But this DELETE is an expensive operation in terms of refresh time it takes. A DELETE is always expensive and sometimes even impossible when we are talking about the complete refresh of materialized views with millions of rows. At COMPLETE refresh a DELETE command takes long time, as it has to make the materialized view available to the users and comply with the ACID theory and its transactional reasons, but who cares? What if we really want to get the refresh done quickly and we don't care about the availability of the materialized view, say in a data warehouse maintenance window?

I have tested how the refresh time improves when you set the ATOMIC_REFRESH => FALSE in the procedure on a 1 CPU home computer with Oracle 11g. If you have more CPUs it will even be quicker because of the PARALLEL parameter.

I just wanted to show the improvement you get in refresh times when ATOMIC_REFRESH is FALSE and Oracle does a  TRUNCATE instead of a DELETE at complete refresh. A real time saver with big materialized views in data warehouses. Here is the test:

-- Enable parallel DML
ALTER SESSION FORCE PARALLEL DML

-- Cleanup
-- DROP TABLE BASE_TABLE;

-- Create the Test table
CREATE TABLE BASE_TABLE
  (
    id NUMBER(10) PRIMARY KEY NOT NULL,
    X  VARCHAR2(255),
    Y  VARCHAR2(255)
  )
  PARTITION BY RANGE
  (
    ID
  )
  INTERVAL
  (
    100000
  )
  (
    PARTITION P1 VALUES LESS THAN (100000),
    PARTITION P2 VALUES LESS THAN (200000)
  );

-- Make the test table paralllel
ALTER TABLE BASE_TABLE PARALLEL 5

Load the test table with data from a dummy cartesian join so that you get millions of rows. Do you like my loop? Loops only once. Well I had it there to add more millions if I wanted to :-) >

BEGIN
  FOR I IN 1 .. 1
  LOOP
    INSERT   /*+ PARALLEL(BASE_TABLE, 5) */    INTO BASE_TABLE SELECT ROWNUM, a.object_name, a.status  FROM all_objects a, emp b, dept c;
  END LOOP;
  COMMIT;
END;

3010392 rows inserted

Create a materialized view log for fast refresh

DROP MATERIALIZED VIEW LOG ON BASE_TABLE;
CREATE MATERIALIZED VIEW LOG ON BASE_TABLE;

Create the fast refresh materialized view

DROP MATERIALIZED VIEW MV_BASE_TABLE;
CREATE materialized VIEW mv_base_table parallel 5 refresh fast
AS
  SELECT * FROM BASE_TABLE;

Update the Test table to simulate changing data

UPDATE BASE_TABLE SET Y='INVALID';
COMMIT;

3010392 rows updated

Now you are ready to do the materialized view refresh with the ATOMIC_REFRESH values set to TRUE and then to FALSE. Observe the refresh times. Refresh the materialized view with the two different values in the 
ATOMIC_REFRESH parameter.

TRUE case with DELETE

EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_BASE_TABLE', METHOD => 'C', ATOMIC_REFRESH => TRUE);

Elapsed 558.8 seconds

Now is time to do the test with the ATOMIC_REFRESH parameter set to FALSE.

FALSE case with TRUNCATE 
Must update again to simulate change in the logs

UPDATE BASE_TABLE SET Y='VALID';
COMMIT;

EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_BASE_TABLE', METHOD => 'C', ATOMIC_REFRESH => FALSE);

Elapsed 215.3 seconds

Half the time! Well if you have real computers it will be much faster. So again we see DELETE is really not good when you work with millions of rows. Nice to have options!

SQL query to find Table Size

There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. Isn't it?

Anyway you can choose to save this query as a function for easy retrieval.
select
segment_name table_name, 
sum(bytes)/(1024*1024) table_size_meg
from user_extents
where segment_type='TABLE'
and segment_name = '&table_name'
group by segment_name;

Read more on what all to remember while getting the size of a table. Click here

Create your own function for the purpose:
CREATE OR REPLACE FUNCTION get_table_size
(t_table_name VARCHAR2)RETURN NUMBER IS
l_size NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024)
INTO l_size
FROM user_extents
WHERE segment_type='TABLE'
AND segment_name = t_table_name;

RETURN l_size;EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/

Example:
SELECT get_table_size('EMP') Table_Size from dual;

Result:
Table_Size
0.0625

--------------------------------
select owner                      
     , segment_name
     , sum(bytes)/1024/1024 as "MBytes"
from dba_segments
where owner='SCOTT'
  and segment_name='EMP'
group by owner
       , segment_name;

Steps to remove the locked database object

--get object id
SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'XX_RES_ALLOC_DM%';

select * from all_objects where object_id IN (323889) ;

--find locked or not , if found then find session id
select * from v$locked_object;

 WHERE object_id IN (1193375,1169419,1169514)  ;         

---from the session id(SID), find serail
select * from v$session where sid=3250;

---kill the session 'session id, serail '
alter system kill session (:sid, :SERIAL#) ;
alter system kill session '3120,9499' immediate;

Oracle Applications Tablespace Model - OATM

The Oracle Applications Tablespace Model was another long awaited feature that got introduced in 11.5.10.Prior to 11.5.10 by default each of the oracle applications product would have two dedicated tablespace holding the data element and the other for storing the index eg GLD (For General Ledger base tables) and GLX (For indexes relation to the General Ledger product).This easily resulted in some 300 odd tablespaces to manage apart from the system, temp and the rollback tablespaces.

In the new Oracle Applications Tablespace Model (OATM) all these product related tablespaces have been consolidated in two main tablespaces one for holding the base tables and the other for holding the related indexes. Apart from these two tablespace you have an additional ten tablespaces including system tablespace undo tablespace and the temporary tablespace. Thereby reducing the total number of tablespace in the OATM to twelve.

Apart from the obvious ease of management and administration with a reduced number of tablespace being involved the OATM also provides benefits like efficient space utilization. This is achieved by supporting locally managed tablespaces as opposed to the dictionary managed tablespace in the previous model.

OATM also supports uniform extent allocation and auto allocate extent management. In uniform extent management all the extents have the same size and result in less fragmentation. Auto allocate extent management allows the system to determine the extent sizes automatically.

OATM also provides additional benefits when implementing Real Application Clusters (RAC) in Oracle Applications.

Under the OATM the following twelve tablespaces are created as a default.
• APPS_TS_TX_DATA - This tablespace hold the translational tables of all Oracle Applications products. For example the GL_JE_HEADERS will be a part of APPS_TX_DATA. 
• APPS_TS_TX_IDX - All the indexes on the product tables are kept under this tablespace. 
• APPS_TS_SEED - The seeded data that is setup and reference data tables and indexes form this tablespace. For example your FND_DATABASES table would reside in the APPS_TS_SEED tablespace. 
• APPS_TS_INTERFACE - All the interface tables are kept in this tablespace for example the GL_INTERFACE table. 
• APPS_TS_SUMMARY - All objects that record summary information are grouped under this tablespace. 
• APPS_TS_NOLOGGING - This tablespace contains the materialized views that are not used for summary purposes and other temporary 
object that do not require redo log entries. 
• APPS_TS_QUEUES - With the support for advanced queuing in Oracle Applications, the advanced queue tables and related objects form a part of this tablespace. 
• APPS_TS_MEDIA - This tablespace holds multimedia objects like graphics sound recordings and spital data. 
• APPS_TS_ARCHIVE - Tables that are obsolete in the current release of Oracle Applications 11i are stored here. These tables are preserved to maintain backward compatibility of custom programs or migration scripts. 
• UNDO - The undo tablespace is used as automatic undo management is enabled by default in 11.5.10.This acts as a replacement to red log files. 
• TEMP - The Temp tablespace is the default temporary tablespace for Oracle Applications. 
• SYSTEM - This is the SYSTEM tablespace used by the Oracle Database.

For all new installation of 11.5.10 OATM is available as a default install. For prior applications system you can migrate your existing tables spaces to the oracle applications tablespace model.

For this purpose you have the Tablespace Migration Utility(TMU) which is available as a patch (3381489).