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 视图的作用
- 简化复杂查询
- 限制数据访问
举例：查询每个部门的员工人数和部门名称 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会自动在该字段创建一个唯一索引