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 |
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
Privilege | grant create materialized view to vallep; Note: you must have privileges on the underlying tables |
Create table (if one does not exists) | create table dept ( |
Create Materialized view log | create materialized view log on dept; |
Create Materialized view | create materialized view m_dept build immediate - populate the materialized view right away |
Refresh a materialized view | exec dbms_mview.refresh('m_dept','F'); F = Fast refresh |
Determine Materialized view size | exec dbms_mview.refresh.estimate_mview_size ( |
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 |