Oracle（五）Oracle 序列、视图、索引

Oracle 子查询

Oracle子查询就是嵌套查询，他把select 查询的结果作为另外一个select、update或delete语句的条件，它的本质就是where条件查询中的一个条件表达式。其中我们数据库开发过程中，子查询可以根据查询结果的行数的多少，可以区分为单行子查询和多行子查询。

1、单行子查询：向外部返回的结果为空或者返回一行。

2、多行子查询：向外部返回的结果为空、一行、或者多行。

Oracle单行子查询

Oracle单行子查询是利用where条件“=”关联查询结果的，如果单行子查询返回多行会报单行子查询不能返回多行的数据库错误，下面利用案例讲解子查询。

案例1、查询学生信息表（stuinfo）和班级表(class)中班级为“信息科学2班（18）”的所有学生信息

select *
  from stuinfo t
 where t.classno in (select b.classno
                       from class b
                      where b.classname = '信息科学2班（18）');

/*虽然在这利用内关联也可以查出结果，
  而且效率更好，但是在一些没有关联关
  系的时候可以利用子查询 */
select t.*
  from stuinfo t, class b
 where t.classno = b.classno
   and b.classname = '信息科学2班（18）';

Oracle多行子查询

Oracle多行子查询则需要利用 IN 关键字来接收子查询的多行结果。也可以用量化关键字ANY、ALL和关系运算符>、>=、=、<、<=来组合使用。

ANY关键字：表示子查询结果当中的任意一个。假如：>ANY(子查询)，表示：只要大于子查询当中的任意一个值，这个条件就满足。

ALL关键字：表示子查询中的所有结果。假如：>ALL(子查询)，表示：必须大于子查询当中的所有结果才能满足这个条件。

案例2、查询班级表中所有班级的学生信息。代码如下：

select * from stuinfo t where t.classno in (select b.classno from class b);

案例3、ANY/ALL关键字案例展示，代码如下：

--年龄只要大于当中子查询的最小值26岁即可
select * from stuinfo t where t.age > any(26,27,28);

--年龄必须大于子查询当中的最大值28岁才可以
select * from stuinfo t where t.age > all(26,27,28);

Oracle synonym同义词（别名）

Oracle synonym 同义词是数据库当前用户通过给另外一个用户的对象创建一个别名，然后可以通过对别名进行查询和操作，等价于直接操作该数据库对象。

Oracle同义词常常是给表、视图、函数、过程、包等制定别名，可以通过CREATE 命令进行创建、ALTER 命令进行修改、DROP 命令执行删除操作。

Oracle synonym 同义词按照访问权限分为私有同义词、公有同义词。

私有同义词：私有同义词只能当前用户可以访问，前提：当前用户具有create synonym 权限。

公有同义词：公有同义词只能具有DBA用户才能进行创建，所有用户都可以访问的。

语法结构：

CREATE [OR REPLACE] [PUBLIC] SYSNONYM [当前用户.]synonym_name

FOR [其他用户.]object_name;

解析：

1、create [or replace] 命令同 create 建表命令一样，当当前用户下同义词对象名已经存在的时候，就会删除原来的同义词，用新的同义词替代上。

2、[public]：创建的是公有同义词，在实际开发过程中比较少用，因为创建就代表着任何用户都可以通过自己用户访问操作该对象，一般我们访问其他用户对象时，需要该用户进行授权给我们。

3、用户名.object_name：oralce用户对象的权限都是自己用户进行管理的，需要其他用户的某个对象的操作权限，只能通过对象拥有者（用户）进行授权给当前用户。或者当前用户具有系统管理员权限（DBA），即可通过用户名.object_name操作该对象。

案例分析：

我们在jsq_copy用户下也创建了一张学生信息表（stuinfo），该信息表只存在一个学生信息“张三丰”。由于我们当前用户student用户不具有jsq_copy.stuino的权限，因此要需要该用户授权，然后才能访问。代码如下：

--未授权之前查询（提示表不存在,没有操作权限）
select * from jsq_copy.stuinfo;

--登录jsq_copy用户进行授权
conn  jsq_copy/123456;
grant all on stuinfo to student;

--授权后再次查询该表数据
conn student/123456;
select * from jsq_copy.stuinfo;

我们现在为jsq_copy.stuinfo创建同义词stuinfo_copy，然后通过当前用户student直接操作同义词stuinfo_copy查询jsq_copy.stuinfo表数据。代码如下：

create synonym stuinfo_copy for jsq_copy.stuinfo;

同义词删除

    同义词删除只能通过同义词拥有者的用户或者具有DBA权限的用户才能删除。

DROP [PUBLIC] SYNONYM [用户.]sysnonym_name;

Oracle 序列

