数据库对象（视图，序列，索引，同义词）【weber出品必属精品】
2014-08-23 22:22 yaoweber 阅读(827) 评论(0) 编辑 收藏 举报- 视图
视图的定义：视图就是一个查询的别名
为什么使用视图
限制数据的存取SQL> conn /as sysdba 已连接。 SQL> grant create view to scott; 授权成功。 SQL> create view v1 as select empno,ename,job,mgr from emp; 视图已创建。 SQL> select * from v1; EMPNO ENAME JOB MGR ----- ------ --------- ----- 7369 SMITH CLERK 7902 7499 ALLEN SALESMAN 7698 7521 WARD SALESMAN 7698 7566 JONES MANAGER 7839 7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839 7782 CLARK MANAGER 7839 7788 SCOTT ANALYST 7566 7839 KING PRESIDENT 7844 TURNER SALESMAN 7698 7876 ADAMS CLERK 7788 7900 JAMES CLERK 7698 7902 FORD ANALYST 7566 7934 MILLER CLERK 7782 使得复杂的查询变得容易
允许数据的独立性：with check option
代表对同一数据的不同视角
- 视图的种类
视图分为：简单视图和复杂视图 - 创建一个视图
在 CREATE VIEW 语句中包含一个子查询语法： CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY];
- replace：更改视图的定义
SQL> create or replace view v1 as select * from emp; 如果v1存在则重新覆盖
- 视图起别名：当有函数，表达式的时候必须使用别名
SQL> create or replace view v2 as select deptno ,sum(sal) from emp group by deptno; create or replace view v2 as select deptno ,sum(sal) from emp group by deptno * ERROR at line 1: ORA-00998: must name this expression with a column alias ORA-00998: 必须使用列别名命名此表达式
- 约束
WITH CHECK OPTION：对where子句中条件添加CHECK约束SQL> create or replace view v1 as select * from e where deptno=10 with check option; 视图已创建。 SQL>insert into v1 values(2777, 'MILLER', ' CLERK', 7782,' 23-JAN-82', 1300,null, 20) * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation 违反了where子句的约束。 因为约束的内容为必须是deptno=10;我们查询一下约束 SQL> select constraint_name,constraint_type from user_constraints where table_name='V1'; CONSTRAINT_NAME C ------------------------------ - SYS_C005400 V 我们可以看到这里V1视图中的确是有V约束 这里再次注意，V1必须是大写的，因为select * from tab中查的内容也是大写的。
- 还有要注意：关联其他表的时候，不要插入虚拟列，也就是rowid.
SQL> insert into v1 select * from emp; insert into v1 select * from emp * 第 1 行出现错误: ORA-01733: 此处不允许虚拟列
FORCE:当查询中的表不存在的时候，强制创建视图，但是视图不可用
SQL> create view v2 as select * from t; create view v2 as select * from t * 第 1 行出现错误: ORA-00942: 表或视图不存在 SQL> create force view v2 as select * from t; 警告: 创建的视图带有编译错误。 SQL> select status from user_objects where object_name='V2'; STATUS ------- INVALID SQL> create table t as select * from dept; 表已创建。 SQL> select status from user_objects where object_name='V2'; STATUS ------- INVALID SQL> alter view v2 recompile; alter view v2 recompile * 第 1 行出现错误: ORA-00922: 选项缺失或无效 SQL> alter view v2 compile; 视图已变更。 SQL> select status from user_objects where object_name='V2'; STATUS ------- VALID SQL> drop table t purge; 表已删除。 SQL> select status from user_objects where object_name='V2'; STATUS ------- INVALID SQL> create table t as select * from dept; 表已创建。 SQL> select status from user_objects where object_name='V2';--当访问视图的时候，视图自动编译 STATUS ------- INVALID SQL> select * from v2; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select status from user_objects where object_name='V2'; STATUS ------- VALID
- 查询视图
查询视图的过程：
1. 通过访问数据字典，找到视图的定义
2. 执行视图的定义（视图中的SELECT语句）
- 对视图进行DML操作的规则
可以对简单视图进行DML操作.
对复杂的视图进行DML操作，是有限制的
- 删除视图：删除视图，只删除视图的定义，不会删除视图引用的表
SQL> drop view v1;
- 内联（inline）视图
内联视图是在 嵌在某个SQL 语句中，具有别名的一个子查询.
一个内联视图类似于在FROM子句中使用的一个具有名字的子查询
一个内联视图不是数据库中的一个模式对象
SELECT a.ename, a.sal, a.deptno, b.maxsal FROM emp a, (SELECT deptno, max(sal) maxsal FROM emp GROUP BY deptno) b WHERE a.deptno = b.deptno AND a.sal < b.maxsal
- 序列
1. 什么是序列
自动产生唯一的整数
是一个可以共享的数据库对象
典型地用于产生数据库表中的主键值
能够节省应用程序的代码
当缓存在内存中时，能够提高存取的效率
- CREATE SEQUENCE 语法
SQL> create sequence s1 increment by 1 start with 3 maxvalue 10 nocache cycle; Sequence created.
NEXTVAL：序列的下一个值，如果序列创建之后，第一次访问，必须使用NEXTVAL
CURRVAL：序列的当前值
SQL> select s1.nextval from dual; NEXTVAL ---------- 3 SQL> select s1.currval from dual; CURRVAL ---------- 3 SQL> select s1.nextval from dual; NEXTVAL ---------- 4 SQL> / NEXTVAL ---------- 5
- 如果序列是递增的，这个序列有默认的最小值，就是1，如果序列是循环可用，当达到最大值的时候，再次访问序列，序列将从默认的最小值1开始返回数据
如果序列是递减的，这个序列有默认的最大值，就是-1，如果序列是循环可用，当达到最小值的时候，再次访问序列，序列将从默认的大小值-1开始返回数据
- 序列的使用：
SQL> create table t(id number primary key,name varchar2(10)); 表已创建。 create sequence s1 increment by 1 start with 1 nomaxvalue cache 10; 序列已创建。 ed一个执行过程： begin for i in 1..100 loop insert into t values(s1.nextval,'a'); end loop; commit; end; / PL/SQL 过程已成功完成。 SQL> select * from t; SQL> select * from t; ID NAME ---------- ---------- 3 a 4 a 5 a 6 a 7 a 8 a ..... ID NAME ---------- ---------- 100 a 101 a 102 a 100 rows selected.
注意：
cache 必须跟个数字，如： create sequence s3 increment by 1 start with 1 nomaxvalue cache 2 nocycle; 如果你写成： create sequence s3 increment by 1 start with 1 nomaxvalue cache nocycle; 是不会给你默认缓存多少条，所以必须要 指定条数
- 将序列值缓存在内存中，使得这些值访问起来更快
如果发生下列情况，可能使得一个表中的序列值之间产生间隔，而不是连续的:
1回滚操作产生 2.系统崩溃 3.序列值同时也用于其它表 4.如果一个序列是以 NOCACHE选项建立的, 那么可以通过查询USER_SEQUENCES 表来查看下一个可用的序列值，而不会使序列的当前值增加.
修改一个序列
SQL> alter sequence s1 maxvalue 100; 序列已更改。
如何查看一个用户下的序列
SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- S1 1 10 1 Y N 0 1 S2 1 1.0000E+27 1 N N 0 3
删除一个序列
SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- S1 1 10 1 Y N 0 1 S2 1 1.0000E+27 1 N N 0 3
- 索引
1. 什么是索引
一个数据库模式对象
Oracle利用索引来加快对数据行的访问
依靠索引来快速定位数据，从而减少了磁盘I/O的次数
与使用它的表是相互独立的数据库对象
Oracle 服务器自动对索引进行维护和使用
2.索引的结构：索引是一个平衡树，由根、分支、叶子节点组成
根和分支的作用：快速定位叶子节点
叶子节点：存储键列值----rowid
键列值：索引列的值
- 创建索引
SQL> create table e as select * from emp; 表已创建。 SQL> create index ind_e on e(empno); 索引已创建。
查询表中建立的索引
select index_name,index_type from user_indexes where table_name='E'; INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- INDEX_E NORMAL
- 索引类型
B树索引采用二进制树的形式，它是默认的索引类型
在位图索引中，每个建立索引的独特值都有一个位图，每一位的位置表示一行，其中可能包含（也可能不包含）索引值。对于低基数列而言，这是最佳结构，当列的唯一值比较少时，使用位图索引
- 索引选项：
唯一索引可确保每个索引值是唯一的。
索引可按升序或降序存储其键值
反向关键字索引以反向顺序存储其键值字节
组合索引是基于多列的索引
基于函数的索引是以函数返回值为基础的索引
压缩索引会删除重复的关键字值
我们通过设置autotrace，在执行sql语句的时候同时显示执行计划和附加统计信息
详细的使用autotrace可以看这篇文章《http://xinxiangsui2018.blog.163.com/blog/static/106097856201141891126404/》
SQL> conn /as sysdba 已连接。 SQL> @?/sqlplus/admin/plustrce.sql SQL> grant plustrace to scott; SQL> conn scott/tiger 已连接。 SQL> set autotrace on SQL> set linesize 100 SQL> select * from emp where empno=7788; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------ --------- ----- -------------- ----- ---------- ------ 7788 SCOTT ANALYST 7566 19-4月 -87 4000 20 执行计划 ---------------------------------------------------------- Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7788)
------其实这里的目的是想从执行计划中知道有了索引和没有索引是不同的----- - 索引是如何创建的
自动创建:当在创建表时，如果指定了 PRIMARY KEY或者 UNIQUE约束，那么将自动创建索引
手动创建:用户可以在某个列上建立非唯一的索引，以加快基于该行的查询.
- 在一个列上或者多个列上创建索引.
create index ind_e on e(empno); create index ind_e on e(empno,ename); -
小结： B树索引适用对象： （1）适合高基数的列（唯一值多）； （2）适合与大量的增、删、改（OLTP）； （3）不能用包含OR操作符的查询； 小结: B树索引经过大量的插入删除操作以后一个是容易使树不平衡，再一个是删除后空间不回收。所以定期重建索引非常有必要。 位图索引优点： （1）用一个位来表示一个索引的键值，节省了存储空间； （2）对and,or或=的查询条件，位图索引查询效率很高，计算机善于处理0，1数据。 什么时候适合使用位图索引：引用一下oracle官方文档 1.一个查询条件包含多个列，并且要创建索引的列只有几个不同的值（拥有大量重复值）。 2.大量的数据符合这些列上的约束条件。 3.位图索引可以创建在一个、多个或全部列上。 4.被引用的表包含了非常多的行。 注意: 位图索引只能用在相对稳定的表，不适合用在表数据频繁变化的联机系统中。 什么时候不适合创建位图树索引： 1. 频繁进行插入或更新的表； 2. 索引列被引用为表达式的一部分 3. 列在查询条件中不经常使用. 4. 大多数基于该表的查询，所查询出的数据量远多于2–4% 行 5. 表被频繁修改.
- 如何查看索引：
SELECT ic.index_name,ic.column_name, ic.column_position col_pos,ix.uniqueness from user_indexes ix,user_ind_columns ic where ic.index_name=ix.index_name and ic.table_name='EMP'; INDEX_NAME COLUMN_NAME COL_POS UNIQUENES ---------- -------------------- ---------- --------- PK_EMP EMPNO 1 UNIQUE
- 删除索引
drop index ind_e;
- 同义词
1. 什么是同义词
为某个对象起的别名
2. 同义词的作用
通过创建一个同义词 (对象的另一个名字)来简化对数据库中对象的名称，缩短了对象的名字长度
3. 分类
公共同义词：sys用户才可以创建，公共同义词属于public用户
SQL> conn /as sysdba 已连接。 SQL> create public synonym e for scott.emp; SQL> select owner,synonym_name,table_owner,table_name from dba_synonyms where SYNONYM_NAME='E'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME ---------- -------------------- ------------------------------ ------------------------------ PUBLIC E SCOTT EMP conn scott/tiger grant select on emp to public conn hr/hr select *from e; SQL> drop public synonym e;
私有同义词：普通用户创建，私有同义词属于创建的普通用户
SQL> conn /as sysdba 已连接。 SQL> grant create synonym to scott; SQL> drop table e purge; 表已删除。 SQL> create synonym e for emp; 同义词已创建。 SQL> grant select on emp to hr; 授权成功。 SQL> conn hr/hr 已连接。 SQL> select scott.e; select scott.e * 第 1 行出现错误: ORA-00923: 未找到要求的 FROM 关键字 SQL> select * from scott.e; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------ --------- ----- -------------- ----- ---------- ------ 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 4000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择14行。 SQL> 在实际中的应用：对数据库连接的重命名 SQL> create database link remote_6 connect to scott identified by tiger using 'orcl'; SQL> create synonym remote for scott.emp@remote_6; 同义词已创建。 SQL> select * from remote;
- 数据字典的神秘面纱：
select object_type ,object_name,owner from dba_objects where object_name='DBA_TABLES' OBJECT_TYPE OBJECT_NAME OWNER ------------------- -------------------- ------------------------------ VIEW DBA_TABLES SYS SYNONYM DBA_TABLES PUBLIC SQL> select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where SYNONYM_NAME='DBA_TABLES'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME ---------- -------------------- -------------------- -------------------- PUBLIC DBA_TABLES SYS DBA_TABLES SQL> select TEXT from dba_views where view_name='DBA_TABLES';
