Views


A view is a logical representation of another table or combination of tables
A view is simply the representation of a sql statement that is stored in memory so that it can easily be re-used.
With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view.
Advantages of views
·         Views restrict accesses to the data because the view can display selected columns from the table.
·         Views can be used to make simple queries to retrieve the results of complicated queries for ex, views can be used to query information from multiple table without the user knowing how to write join statement.
·         Views provide groups of users access to data according to their particular criteria.

Two types of views:
1.      Simple view
2.      Complex view
If the table is dropped, if the view is queried then error message will be displayed
If the table rows are deleted, then if view is queried for the data then data present at that instant in the table will be displayed
Complex view:
Sql> create view dept_sum (name, minsal, maxsal, avgsal)
  2  as select d.dname,min(e.sal),max(e.sal),avg(e.sal) from emp e join dept d
  3  on (e.deptno=d.deptno) group by d.dname;
Sql> select * from dept_sum;
Name   minsal              maxsal             avgsal
--------------       ----------            ----------           ----------
Programmer     1300                5000                2916.66667
Research 800               3000                2175
Sales          950            2850                1566.66667
We cannot remove a row if the view contains the following
·         Group function
·         A group by clause
·         A distinct  keyword
·         The pseudo column rownum keyword
Difference between simple views and complex views
  • A simple view selects from one table. A complex view selects from one or more tables.
  • A simple view does not contain functions but complex views contain functions.
  • You can perform dml through simple views but you cannot always perform dml through complex views.

Read-only views
Users can only run select and desc statements against read only views. Examples:
Read only clause on a simple view:
Create view clerk (id_number, person, department, position)
    as select empno, ename, deptno, job
         from emp
         where job = 'clerk'
  with read only;

With check option

The with check option clause specifies the level of checking to be done when doing dml against the view. If specified, every row that is inserted, updated or deleted through the view must conform to the definition of the view. 
Sql> create view d20 as select ename, sal, deptno from emp2 where deptno = 20
  2  with check option;
View created.
Sql> update d20 set deptno = 10;
Update d20 set deptno = 10
       *
Error at line 1:
Ora-01402: view with check option where-clause violation



Materialized views in oracle
A materialized view is a stored summary containing pre computes results (originating from an sql select statement).
As the data is pre computed, materialized views allow for (seemingly) faster data ware query answers
Types of materialized views
There are three types of materialized views:
  • Read only materialized view
  • Updateable materialized view
  • Writeable materialized view
Read only materialized views
Advantages:
  • There is no possibility for conflicts as they cannot be updated.
  • Complex materialized views are supported
Updateable materialized views
Advantages:
  • Can be updated even when disconnected from the master site or master materialized view site.
  • Requires fewer resources than multi master replication.
  • Are refreshed on demand. Hence the load on the network might be reduced compared to using multi master replication because multi master replication synchronizes changes at regular intervals.
Updateable materialized views require the advanced replication option to be installed.
Writeable materialized views
They are created with the for update clause during creation without then adding the materialized view to a materialized view group. In such a case, the materialized view is updatable, but the changes are lost when the materialized view refreshes.
Writeable materialized views require the advanced replication option to be installed.


A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.
A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the advanced replication feature. You can select data from a materialized view as you would from a table or view.
For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.
In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.
Primary key materialized views
The following statement creates the primary-key materialized view on the table emp located on a remote database.
Sql>        create materialized view mv_emp_pk
               refresh fast start with sysdate 
               next  sysdate + 1/48
               with primary key 
               as select * from emp;
 
Materialized view created.
Note: when you create a materialized view using the fast option you will need to create a view log on the master tables(s) as shown below:
Sql> create materialized view log on emp;
Materialized view log created.
Rowid materialized views
The following statement creates the rowid materialized view on table emp located on a remote database:
Sql>        create materialized view mv_emp_rowid 
               refresh with rowid 
               as select * from emp;  
Materialized view log created.
Sub query materialized views
The following statement creates a sub query materialized view based on the emp and dept tables located on the remote database:
Sql> create materialized view  mv_empdept
As select * from emp e
Where exists
     (select * from dept d
     where e.deptno = d.deptno)
