Fast Refreshable Materialized View

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query


Simple Materialized View
Appropriate for Materialized View’s where the base tables have a small amount of changes over a period of time compared to the total number of rows in the table.
1. Create the Materialized View log.
2. Create the Materialized View as Fast Refreshable.

A Fast Refreshable Materialized View requires a Materialized View Log on the base table(s).
SQL> create materialized view log on experiment_runs;

select object_name, object_type
from user_objects where object_name like '%EXPERIMENT%';

RUPD$_EXPERIMENT_RUNS    TABLE
MLOG$_EXPERIMENT_RUNS    TABLE

create materialized view EXPERIMENTS_MV
refresh
with primary key
fast
on commit
as
select EXP_RUNID, USERNAME
from EXPERIMENT_RUNS
where SUCCESS = 'Y'
order by USERNAME;

select object_name, object_type
from user_objects where object_name like '%EXPERIMENT%';

RUPD$_EXPERIMENT_RUNS    TABLE
MLOG$_EXPERIMENT_RUNS    TABLE
EXPERIMENTS_MV        MATERIALIZED VIEW
EXPERIMENTS_MV        TABLE
Primary Key VS. ROWID
SQL> create materialized view log on experiment_runs with primary key; (This is the default).
SQL> create materialized view log on experiment_runs with rowid; (Required if there is no PK on base table).

The Primary Key parameter also applies to the Materialized View itself. The ‘with primary key’ parameter is the default option and if no primary key exists, the Materialized View must include the ‘with rowed’ parameter.

When you use create a Primary Key-Fast Refreshable Materialized View, the Primary Key MUST be part of the Materialized View select clause.

Simple Materialized View VS. Complex Materialized View
Simple Materialized View can always be Fast Refreshed. Based on a single table (usually). It is possible to create a Simple Materialized View on a complex query (2 tables).

Complex Materialized View cannot be Fast Refreshed. This includes queries with more than one table, aggregates, and functions. You can still create a Materialized View that is Complete Refresh on Commit.

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

Creating a Complete Refreshable Materialized View

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

Creating a Complete Refreshable Materialized View
 
Before you can create a Materialized View you need CREATE MATERIALIZED VIEW and CREATE TABLE system privileges.

Appropriate for Materialized View’s where the base tables have a significant amount of row changes. Also required when Fast Refresh is not possible.

create materialized view EXPERIMENTS_MV

refresh

complete

on demand

as

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

Query user_mviews

select mview_name

,refresh_method

,refresh_mode

,fast_refreshable

from user_mviews

where mview_name = ‘EXPERIMENTS_MV’;

MVIEW_NAME        REFRESH_METHOD    REFRESH_MODE      FAST_REFRESHABLE

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

EXPERIMENTS_MV    COMPLETE          DEMAND            NO

How to refresh a Materialized View:

Use the refresh procedure of the DBMS_MVIEWpackage.

Passing in one parameter:

(The refresh method will be the default refresh method for

that Materialized View)

The name of the Materialized View and the refresh method.

SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV');

Passing in two parameters:

The name of the Materialized View

The refresh method.

SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV','C');

You can include more than one Materialized View name

with a comma delimited string in quotes.

For example:

