OCP-1Z0-051 第42题 视图的注意事项

一、原题

Which three statements are true regarding views? (Choose three.)
A. Views can be created only from tables.
B. Views can be created from tables or other views.
C. Only simple views can use indexes existing on the underlying tables.
D. Both simple and complex views can use indexes existing on the underlying tables.
E. Complex views can be created only on multiple tables that exist in the same schema.
F. Complex views can be created on multiple tables that exist in the same or different schemas.

答案：BDF

二、题目翻译

关于视图的描述哪三个句子是正确的？（选择三个）
A. 视图只能使用表来创建。
B. 视图可以使用表或其它视图来建立。
C. 只有简单视图能使用相关表上已经存在的索引。
D. 简单视图和复杂视图都能使用相关表上已经存在的索引。
E. 复杂视图只能使用一个schema里的多个表进行创建。
F. 复杂视图可以使用相同或不同schema里的多个表进行创建。

三、题目解析

A选项不正确，B选项正确
参考联机文档第一句：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8004.htm#SQLRF01504
Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views.
A view contains no data itself. The tables upon which a view is based are called base tables.
使用CREATE VIEW语句来定义一个视图，视图是一个使用一个或多个表或视图创建的逻辑表。
视图本身并不包含数据，用来建视图的表，称为基表。

C选项不正确，D选项正确，简单视图和复杂视图，都能使用相关表上的索引。

       E选项不正确，F选项正确，测试一下，在scott用户的emp表，和hr用户的departments表上建视图：

SQL> conn scott/tiger

Connected.

SQL> grant select on emp to hr;

Grant succeeded.

SQL> conn hr/hr

Connected.

SQL> create view v_emp
  2  as
  3  select e.ename,d.department_name
  4  from scott.emp e,departments d
  5  where e.deptno=d.department_id;

View created.

SQL> select * from v_emp;

ENAME                DEPARTMENT_NAME
-------------------- --------------------
CLARK                Administration
KING                 Administration
MILLER               Administration
JONES                Marketing
FORD                 Marketing
ADAMS                Marketing
SMITH                Marketing
SCOTT                Marketing
WARD                 Purchasing
TURNER               Purchasing
ALLEN                Purchasing
JAMES                Purchasing
BLAKE                Purchasing
MARTIN               Purchasing

14 rows selected.