数据库对象（视图，序列，索引，同义词）【weber出品必属精品】

浏览数：35 / 时间：2015年06月12日

视图
视图的定义：视图就是一个查询的别名
为什么使用视图
限制数据的存取

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‘;