Oracle序列Sequence是用来生成连续的整数数据的对象，它经常用来作为业务中无规则的主键。Oracle序列可以是升序列也可以是降序列。

创建Oracle序列的语法结构如下：

CREATE SEQUENCE sequence_name
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[START WITH num]
[INCREMENT BY increment]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]

语法解析：

• 1、MAXVALUE/MINVALUE：指定的是序列的最大值和最小值。
• 2、NOMAXVALUE/NOMINVALUE：不指定序列的最大值和最小值，使用系统的默认选项，升序的最大值：10^{27次方，降序是-1。升序最小值：1，降序的最小值：-10}26。
• 3、START WITH：指定从某一个整数开始，升序默认是1，降序默认是-1。
• 4、CYCLE | NOCYCLE:表示序列达到最大值或者最小值的时候，是否重新开始。CYCLE：重新开始，NOCYCLE：不重新开始。
• 5、CACHE：使用 CACHE 选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率 。
• 6、NOCACHE：不预先在内存中生成序列号。

Oracle 视图

oracle视图可以理解为数据库中一张虚拟的表，他是通过一张或者多张基表进行关联查询后组成一个虚拟的逻辑表。查询视图，本质上是对表进行关联查询。

视图的本身是不包含任何数据，只是一个查询结果，当基表的数据发生变化时，视图里面的数据也会跟着发生变化。我们经常在实际开发过程中遇到的视图可以大概分为三种：单表视图、多表关联视图、视图中含有子视图。

视图的作用和优势

1、使数据简单化：可以将复杂的查询创建成视图，提供给他人使用，他人就不需要去理解其中复杂性的业务关系或逻辑关系。这样对视图的使用人员来说，就简化了数据的，屏蔽了数据的复杂性。

2、表结构设计的补充：系统刚刚开始设计时，大部分程序是直接访问表结构的数据的，但是随着业务的变化、系统的更新等，造成了某些表结构的不适用，这时候去修改表结构对系统的影响太大，开发成本较高，这个时候可以创建视图来对表结构的设计进行补充，降低开发成本。程序可以直接通过查询视图得到想要的数据。

3、增加安全性：视图可以把表中指定的字段展示给用户，而不必把表中所有字段一起展示给用户。在实际开发中，视图经常作为数据的提供方式，设置为只读权限提供给第三方人员进行查询使用。

创建视图

CREATE [OR REPLACE]  VIEW view_name
AS
SELECT查询
[WITH READ ONLY CONSTRAINT]

解释：

1、OR REPLACE：如果视图已经存在，则替换旧视图。

2、WITH READ ONLY：默认不填的，用户是可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行 insert 操作，或者基表设置了某些约束，这时候插入视图或者修改视图的值，有可能会报错）， WITH READ ONLY 说明视图是只读视图，不能通过该视图进行增删改操作。但是在现实开发中，基本上不通过视图对表中的数据进行增删改操作。

案例1、 利用学生信息表（stuinfo）、班级表（class）关联创建视图，只提供一些学生基本信息和班级信息（剔除学生一些敏感信息：如身份证，家庭地址等）。代码如下：

create view vw_stuinfo as 
select a.stuid,--学号
       a.stuname,--学生姓名
       a.grade,--年级
       a.sex,--性别（1:男、2:女）
       a.age,--年龄
       b.classname,--班级
       b.monitorname,--班长
       b.headmastername--班主任
  from stuinfo a, class b
 where a.classno = b.classno;

select * from vw_stuinfo;

Oracle索引

Oracle索引（index）最大的作用是用来优化数据库查询的效率，提升数据库的查询性能。（类似书的目录）

Oracle数据库中如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据进行排序或归类，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle 会自动的引用该索引，先从索引表中查询出符合条件记录的 ROWID，由于 ROWID 是记录的物理地址，因此可以根据 ROWID 快速的定位到具体的记录，当表中的数据非常多时，引用索引带来的查询效率非常可观 。

何时建立索引：

既然我们都知道建立索引有利于查询速率的提升，那是不是所有字段都可以加上索引。这是万万不行的，建立索引不仅仅要浪费空间来存储索引表，当数据量较少时，直接查询数据比经过查询索引表再定位到表数据的速度更快。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。所以要根据实际情况， 考虑好再建立索引。

考虑：

1、Oracle 数据库会为表的主键和包含唯一约束的列自动创建索引，所以在建立唯一约束时，可以考虑该列是否必要建立。是否经常要作为查询条件。

2、如果某个表的数据量较大（十几二十万以上），某列经常作为where的查询条件，并且检索的出来的行数经常是小于总表的5%，那该列可以考虑建立索引。

3、对于两表连接的字段，应该考虑建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