Refresh clause
[refresh [fast|complete|force]
               [on demand | commit]
               [start with date] [next date]
               [with {primary key|rowid}]]
The refresh option specifies:
  1. The refresh method used by oracle to refresh data in materialized view
  2. Whether the view is primary key based or row-id based
  3. The time and interval at which the view is to be refreshed

Refresh method - fast clause
The fast refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.
You should create a materialized view log for the master tables if you specify the refresh fast clause. 
Sql> create materialized view log on emp;
 
Materialized view log created.
Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.
Refresh method - complete clause
The complete refresh re-creates the entire materialized view. If you request a complete refresh, oracle performs a complete refresh even if a fast refresh is possible.
Refresh method - force clause
When you specify a force clause, oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (fast, complete, or force), force is the default.

Primary key and rowid clause
With primary key is used to create a primary key materialized view i.e. The materialized view is based on the primary key of the master table instead of rowid (for rowid clause). Primary key is the default option. To use the primary key clause you should have defined primary key on the master table or else you should use rowid based materialized views.
Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
Rowid materialized views should have a single master table and cannot contain any of the following:
  • Distinct or aggregate functions
  • Group by subqueries , joins & set operations

Timing the refresh
The start with clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The next clause specifies the interval between refreshes
Sql> create materialized view mv_emp_pk
         refresh fast
         start with sysdate
         next  sysdate + 2
         with primary key
         as select * from emp;

Materialized view created.
In the above example, the first copy of the materialized view is made at sysdate and the interval at which the refresh has to be performed is every two days.

Comments

Post a Comment

Popular posts from this blog

Dependency Management - Dependencies in Server-Side PL/SQL

Dependency Management - Dependencies in Server-Side PL/SQL If you’re working with server-side PL/SQL programs, you can use the server’s data dictionary to explore usage relationships in quite a bit of detail. Here’s a simple illustration of this rule in action, using the data dictionary to give us eyes into the database. Let’s say that you have a package named bookworm on the server. In this package is a function that selects from the books table. If you create the table and then create the package, expect to see the following: SQL> SELECT object_name, object_type, status 2 FROM USER_OBJECTS 3 WHERE object_name = 'BOOKWORM'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------ ------- BOOKWORM PACKAGE VALID BOOKWORM PACKAGE BODY VALID That is, there are two objects with the name BOOKWORM; the first is the package spec and the second is the body. ...
Read more

Dependencies Among Schema Objects

Image
Dependencies Among Schema Objects The definitions of some objects, including views and procedures, reference other objects, such as tables. As a result, the objects being defined are dependent on the objects referenced in their definitions. This chapter discusses the dependencies among schema objects and how Oracle automatically tracks and manages these dependencies. This chapter contains the following topics: Introduction to Dependency Issues Resolution of Schema Object Dependencies Object Name Resolution Shared SQL Dependency Management Local and Remote Dependency Management Introduction to Dependency Issues Some types of schema objects can reference other objects as part of their definition. For example, a view is defined by a query that references tables or other views. A procedure's body can include SQL statements that reference other objects of a database. An object that references another object as part of its definition is called a dependent object, while ...
Read more

Dependency Injection in PL/SQL – Remove Hard Coded Dependencies from your code

Image
Dependency Injection in PL/SQL – Remove Hard Coded Dependencies from your code Frequently our code has dependencies. Some are intrinsic dependencies, like the packages we need to invoke and the type definitions we rely on. Other dependencies are more like configuration settings, not really an intrinsic part of the code at all. Like the parameters in a discount calculcation or the name of the file our Logging component should write or the URL at which a WebService can be accessed. In this article we briefly discuss the Dependency Injection design pattern and continue to investigate a possible implementation in PL/SQL. The Dependency Injection design pattern has been introduced by Martin Fowler – see for example Inversion of Control Containers and the Dependency Injection pattern at http://www.martinfowler.com/articles/injection.html – and can be seen as a specialization of the Inversion of Control pattern. Dependency Injection is about the principle of separating configuration from us...
Read more