oracle-视图-索引-序列

Oracle提高查询性能

一 视图

视图是一个虚拟表，就是对select查询的结果取个名字.其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图是oracle又一种数据对象，视图的主要的用处是1,简化操作，提高案例，满足不同用户的查询需求，视图不是一个真正存在的物理表，它是根据别的表动态生成的。2,限制数据访问

创建视图基本语法：

create view 视图名 as select语句 [with read only]

 

特别说明：

1 with read only如果带上的话，只能查询，不能改写。

2 如果有聚合函数,需要给聚合函数别名

3 查询和表是一样的

4 视图中不包含数据,只是一个映射关系

5 当基表发生变化时,视图也会改变

 

创建或修改视图基本语法：

create or replace view 视图名 as select语句 [with read only]

 

特别说明：with read only如果带上的话，只能查询，不能改写。

 

删除视图基本语法：

drop view 视图名;

 

案例：

创建和emp表(empno,ename,job)完全一致的视图，看看带with read only和不带的区别。

注意：当表结构过于复杂时，请使用视图。

create or replace view empview as

select empno,ename,job

from emp;

 

视图可以简化操作(可以将多表中的字段合并在一张视图中)

例：我们希望查询雇员的名字和部门编号和部门名称

create or replace view empdeptview as

select e.ename,e.deptno,d.dname

from emp e,dept d

where e.deptno=d.deptno

with read only;

 

1.1 视图与表的区别

1．表需要占用磁盘空间，而视图不需要；

2．视图不能添加索引；

3．使用视图可以简化复杂查询；

4．使用视图利于提高安全性。

5．对视图的修改将会影响实际的数据表

1.2 视图的作用

1. 简化复杂查询
2. 限制数据访问

举例：查询每个部门的员工人数和部门名称
select d.dname,count(*)
from dept d,emp e
where d.deptno=e.deptno
group by d.dname;

--创建视图，存储上述结果
--复杂视图：牵涉到聚合函数的
create view numEmp as 
select d.dname,count(*) co
from dept d,emp e
where d.deptno=e.deptno
group by d.dname;

create view empSimple as
select empno,ename,job,mgr,hiredate,deptno
from emp;
--限制数据访问举例：
create user hope identified by 123456;
grant connect to hope;
grant select on scott.empSimple to hope;

1.3 视图的分类

--简单视图：简单查询语句（不涉及表连接和聚合函数）

--复杂视图：涉及到聚合函数，但是不涉及到表连接

--连接视图：涉及到表连接的视图

能不能对视图进行DML操作？ 

能，但是不是所有的视图都能进行DML操作

--(1)非只读的简单视图可以进行DML操作，事实上是针对基表的操作
create or replace view test
as select empno,ename from emp;

insert into test values(1010,'shelly');
select * from emp;

--是不是所有的简单视图都能进行DML操作呢？不是的
create or replace view test
as select ename,sal from emp;

insert into test values('haloo',5000);   --不能插入
--结论：针对视图的DML操作不能违反基表的约束。

--（2）with read only，复杂视图和连接视图不能进行DML操作；
drop view test;

 

二 序列

序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。创建序列的语法是：

语法结构：创建序列

CREATE SEQUENCE sequence_name

[START WITH num]

[INCREMENT BY increment]

[MAXVALUE num|NOMAXVALUE]

[MINVALUE num|NOMINVALUE]

[CYCLE|NOCYCLE]

[CACHE num|NOCACHE]

语法解析：

START WITH：从某一个整数开始，升序默认值是1，降序默认值是-1。

INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是1，降序默认值是-1。

MAXVALUE：指最大值。

NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。

MINVALUE：指最小值。

NOMINVALUE：这是默认值选项，升序默认值是1，降序默认值是-1026。

CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始。

NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE。

CACHE：使用CACHE选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle默认会生产20个序列号。

NOCACHE：不预先在内存中生成序列号。

案例2：创建一个从1开始，默认最大值，每次增长1的序列，要求NOCYCLE，缓存中有30个预先分配好的序列号。

代码演示：生成序列号

SQL> CREATE SEQUENCE MYSEQ

  2  MINVALUE 1

  3  START WITH 1

  4  NOMAXVALUE

  5  INCREMENT BY 1

  6  NOCYCLE

  7  CACHE 30

  8  /

序列创建之后，可以通过序列对象的CURRVAL和NEXTVAL两个“伪列”分别访问该序列的当前值和下一个值。

代码演示：序列使用

SQL> SELECT MYSEQ.NEXTVAL FROM DUAL;

