Oracle视图概述

使用视图的原因：安全，简化用户的SQL语句，防止错误的发生，提高性能，让数据更加有意义；

 

创建视图示例：

CREATE VIEW emp_finAS SELECT hire_date,job_id,salary,commission_pct FROM employees;

 

 

当对两张表进行关联的时候通常用到两种技术：nested loop,  hash join

nestedloop join uses an index to get to individual rows

hash joinreads the whole table into memory;

例

create view dept_emp as select /*+USE_HASH(employees departments)*/ department_name,last_name

fromdepartments natural join employees;

 

简单视图和复杂视图

简单视图和复杂视图的分类方法是：是否允许DML语句执行

简单视图允许DML语句：A simple view draws data from one detailtable, uses no functions ,and does no aggregation

复杂视图不允许DML语句:A complexview can join detail table, use functions, and perform aggregations;

 

创建视图

CREATE [OR REPLACE][FORCE | NOFORCE ] VIEW

viewname [(alias1)]

AS subquery

[WITH CHECK OPTION[CONSTRAINT constraintname]]

[WITH READ ONLY[CONSTRAINT constraintname]];

 

force：即使原表不存在也建立起一个视图；

withcheck option:

with read only:组织通过视图进行DML操作；

 

视图必须通过成功编译才可以被使用；