4、不应该在小表上建立索引。上面也说过，小表之间查询的数据会比建立索引的查询速度更快，但是在某些字段，如性别：只有男、女和未知三种数据时，可以考虑位图索引，可以增加查询效率。

5、经常进行DML操作，即经常进行增删改的操作的表，创建表索引时就要权衡一下，因为建索引会导致进行DML操作时速度变慢。所以可以根据实际情况，选择某些字段建立索引，而不能盲目乱建。

索引的类别：

1、b-tree索引： Oracle数据中最常见的索引，就是b-tree索引，create index创建的normal就是b-tree索引，没有特殊的必须应用在哪些数据上。

2、bitmap位图索引： 位图索引经常应用于列数据只有几个枚举值的情况，比如上面说到过的性别字段，或者我们经常开发中应用的代码字段。这个时候使用bitmap位图索引，查询效率将会最快。

3、函数索引： 比如经常对某个字段做查询的时候经常是带函数操作的，那么此时建一个函数索引就有价值了。例如：trim（列名）或者substr(列名)等等字符串操作函数，这个时候可以建立函数索引来提升这种查询效率。

4、hash索引： hash索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。创建hash索引必须使用hash集群，相当于定义了一个hash集群键，通过这个集群键来告诉oracle来存储表。因此，需要在创建HASH集群的时候指定这个值。存储数据时，所有相关集群键的行都存储在一个数据块当中，所以只要定位到hash键，就能快速定位查询到数据的物理位置。

5、reverse反向索引： 这个索引不经常使用到，但是在特定的情况下，使用该索引可以达到意想不到的效果。如：某一列的值为{10000,10001,10021,10121,11000,…}，假如通过b-tree索引，大部分都密集分布在某一个叶子节点上，但是通过反向处理后的值将变成{00001,10001,12001,12101,00011,…}，很明显的发现他们的值变得比较随机，可以比较平均的分部在各个叶子节点上，而不是之前全部集中在某一个叶子节点上，这样子就可大大提高检索的效率。

6、分区索引和分区表的全局索引： 这两个索引是应用在分区表上面的，前者的分区索引是对分区表内的单个分区进行数据索引，后者是对分区表的全表进行全局索引。分区表的介绍，可以后期再做单独详解，这里就不累述了。

索引的创建

注：可以不加 表空间。

create[unique]|[bitmap] index index_name --UNIQUE表示唯一索引、BITMAP位图索引
on table_name(column1,column2...|[express])--express表示函数索引
[tablespace tab_name] --tablespace表示索引存储的表空间
[pctfree n1]    --索引块的空闲空间n1
[storage         --存储块的空间
 (
    initial 64K  --初始64k
    next 1M
    minextents 1
    maxextents unlimited

)];

语法解析：

1、UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引，BITMAP表示位图索引。

2、index_name：指定索引名。

3、tabl_name：指定要为哪个表创建索引。

4、column_name：指定要对哪个列创建索引。我们也可以对多列创建索引，这种索引称为组合索引。

也可以是函数表达式，这种就是函数索引。

修改索引：

1、重命名索引：

alter index index_old rename to index_new;--重新命名索引

2、合并索引、重新构造索引：我们索引建好后，经过很长一段时间的使用，索引表中存储的空间会产生一些碎片，导致索引的查询效率会有所下降，这个时候可以合并索引，原理是按照索引规则重新分类存储一下，或者也可以选择删除索引重新构造索引。

alter index index_name coalesce;--合并索引
alter index index_name rebuild;--重新构造

删除索引：

drop index index_name;

查看索引

select t.INDEX_NAME,--索引名字
       t.index_type,--索引类型
       t.TABLESPACE_NAME,--表空间
       t.status,--状态
       t.UNIQUENESS--是否唯一索引
  from all_indexes T 
  where  t.INDEX_NAME='index_name';

案例分析：

案例1、学生信息表（stuinfo）创建的时候就对学号（stuid）设置了主键（PK_STUINFO），当我们学生信息表数据量大的情况下，我们明显发现班号（classno）需要一个索引，不仅仅是用来关联班级信息表（class）、而且经常作为查询条件，因此创建脚本如下：

create index IDX_STUINFO_CLASSNO on STUINFO (CLASSNO)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

案例2、对于学生信息我们经常用性别作为统计条件进行对学生信息进行统计，因此我们可以在性别（sex）建立一个位图索引进行查询优化。代码如下：

create bitmap index IDX_STUINFO_SEX on STUINFO (SEX)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

查询一下三种索引的状态：

select t.INDEX_NAME,
       t.index_type,
       t.TABLESPACE_NAME,
       t.status,
       t.UNIQUENESS
  from all_indexes T
  where t.TABLE_NAME='STUINFO'
  AND T.OWNER='STUDENT'