NEXTVAL

----------

   1

SQL> SELECT MYSEQ.NEXTVAL FROM DUAL;

NEXTVAL

----------

2

SQL> SELECT MYSEQ.CURRVAL FROM DUAL;

CURRVAL

----------

   2

使用ALTER SEQUENCE可以修改序列，在修改序列时有如下限制：

不能修改序列的初始值。

最小值不能大于当前值。

最大值不能小于当前值。

使用DROP SEQUENCE命令可以删除一个序列对象。

代码演示：序列修改和删除

SQL> ALTER SEQUENCE MYSEQ

  2  MAXVALUE 10000

  3  MINVALUE -300

  4  /

SEQUENCE ALTERED

SQL> DROP SEQUENCE MYSEQ;

SEQUENCE DROPPED

三 索引

当我们在某本书中查找特定的章节内容时，可以先从书的目录着手，找到该章节所在的页码，然后快速的定位到该页。这种做法的前提是页面编号是有序的。如果页码无序，就只能从第一页开始，一页页的查找了。

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

²  如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。 ²  当从很多行的表中查询少数行时，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时，索引就非常有用。

Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。

语法结构：创建索引

CREATE [UNIQUE] INDEX index_name

ON table_name(column_name[,column_name…])

语法解析：

UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。

index_name：指定索引名。

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

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

案例：为EMP表的ENAME列创建创建唯一索引，为EMP表的工资列创建普通索引，把JOB列先变为小写再创建索引。

代码演示：创建索引

SQL> CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME);  ①

Index created

SQL> CREATE INDEX IDX_SAL ON EMP(SAL);  ②

Index created

SQL> CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));  ③

Index created

代码解析：

为SCOTT.EMP表的ENAME列创建唯一索引。

为SCOTT.EMP表的SAL列创建索引。

在查询中可能经常使用job的小写作为条件的表达式，因此创建索引时，可以先对JOB列中的所有值转换为小写后创建索引，而这时需要使用lower函数，这种索引称为基于函数的索引。

在select语句查询时，Oracle系统会自动为查询条件上的列应用索引。索引就是对某一列进行排序，因此在索引列上，重复值越少，索引的效果越明显。

Oracle可以为一些列值重复非常多且值有限的列（比如性别列）上创建位图索引。关于Oracle更多的索引类型（比如反向键索引等），请参考Oracle官方文档。

3.1 索引--原理介绍

索引是用于加速数据存取的数据对象，合理的使用索引可以大大降低I/O次数，从而提高数据访问性能。

为什么添加了索引后，会加快查询速度呢？

索引可以理解成类似书的目录，不用查找所有的内容来进行匹配，而是通过目录对应来快速找到存放的地方，然后再将其取出。

3.2 单列索引

单列索引是基于单个列所建立的索引，语法：

create index index_name on table(columnname);

create index 索引名称 on 表名(列名);

3.3 复合索引

复合索引是基于两列或者多列的索引。在同一张表上可以有多个索引，但是要求列的组合必需不同，

语法：

create index index_name on table(columnname,columnname...);

create index 索引名称 on 表名(列名1，列名2,...);

3.4 索引的使用原则：

1、在大表上建立索引才有意义；

2、在where子句或是连接条件上经常引用的列上建立索引；

3、在逻辑型类型字段上或者值就是固定几种的列上也不要建立索引。

3.5 索引的缺点

3.5.1 索引缺点分析

索引有一些先天不足：

1、建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。

2、更新数据的时候，系统必需要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

比如在如下字段建立索引应是不恰当的：

1、很少或从不引用的字段；

2、逻辑型的字段，如男或女(是或否)等。

综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标。

3.6 索引分类

按照数据存储方式，可以分为B*树、反向索引、位图索引；

按照索引列的个数分类，可以分为单列索引、复合索引；

按照索引列值的唯一性，可以分为唯一索引和非唯一索引。

例子

--复合索引：
create index idx_ename_deptno on emp(ename,deptno);
--创建唯一索引
create unique index idx_ename on emp(ename);
--注意：在某一个字段上创建一个唯一索引，Oracle自动在该字段创建一个唯一约束
--删除：drop index 索引名
drop index idx_ename;

--重建：alter index 索引名 rebuild
alter index idx_ename rebuild;

--注意事项：如果定义了主键约束或唯一约束，那么Oracle会自动在该字段创建一个唯一索引

 

注意事项：如果定义了主键约束或唯一约束，那么Oracle会自动在该字段创建一个唯一索引