Views and Materialized Views 整理

Views and Materialized Views

There are two kinds of views in an Oracle database, and they are very different in their implementation and use.  One is stored as pure SQL, while the other creates a table that it maintains.   We will start with the simple view.

Views

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.  You use a view in much the same way you use a database table. Note that this command does not result in anything being actually stored in the database at all except for a data dictionary entry that defines this view.This means that every time you query this view, Oracle has to go out and execute the view and query the database data. When you query from a view, the database takes the stored SQL statement and creates a table in memory.  Because it is defined as a SQL statement, a view can join tables or limit the number of rows.  There are two primary uses of views:

 

§        Reduce the complexity of a SQL statement

We can reduce the complexity of our queries by creating a view instead of using subqueries.

Visually, the database creates the view in memory and then selects from it.  In reality, the view SQL is combined with your query, and the resulting query is executed.  The complexity is reduced only for the user.

 

§        Restrict access to data.

The other use of a view is to limit a user’s access to some of the data.Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to. Using views for security on less complex databases is probably not a bad thing. As databases become more complex, this solution becomes harder to scale and other solutions will be needed.

 

A simple view can be inserted/updated/deleted because it directly relates to the table it is created on.

 

Example:

If you need to recreate a view, you can use the CREATE or REPLACE command, and all grants will be preserved.

SQL> create or replace view sales_s109 as
  2  select *
  3  from sales
  4  where store_key in ('S109','S108')
  5  with read only;

View created.

Notice that with the CREATE or REPLACE command, we do not have to first drop the view.

 

Complex views contain table joins, functions or groups.  Complex views do not map back to the underlying tables.  If we group our sales by store, each row in my view is an aggregate of the rows in the underlying tables.

 

Complex views contain table joins, functions or groups.  Complex views do not map back to the underlying tables.  If we group our sales by store, each row in my view is an aggregate of the rows in the underlying tables.

create view avg_store as
select
  store_name,
  avg(quantity) qty
from
  store join sales using (store_key)
group by store_name;

The example above contains a GROUP BY clause and a multi- row function.  This view is not updatable.  A view is not updatable if:

§        Contains a multi row function

§        Contains a group by clause

§        Contains the distinct key word.

§        Uses the rownum key word.

If the view joins more than one table and does not violate the rules defined above, you can update one table’s data at a time.

 

Our view will only show us rows with a store key of S110.  But, it will allow you to insert a row with another store key.  To insure that this is not possible, you can create the view with the CHECK option, and all INSERT<!-- [if supportFields]> XE "INSERT" <![endif]--><!-- [if supportFields]><![endif]--> /UPDATE<!-- [if supportFields]> XE "UPDATE" <![endif]--><!-- [if supportFields]> <![endif]--> operations will be validated against the WHERE clause in the view.

SQL> create or replace view sales_s110 as
  2  select *
  3  from sales
  4  where store_key = 'S110'
  5  with check option constraint s110_view_ck; 

View created.

SQL> insert into sales_s110
  2  values ('B104','S106','O200',SYSDATE,300);
insert into sales_s110
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

The CHECK option creates a constraint on the view.

 

If we no longer need the view, we can delete it from the database by dropping it. SQL> drop view sales_s110;