SQL> exec dbms_mview.refresh('MV_1, MV_2,MV_3’,'CFC');

If you exclude the refresh type parameter then the default

refresh method is used. You will get an error if you try to

use F for Fast Refresh and your Materialized View is not

set up as a Fast Refreshable Materialized View.

Refresh Types:

C = Complete

F = Fast

? = Force

SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV','?');

Force: Performs a FAST if possible, otherwise it performs a

complete refresh.

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

What is a Materialized View?

More on Materialized Views
  Materialized Views

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

What is a Materialized View?

Let’s start by looking at the differences between a View and a Materialize View.

A View is a logical table based on one or more tables or views.

Logical table meaning that the query is run at the time you select from the view.

A Materialized View is like a regular view except that the initial query has already been run and the results are actually stored in a physical table. When you select from a Materialized View you are only querying the underlying table where the results are already stored.

View – SQL query executed when you query the view.

Materialized View – Select directly from a base table where the SQL query results are already stored.

Materialized Views VS. Row/Result Cache

• Materialized View – The results stored in a table. Refreshed completely or only the DML transactions to the base table on commit or at periodic intervals.
• Result Cache – The results stored in memory.  Refreshed anytime the underlying data is changed.

 Complete Refresh VS. Fast Refresh

• Complete Refresh simply reloads the entire Materialized View from scratch.
• Fast Refresh, once created, only applies the DML changes to the Materialized View.

 Materialized View Log

• Fast Refresh requires a Materialized View Log be created prior to creating the Materialized View. The Materialized View Log is the table that stores all the DML changes that take place on the base table for the Materialized View. The Materialized View Log is self maintained and once the records are applied to the Materialized View they are automatically purged from the Materialized View log.

Refresh Mode

On Demand, On Commit, or Never.

• On Demand:  Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package REFRESH.

• On Commit: Refresh occurs automatically when a transaction that modified one of the Materialized View's base tables commits.  

For example, let’s say you have query that provides a sum of minutes and totals by user and experiment. You have an application that is running this query and because of the sum and aggregate it continues to take longer as time goes by.

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

Let’s transform this query into a table that can be queried.

CREATE TABLE EXPERIMENTS

AS

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

You put into place a nightly process to truncate and refresh it each day.

TRUNCATE TABLE EXPERIMENTS;

INSERT INTO EXPERIMENTS...

Now each day the users have sub-second query results by selecting from EXPERIMENTS.  The only problem is that the data is only up to date through the prior day.

This is similar to a Materialized View, but a Materialized View can not only be automated, it can greatly enhance this process.  A Materialized View has additional features, many which we will discuss, that a regular View does not have. 



Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

Materialized Views

Materialized View technology has been available since Oracle version 7. Originally it was called snapshot technology and soon became known as Materialized View Replication. There are several reasons you might want to use Materialized Views in your environment. First, it’s a great way to offload data to a data warehouse or a separate reporting database.  Also you can use them to improve query performance by periodically computing and storing the results of aggregated data and complex queries. 

The following is based on my white paper for a session I am presenting at Collaborate in Denver, Co the week of April 7th. 

This blog is broken into several specific  topics on Materialized Views.

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

Purging or deleting audit trail files in 11g

Whether you are using audit_trail = OS or DB there will be audit files accumulating in the audit_file_dest. Much more if audit_trail parameter is set to DB. This is the script I use to delete the files in the adump directory.

More on Oracle audit files.

Where are these audit files?

SQL> select value from v$parameter where name = 'audit_file_dest';

VALUE

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

/app/oracle/admin/db251/adump

I run this file through corntab every night.
It tells me 

• how many audit trail files there are
• how many audit trail files are going to be deleted
• After purging, how many audit files are left

 Create a file in your common scripts or desired directory.

vi delete_aud_files.sh

Make it executable.

chmod 770 delete_aud_files.sh

Add it to your crontab to run every day.

## Delete Audit files 8:30pm every night

30 20 * * * /oracle/scripts/cleanup/delete_aud_files.sh

This is an example of the script I use.

NOTE: items in RED should be editing to your environment.

#!/bin/sh
ORACLE_BASE=/app/oracle; export ORACLE_BASE
ORACLE_SID=db251; export ORACLE_SID
DAY_AFTER=3; export DAY_AFTER
OUTF=/oracle/scripts/cleanup/cleanup_audit_files.log ; export OUTF
#
echo `date '+%m/%d/%y %A %X'` > ${OUTF}
echo >> ${OUTF}
echo "SCRIPT NAME:   $0" >> ${OUTF}
echo "SERVER:        "`uname -n` >> ${OUTF}
echo "KEEP DURATION: $DAY_AFTER days" >> ${OUTF}
echo >> ${OUTF}
echo >> ${OUTF}
#
echo "Delete audit files owned by oracle..." >> ${OUTF}
echo >> ${OUTF}
echo >> ${OUTF}

# change directory to the audit file directory
cd ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
echo >> ${OUTF}
echo "Deleting from directory:" >> ${OUTF}
echo "[${ORACLE_BASE}/admin/${ORACLE_SID}/adump]" >> ${OUTF}
echo >> ${OUTF}
echo "The total number of files in directory is:" >> ${OUTF}

# output the total count of audit files to outfile
ls -al | wc -l >> ${OUTF}
echo >> ${OUTF}
echo "Total number of files to be deleted is:" >> ${OUTF}

# output the total number of audit files that will be deleted
find . -maxdepth 1 -type f -mtime +$DAY_AFTER -name "*.aud" | wc -l >> ${OUTF}
echo >> ${OUTF}

# delete the audit files
find . -maxdepth 1 -type f -mtime +$DAY_AFTER -name "*.aud" | xargs rm
echo "Files successfully deleted." >> ${OUTF}
echo "Total number of files remaining:" >> ${OUTF}

# output the remaining count of audit files in the adump directory
ls -al | wc -l >> ${OUTF}
echo >> ${OUTF}
echo >> "Complete with delete." >> ${OUTF}
#
# Now email results
echo >> $OUTF
echo `date '+%m/%d/%y %A %X'` >> $OUTF
cat $OUTF | /bin/mailx -s "`uname -n` : delete old Oracle audit files" my_email@company.com
exit

This is what the email output looks like:

06/04/12 Monday 20:30:01

SCRIPT NAME:   /oracle/scripts/cleanup/delete_aud_files.sh
SERVER:        neutron
KEEP DURATION: 3 days


Delete audit files owned by oracle...

Deleting from directory:
[/app/oracle/admin/db251/adump]

The total number of files in directory is:
38677

Total number of files to be deleted is:
16589

Files successfully deleted.
Total number of files remaining:
22094

06/04/12 Monday 20:30:03

Oracle 11g Audit Trail

If you don't care about auditing the DML in your database then you will agree with me that Oracle should not have made auditing mandatory. Yes you can set this parameter to NONE, but you will still get some level of auditing. This is only a brief overview and I am not going into too much depth here.

The possible values for the audit_trail parameter are
 SQL> show parameters audit_trail

DB
DB_EXTENDED
OS
XML
NONE

If the value is DB you should set up a cron script to purge these files:
Script to purge audit trail files in Oracle 11g.

Where are the audit files

SQL> select value from v$parameter where name = 'audit_file_dest';

VALUE

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

/app/oracle/admin/db251/adump


To update this parameter
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown immediate;
SQL>startup;
SQL> show parameter audit_trail;
AUDIT_TRAIL TYPE   VALUE
-----------------   ------    -------
audit_trail           string   DB

The default is OS. If the setting is OS then you should also periodically delete rows from the AUD$ table.

SQL> select count(*) from aud$;

COUNT(*)

----------

344273

SQL> select count(*) from aud$ where ntimestamp# > sysdate - 21;

 COUNT(*)

----------

53550

SQL> delete from aud$ where ntimestamp# > sysdate - 21;

53550 rows deleted.