Using Views

A view is a virtual table consisting of a stored query, it contains no data. A view does not exist, basically its a definition defined within the data dictionary, lots of the DBA_ are views. There are a number of reason why we need views

When you use the view the SQL statement defining the view is executed, views can be created in your own schema (need CREATE VIEW privilege)
or someone else's schema (need CREATE ANY VIEW privilege), you also need privileges on the underlining tables as well.

Creating create view test_view as select employee_id, first_name, last_name from employee where manger_id = 122;
Removing drop view test_view;
Compile alter view test compile;
Using a view

select * from test_view;

Note: The sql statement defined by the view will be run.

Check for invalid views select object_name, status from dba_objects where status = 'INVALID' and object_type = 'VIEW';
Display source code of view select view_name, text from user_views;
Display view definition select * from v$fixed_view_definition where view_name = 'V$SESSION';
Useful Views
DBA_VIEWS describes all views in the database
DBA_CATALOG lists all indexes, tables, views, clusters, synonyms, and sequences in the database
V$FIXED_VIEW_DEFINITION contains the definitions of all the fixed views

Non-updatable Views

Need to update this section ASAP

link to triggers

Views are non-updatable when they contain any of the following constructs

You also cannot reference any pseudo-columns or expressions when you update a view.

Materialized Views

Everytime you use a view oracle has to execute the sql statement defined for that view (called view resolution), it must be done each time the view is used. If the view is complex this can take sometime, this is where a materialized views comes in, unlike a view it contains space and storage just like a regular table. You can even partition them and create indexes on them. Materialized views take a snapshot of the underlying tables which means that data may not represent the source data. To get the materialized view data up to date you must refresh it. Creating materialized views is simple but optimizing it can be tricky, keeping the data up to date and also getting the CBO (cost based optimizer) to use the view. As with view materialized views can be inserted, updated and deleted from.

There are three types of materialized views

Readonly Materialized view Cannot be updated and complex materialized views are supported
Updateable Materialized view can be updated even when disconnected from the master site, are refreshed on demand and consume fewer resources but requires advanced replication option to be installed
Writeable Materialized view are created with the for update clause, any changes are lost when the view is updated this also requires advanced replication option to be installed.

Query rewrite

By setting the parameter QUERY_REWRITE_ENABLED to true (default false) you instruct oracle to automatically update the materialized data with the underlying tables, so if the source data is changed oracle automatically updates the materialized view data. This is where the CBO comes to play as it now has a choice to use the view to update the table of use the source tables, it calculates the total cost on each taking account for I/O, CPU and memory usage because the materialized has the data already summarized your queries should cost less in resources and hence should run more quicker. Sometimes the CBO needs a little help in using the materialized view, this is achieved using hints within the sql statement see SQL query optimization.

Refreshing Materialized Views

Since a materialized view is defined on underlying master tables, when the master tables changes, the materialized views become out of date. To take care of this problem materialized views are updated thus keeping them in sync with the master tables. There are two types of refresh modes and 4 types of refresh types.

Refresh Modes
ON COMMIT When data is committed in the master table the view is automatically refreshed
ON DEMAND (default) You have to execute DBMS_MVIEW.REFRESH to update the view.
Refresh Types
COMPLETE This will completely rebuild the view, so if it took 2 hours to build originally it will take 2 hours to rebuild.
FAST The materialized view will use a log to log all changes to the master tables, each table within the view will have its own log file. It will then use the materialized view log to update the tables.
FORCE (default) Oracle will first use the FAST option then the COMPLETE option.
NEVER Never refresh the materialized view,use this if the underlying tables never change.

Creating Materialized views

When you create a materialized view/materialized log 3 objects are created:

There are three steps involved in creating a materialized view


grant create materialized view to vallep;
grant query rewrite to vallep;

Note: you must have privileges on the underlying tables

Create table (if one does not exists)

create table dept (
dept_id number primary key,
description varchar2(50));

Create Materialized view log create materialized view log on dept;
Create Materialized view

create materialized view m_dept
build immediate
refresh fast on commit
enable query rewrite
select * from dept;

build immediate - populate the materialized view right away
refresh fast on commit - use the fast refresh method using the logs create above
enable query rewrite - Oracle CBO will rewrite queries to use the new materialized view.

Refresh a materialized view

exec dbms_mview.refresh('m_dept','F');

F = Fast refresh
C = Complete refresh
? = Force refresh
A = Always refresh

Determine Materialized view size

exec dbms_mview.refresh.estimate_mview_size (
  stmt_id IN VARCHAR2,
  select_clause IN VARCHAR2,
  num_rows OUT NUMBER,
num_bytes OUT NUMBER);

Removing Materialized Views drop materialized view test_mview;
Useful Views
dba_mviews describes all materialized views in the database
dba_base_table_mviews describes all materialized views using materialized view logs in the database
dba_mview_comments displays comments on all materialized views in the database
dba_mview_detail_relations represents the named detail relations that are either in the FROM list of a materialized view, or that are indirectly referenced through views in the FROM list
dba_mview_joins describes a join between two columns in the WHERE clause of a subquery that defines a materialized view
dba_mview_keys describes the columns or expressions in the SELECT list upon which materialized views in the database are based
dba_mview_logs describes all materialized view logs in the database
dba_mview_refresh_times describes refresh times of all materialized views in the database
dba_tune_mview displays the result of executing the DBMS_ADVISOR.TUNE_MVIEW procedure