view to scott; 授权创建试图
SQL> create force view v1 as select * from v;
Warning: View created with compilation errors.
SQL> select object_name,status from user_objects where object_name='V1';
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
V1
INVALID
SQL> select text from user_views where view_name='V1';
TEXT
--------------------------------------------------------------------------------
select * from v
创建基表，执行查询
SQL> select object_name,status from user_objects where object_name='V1';
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
V1
VALID
SQL> create or replace view empsal as select * from emp where sal>2000 with check option;
View created.
SQL> select sal from empsal;
SAL
----------
2975
2850
2450
3000
5000
3000
SQL> update empsal set sal=1799 where sal=2450;
update empsal set sal=1799 where sal=2450
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> update empsal set sal=7000 where sal=2450;
1 row updated.
drop view empsal;
视图DML操作限制
碰到如下语法不能删除试图行数据
使用分组函数
使用group by语法
使用去除重复行语句
使用了rownum伪列
修改限制
使用分组函数
使用group by语法
使用去除重复行语句
使用了rownum伪列
使用了表达式
insert操作限制
使用分组函数
使用group by语法
使用去除重复行语句
使用了rownum伪列
使用了表达式
非空约束的列没在select列表中引用
7.索引
自动建立
手动建立
creat unique|bitmap index ...on table...
多个列上建立索引需要注意顺序
user_indexes
user_ind_columns
基于函数的建立索引
SQL> set autot trace exp;
SQL> select * from emp where ename='SCOTT';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='SCOTT')
SQL> create index emp_ename_i on emp(ename);
Index created.
SQL> select * from emp where ename='SCOTT';
Execution Plan
----------------------------------------------------------
Plan hash value: 549418132
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_I | 1 | | 1 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SCOTT')
SQL> select * from emp where substr(ename,1)='KING';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
------------------------------------------------------------------------- |