二十八、视图、索引、同义词

数据库对象：

• 表 存储数据
• 视图 表的逻辑相关的数据的集合
• 索引 提高查询效率
• 同义词 给对象起别名
• 序列： 提供一组有规律的数值（主要提供主键的自增）

视图

是通过查询语句将查询出来的结果创建为一个表现形式（视图）就是满足查询语句的一个子表。

视图的作用

为了简化开发人员对于复杂sql的编写，提高复杂sql语句的复用。

创建或修改视图

create or replace view 视图名称 as 查询的 sql 语句

查询视图：

select * from emp10;

-- 查询部门10的员工信息，并存储在一个视图中
create or replace view emp10
as
select * from emp where deptno=10;
-- 查看视图
select * from emp10;

实例：

-- 示例：查询每个部门中最低薪资,最高薪资,平均薪资
create or replace view salMessage(no,minSal,maxSal,avgSal)
as
select deptno,min(sal),max(sal),avg(sal) from emp group by deptno;
select * from salMessage;
-- 当我们在创建视图的子查询中，如果出现了分组查询的使用，必须为每一个分组函数其一个别名
create or replace view salMessage1
as
select deptno,min(sal) minSAL,max(sal) maxSAL,avg(sal) avgSal from emp group by deptno;
select * from salMessage1;

注：当我们在创建视图的子查询中，如果出现了分组查询的使用，必须为每一个分组函数其一个别名

删除视图

drop view 视图名称

--删除一个视图
drop  view  emp10;

Top-N 分析

• Top-N 分析查询一个列中最大或最小的 n 个值:
  • 销售量最高的十种产品是什么?
  • 销售量最差的十种产品是什么?
• 最大和最小的值的集合是 Top-N 分析所关心的
• 查询最大的几个值的 Top-N 分析:

SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top-N_column) WHERE ROWNUM <= N;

注：对ROWNUM只能使用 < 或 <= ，而使用 = ，> ，>= 都将不能返回任何数据。

--练习1：查询员工表中，工资前10名的员工信息。
select empno,ename,sal,hiredate,rownum as rn
from (select empno,ename,sal,hiredate from emp order by sal desc)
where rownum<=10;
-- 练习2：查询员工表中，工资排名在5-10之间的员工信息。
select empno,ename,sal,hiredate from 
(select rownum rn,empno,ename,sal,hiredate 
from 
    (select rownum rn,empno,ename,sal,hiredate from emp order by sal desc)
)
where rn<=10 and rn>5;

索引

• 一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
• 索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度
• 索引一旦建立，Oracle 管理系统会对其进行自动维护，而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
• 在删除一个表时，所有基于该表的索引会自动被删除
• 通过指针加速Oracle 服务器的查询速度
• 通过快速定位数据的方法，减少磁盘 I/O

创建索引

• 自动创建：在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
• 手动创建：用户可以在其他列上创建非唯一的索引，以加速查询

CREATE INDEX index ON table (column[, column]...);

-- 创建索引
create index emp_name_index
on emp(ename);

删除索引：

drop index 索引名

何时创建索引

以下情况可以创建索引:

• 列中数据值分布范围很广
• 列经常在 WHERE 子句或连接条件中出现
• 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

创建索引的表，一定是查询的频率大于修改的频率

何时不创建索引

下列情况不要创建索引：

• 表很小
• 列不经常作为连接条件或出现在 WHERE 子句中
• 查询的数据大于数据总量的4%
• 表经常更新

同义词（synonym）

使用同义词访问相同的对象：

• 方便访问其他用户的对象
• 缩短对象名字的长度

在 oracle 中，每个用户只能直接访问属于自己所有的表、视图、索引、序列；如果要访问其他用户的对象，则需要在对象上指明你所要访的对象的所属用户。

不使用同义词访问：

-- system 用户访问 soctt 用户的 emp 表
select * from scott.emp;

企业的开发中，当项目经理或者数据库管理员创建好了数据表之后，但是此时数据表只属于项目经理用户或这数据库管理员所有， 那么对于其他的开发人员来说，如果要访问数据表，则在每一个sql语句上的表明上都需要添加表的所属用户。此时我们就需使用同义词。

使用同义词：

-- 创建同义词
create synonym s_emp for scott_emp;
-- 查询同义词
select * from s_emp;

删除同义词：

--删除同义词
drop  synonym  s_emp;

注：同义词对表的修改也会影响到基表
视图也可以创建同义词