Oracle数据库基础及应用第12章 视图索引的管理.ppt

* Denying DML Operations You can ensure that no DML operations occur on your view by creating it with the WITH READ ONLY option. The example on the slide modifies the EMPVU10 view to prevent any DML operations on the view. Instructor Note (for pages 11-17) If the user does not supply a constraint name, the system assigns a name in the form SYS_Cn, where n is an integer that makes the constraint name unique within the system. * Using the WITH CHECK OPTION Clause It is possible to perform referential integrity checks through views. You can also enforce constraints at the database level. The view can be used to protect data integrity, but the use is very limited. The WITH CHECK OPTION clause specifies that INSERTs and UPDATEs performed through the view cannot create rows which the view cannot select, and therefore it allows integrity constraints and data validation checks to be enforced on data being inserted or updated. If there is an attempt to perform DML operations on rows that the view has not selected, an error is displayed, with the constraint name if that has been specified. UPDATE empvu20 SET department_id = 10 WHERE employee_id = 201; UPDATE empvu20 * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation Note: No rows are updated because if the department number were to change to 10, the view would no longer be able to see that employee. Therefore, with the WITH CHECK OPTION clause, the view can see only employees in department 20 and does not allow the department number for those employees to be changed through the view. create view v_emp_job_sal(job,avgsal,sumsal,maxsal,minsal) as select job,avg(sal),sum(sal),max(sal),min(sal) from emp group by job; * * 索引是一种可选的数据结构，在一个表上是否建立索引，不会对表的使用方式产生任何影响。但是如果在表中的某些字段上建立了索引，能够显著地提高对该表的查询速度，并且可以在很大程度上减少查询时的硬盘I/O操作。 索引与表一样，不仅需要在数据字典中保存索引的定义，还需要在表空间中为它分配实际的存储空间。当创建索引时，Oracle会自动在用户的默认表空间中或指定的表空间中创建一个索引段，为索引数据提供存储空间。与创建表的情况类似，在创建索引时，也可以为它设置存储参数。 为某个表创