Oracle-5-2数据库对象(索引、视图、聚集、序列、同义词)
Oracle数据库管理第9章 视图、索引、序列、同义词
如果一个数据表中存有海量的数据记录，当对表执行指定条件的查询时。常规的查询方法会将 所有的记录都读取出来，然后再把读取的每一条记录与查询条件进行比对，最后返回满足条件的记 录。这样进行操作的时间开销和I/O开销都十分巨大的。对于这种情况，就可以考虑通过建立索引来 减小系统开销。 如果要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，而有了索引之后，只 需要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID快速找到表中对应 的记录。举个例子来说，如果将表看作一个本书，则索引的作用则类似于书中的目录。在没有目录 的情况下，要在书中查找指定的内容必须阅读全书，而有了目录之后，只需要通过目录就可以快速 找到包含所需内容的页码（相当于ROWID）。 Oracle系统对索引与表的管理有很多相同的地方，不仅需要在数据字典中保存索引的定义，还 需要在表空间中为它分配实际的存储空间。创建索引时，Oracle会自动在用户的默认表空间或指定 的表空间中创建一个索引段，为索引数据提供空间。 说明：将索引和对应的表分别放在不同硬盘的不同的表空间中能够提高查询的速度，因为Oracle能 够并行读取不同硬盘的数据，这样的查可以避免产生I/O冲突。 用户可以在Oracle中创建多种类型的索引，以适应各种表的特点。按照索引数据的存储方式可 以将索引分为B树索引、位图索引、反向键索引和基于函数的索引；按照索引列的唯一性又可以分为 唯一索引和非唯一索引；按照索引列的个数又可以分为单列索引和复合索引。
上述语句建立一个视图EMP_SALES_VIEW。因为建立视图时没有提供列别名，所以视图的列名分别 为EMPNO、ENAME、JOB和DEPTNO，用户可以通过SELECT语句像查询普通的数据表一样查询视图的信息 ，来看下面的例子。
Oracle实验3：视图、序列、同义词和索引
解决办法：
先退出scott用户登陆sys用户使用以下sql语句授权再登陆scott用户创建同义词。
Sql语句：
Grantcreate synonymto scott ;
5.写出用数据字典视图来查看已有的视图、序列、同义词和索引的SELECT语句。
视图
SELECT * FROM user_views;
序列
SELECT * FROM user_sequences;
同义词
select * from syn;
索引
select * from ind；
出现的问题：
解决方案（列出遇到的主要问题和解决办法，列出没有解决的问题）：
from dept d left outer join emp e on (d.deptno = e.deptno)
group by d.deptno,d.dname
order by d.deptno;
2.创建一个新的序列empno_seq，序列的步长为1，起始值为7961，最大值为9999，并且不允许序列循环。用此序列可以向EMP表中插入新雇员。
40 OPERATIONS 0
已选择4行。
Sql语句：
Create view salOfDept
AS
Select d.deptno, dname, count(e.sal) count_num, avg(e.sal) avg_sal, max(e.sal) max_sal,min(e.sal) min_sal
SQL语句：
Create sequence empno_seq
Increment by 1
Start with 7961
Maxvalue 9999
oracle 第13讲 使用视图、索引、序列和同义词
第13讲使用视图、索引、序列和同义词对象1、使用视图（view）视图是基于其他表或其他视图的逻辑表。
视图的作用：（1）限制数据访问，访问视图时只能访问select语句所涉及到的列。
（2）简化复杂查询，如果经常需要在多个表之间执行复杂查询操作，可以基于该复杂查询语句建立视图。
视图分类：（1）简单视图，基于单个表所建视图，不包含任何函数、表达式及分组数据的视图。
（2）复杂视图，包含函数、表达式或者分组数据的视图。
（3）连接视图，基于多表所建立的视图。
（4）只读视图，至允许执行查询操作。
在视图上执行DML操作的原则：（1）DELETE操作原则：如果视图包含有group by子句、分组函数、distinct关键字和rownum 伪列，那么不能在该视图上执行delete操作。
（2）UPDATE操作原则：如果视图包含有group by子句、分组函数、distinct关键字和rownum伪列，以及使用表达式所定义的列，那么不能在该视图上执行update操作。
（3）INSERT操作原则：如果视图包含有group by子句、分组函数、distinct关键字和rownum 伪列，以及使用表达式所定义的列，或者在视图上没有包含视图基表的not null列，那么不能在该视图上执行insert操作。
使用system帐号授予scott账户create view的权限。
SQL> grant create view to scott;1.1 建立视图（1）建立简单的视图例：建立视图vu_emp，包含emp表的empno,ename,sal列create view vu_emp asselect empno,ename,sal from emp;查看视图列SQL> desc vu_emp;Name Type Nullable Default Comments----- ------------ -------- ------- --------EMPNO NUMBER(4)ENAME VARCHAR2(10) YSAL NUMBER(7,2) Y使用数据字典user_views查看用户视图SQL> select view_name from user_views;VIEW_NAME------------------------------VU_EMP查看视图文本：SQL> select text from user_views where view_name=upper('vu_emp');对简单视图的增、删、改操作SQL>insert into vu_emp (empno,ename,sal) values (1000,'郭永洪',1500);SQL> update vu_emp set sal=3000 where empno=1000;SQL> delete from vu_emp where empno=1000;视图本身没有任何数据，视图上的增、删、改、查操作都是针对视图基表来完成的。
5、Oracle之序列、索引、视图和同义词
序列
»序列(Sequence)用于自动生成连续整型数据的 对象。该对象是共享对象，主要用于提供自动增 长功能；可以升序，也可以降序
CREATE SEQUENCE 序列名 [START WITH 起始值] [INCREMENT BY 步进值] [MAXVALUE 最大值|NOMAXVALUE] [MINVALUE 最小值|NOMINVALUE] [CYCLE|NOCYCLE] [CACHE 个数|NOCACHE]
14
序列
»访问序列：可以通过序列对象的CURRVAL和 NEXTVAL两个“伪列”分别访问该序列的当前值 和下一个值。
– select [scham.]序列名.{currval | nextval} from dual; --查看序列当前值和下一个值
»修改序列：ALTER SEQUENCE 序列名...
– 当从很多行的表中查询少数行时，也要考虑创 建索引。有一条基本的准则是：当任何单个查 询要检索的行少于或者等于整个表行数的10% 时，索引就非常有用
4
索引
»创建索引：CREATE [UNIQUE] INDEX 索引名 ON 表名(列名1[,列名2…]) [REVERSE]
»与mysql中索引的区别
B树索引结构
»创建： CREATE INDEX 索引名 ON 列名(列名);
小小于于等等于于5050
大于50
SELECT id FROM t WHERE id=31;
>100 <100..90 <90..80 <80..70 <70..60 <60..50
<50..40
4<04-03.0.30
<30..20 <20..10 <10
视图-索引-同义词-序列
常用的Oracle数据库对象一、数据库对象简介1、Oracle数据库对象又称模式对象2、数据库对象是逻辑结构的集合，最基本的数据库对象是表3、其他数据库对象包括：二、同义词1、同义词是现有对象的一个别名●简化SQL语句●隐藏对象的名称和所有者●提供对对象的公共访问2、同义词公用两种类型●公有同义词可被所有的数据库用户访问●私有同义词只能在其模式内访问，且不能与当前模式的对象同名●创建一个名为emp同义词，来自scott.emp表，查询的时候就可以直接用同义词，而不用加模式名（这个是私用同义词，只能针对当前用户）●创建一个公用的同义词，当前用户可以查询●其他用户也可以查询●替换和删除同义词（包括共有和私有）三、序列1、序列是用于生成唯一、连续序号的对象2、序列可以是升序的，也可以是降序的3、使用create sequence 语句创建序列4、序列的访问，通过序列的伪列来访问序列的值●Nextval返回序列的下一个值（用来实现排序序列名.nextval）●Currval返回序列的当前值（用来查看当前序列值序列名.currval）●向表中插入序列toys_seq.nextval●查看序列●查看序列的当前值●修改序列●查看修改后的序列，可以插入的值●删除序列四、视图1、视图以经过制定的方式显示来自一个或多个表的数据2、视图可以视为‘虚拟表’或‘存储的查询’3、创建视图所依据的表称为‘基表’4、视图的优点有：●提供了另外一种级别的表安全性●隐藏的数据的复杂性●简化的用户的SQL命令●隔离基表结构的改变●通过重命名列，从另一个角度提供数据5、创建视图●创建表，并插入数据●创建视图并查看视图●运用order by子句创建视图●创建带有错误的视图●在创建一个表，用于多表连接的视图6、在视图上也可以使用修改数据的DML语句，如：insert update和delete7、视图上的DML语句有如下限制：●只能修改一个底层的基表●如果修改违反了基表的约束条件，则无法更新视图●如果视图包含连接操作符、distinct关键字、集合操作符、聚合操作符或group by子句，则将无法更新视图●如果视图包含位列或表达式，则将无法更新视图8、键保留表●因为studno既是stud_details中的主键，也是连接结果的主键8、视图中的函数●视图中可以使用单行函数、分组函数和表达式●使用drop view语句删除视图五、索引1、索引是与表相关的一个可选结构2、用以提高SQL语句执行的性能3、减少磁盘I/O4、使用create index语句创建索引5、在逻辑上和物理上都独立于表的数据6、Oracle自动维护索引7、索引有各种类型，除了标准索引外，还有一些特殊烈性的索引：8、创建标准索引●创建、更改和删除索引9、唯一索引●唯一索引确保在定义索引的列中没有重复值●Oracle自动在表中的主键列上创建唯一索引●使用create unique index语句创建唯一索引10、组合索引●组合索引是在表的多个列上创建的索引●索引中列的顺序是任意的●如果SQL语句的where子句引用了组合索引的所有累或大多数列，则可以提高检索速度●组合emp2表中的empno和ename列创建索引11、反向键索引●反向键索引反转索引列键值的每个字节●通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上●创建索引时使用reverse关键字12、位图索引●位图索引适合创建在低基数列上●位图索引不直接存储rowID，而是存储字节位rowID的映射●减少响应时间●节省空间占用13、索引组织表●索引组织表的数据存储在与其关联的索引上●索引中存储的时行的实际数据，而不是rowID●基于主键访问数据●Create table命令与organization index子句一起用于创建索引组织表●普通表与索引表的比较14、基于函数的索引●基于一个或多个列上的函数或表达式创建的索引●表达式中不能出现聚合函数●不能在LOB类型的列上创建●创建必须具有query rewriter权限六、总结1、同义词时现有数据库对象的别名2、序列用于生成唯一、连续的序号3、视图时基于一个或多个表的虚拟表4、索引时与表相关的一个可选结构，用于提高SQL语句执行的性能5、索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引6、索引组织基于主键访问数据。
oracle 表对象索引、视图
1.创建表结构（员工医疗保险系统表）企业表：医院表：医保卡表：员工表：就诊表：消费表：医保表：2.查看表结构(1) 利用OEM查看员工医疗保险系统所有表的字段信息和约束信息。
(2) 利用SQL*Plus或iSQL*Plus从数据字典DBA_TAB _COLUMNS查看员工医疗保险系统所有表的字段信息。
(3) 利用SQL*Plus或iSQL*Plus从数据字典DBA_ CONSTRAINTS查看员工医疗保险系统所有表的约束信息。
SELECT * FROM DBA_TABLES WHERE TABLE_NAME= 'STAFF';3.修改表结构(1) 利用SQL*Plus或iSQL*Plus将表“staff_sql”重新命名为“staff_sql0”。
Alter table staff rename to staff_sql0;(2) 利用企业管理器为“staff_sql0”表添加“age INT”字段，利用DESC命令查看“staff_sql0”表的字段信息。
(3) 利用SQL*Plus或iSQL*Plus为“staff_sql0”表添加“salary NUMBER(5,2)”、“salary_add NUMBER(3,1)”两个字段，利用DESC命令查看“staff_sql0”表的字段信息。
(4) 利用SQL*Plus或iSQL*Plus向“staff_sql0”表添加“sname”字段惟一性约束，从数据字典DBA_CONSTRAINTS查看“staff_sql0”表的约束信息。
(5) 利用SQL*Plus或iSQL*Plus删除“staff_sql0”表上“sname”字段惟一性约束，从数据字典DBA_CONSTRAINTS查看“staff_sql0”表的约束信息。
(6) 利用企业管理器从“staff_sql0”表删除“age”字段，利用DESC命令查看“staff_sql0”表的字段信息。
本文详细介绍Oracle概念：同义词、序列与视图
同义词 synonymCREATE [PUBLIC]SYNONYM synonym For schema.object隐藏对象的名称和所有者:select count(*) from hr.employees;create synonym emp for hr.employees; --默认属于donny用户，是donny 的私有对象privateselect count(*) from emp;为分布式数据库的远程对象提供了位置透明性:访问其他数据库时，要首先建立数据库连结：CREATE DATABASE LINK test_link CONNECT TO username IDENTIFIED BY pass USING 'orabase';Select count(*) from hr.employees@test_link;create synonym link_emp for hr.employees@test_link;select count(*) from link_emp;提供对象的公共访问:create public synonym pub_emp for hr.employees;pub_emp属于public用户，数据库所有用户都可以访问。
同义词类型–私有 emp 实际上donny.emp–公用 pub_emp 所有用户都可以直接访问当公有对象和私有对象同名时(因为数据不同的用户，所以可以)，以私有对象优先。
(类似于局部变量)desc dba_synonyms/ user_synonyms/ all_synonyms 数据字典，复数tab公有同义词建立私有的tab表，查看效果。
删除同义词：drop synonym donny.emp;drop public synonym pub_emp;序列sequence：CREATE SEQUENCE donny.seq --也是属于某个用户的，以下参数均可省略，使用默认值。
数据库对象视图序列、索引、同义词
通过本节学习，你需要掌握：
视图(view) 序列(sequence) 索引(index) 同义词(synonym)
最后修改时间：2011.4象
数据库对象 描述
Table
基本的存储单元，包括数据行和字段
View
2020/4/14
11
.
从视图中查询数据(2)
SQL*PLUS
SELECT * FROM empvu10;
7839 KING PRESIDENT 7782 CLARK MANAGER 7934 MILLER CLERK
USER_VIEWS
EMPVU10 SELECT empno, ename, job FROM emp WHERE deptno = 10;
----
EMPNO
NOT NULL NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
2020/4/14
8
.
创建视图(3)
创建视图的查询语句中可以使用复杂的 SELECT语句，包括连接(join)、分组 (groups)、子查询(subqueries)、排序 (order by)
2020/4/14
5
.
简单视图和复杂视图
特点 涉及的表格数量 包含函数？ 包含组数据？ 允许DML操作？
简单视图 1 否 否 是
复杂视图 ≥1 是 是 不一定
2020/4/14
6
.
创建视图(1)
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view [(alias [, alias …] )]
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Oracle数据库对象 Oracle数据库对象
作业
作业
Oracle数据库对象 Oracle数据库对象
簇(Cluster)
一 创建簇 create cluster Class_cluster( classNO varchar2(10) ) tablespace users; 二 簇的索引 create index idxClassCluster on cluster Class_cluster; --只有建立了聚集索引才可能使用聚集 三 基于簇创建表 create table stuClass( ClassNo varchar2(10), ClassName varchar2(20) ) cluster Class_cluster(ClassNo); create table student( stuno varchar2(10), stuName varchar2(10), ClassNO varchar2(10) ) cluster Class_cluster(ClassNo); 四 删除簇 drop cluster 簇名 [INCLUDING TABLES --删除属于聚集的所有表 [CASCADE CONSTRAINTS]]; --从聚集外的表中删除引用聚集表主键或唯一 键的全部引用完整性约束.若省略该选项,且有这样的引用完整性约束,则会出错,且 不删除该聚集.
Oracle数据库对象 Oracle数据库对象
同义词
同义词
定义：定用一个别名来代替“用户名.对象名” 定义：定用一个别名来代替“用户名.对象名” 功能： 功能：安全性高 语法结构： 语法结构： 用户名. CREATE [public] SYNONYM 同义词名 FOR 用户名.对象名 使用同义词： 使用同义词： SELECT * FROM 同义词 删除同义词： 删除同义词： DROP [public] SYNONYM 同义词
Oracle数据库对象 Oracle数据库对象
索引
按特定的顺序定位、查找表中的记录，可以使用 按特定的顺序定位、查找表中的记录， 索引
作用：可提高查询速度 作用： 唯一索引: 唯一索引: 字段1字段2 字段N CREATE UNIQUE INDEX 索引名 ON 表 （字段1字段2…字段N）; 普通索引: 普通索引: 字段1字段2 字段N CREATE INDEX 索引名 ON 表（字段1字段2…字段N）; 反向键(逆键)索引: 反向键(逆键)索引: CREATE INDEX 索引名 ON 表（字段1字段2…字段N） REVERSE; 字段1字段2 字段N 重建逆键索引为非逆键的索引(不可以将正常的索引重建为逆键索引): 不可以将正常的索引重建为逆键索引): ALTER INDEX idx_dept REBUILD NOREVERSE; 位图索引: 位图索引: 字段1字段2 字段N); CREATE BITMAP INDEX 索引名 ON 表(字段1字段2…字段N);
创建视图的语法： 创建视图的语法：
CREATE [OR REPLACE] [FORCE] VIEW view_name [(alias[, alias]...)] AS select_statement [WITH CHECK OPTION] [WITH READ ONLY];
删除视图: 删除视图: DROP VIEW <视图名> <视图名 视图名>
现在来测试下用视图来操作表数据有哪些Байду номын сангаас制
Oracle数据库对象 Oracle数据库对象
簇(Cluster)
聚集(Cluster) (Cluster)是存储表数据的可选择的方法. 一 个聚集是一组表，将具有同一公共列值的行存储 在→起，并且它们经常一起使用。这些公共列构 成聚集码.例如， EMP 表和DEPT表共享DEPTNO 列.所以EMP表和DEPT表可聚集在一起，聚集码 的列为DEPTNO列，该聚集将每个部门的全部职 工行、该部门的行物理地存储在同一数据块中. 在读数据块时,该数据块中聚集表的所有数据被 读取(到SGA),这是建立聚集的主要原因.因此聚集 主要使用在有相关数据的两个表中且在一起被频 繁访问.
Oracle数据库对象 Oracle数据库对象
索引
按特定的顺序定位、查找表中的记录，可以使 按特定的顺序定位、查找表中的记录， 用索引
作用：可提高查询速度 作用： 函数索引: 函数索引: 函数名(字段参数列表)); CREATE INDEX 索引名 ON 表(函数名(字段参数列表)); 删除索引： 删除索引： 索引名； DROP INDEX 索引名；
Oracle数据库对象 Oracle数据库对象
序列
序列
定义：按照设定的规则自动产生数据的方案。 定义：按照设定的规则自动产生数据的方案。 语法： 语法： create sequence 序列名 增长值] 开始值] [increment by 增长值] | [start with 开始值] [maxvalue 指定最大值 | nomaxvalue（10^27或-1）] nomaxvalue（10^27或 nominvalue(1或 [minvalue 最小值 | nominvalue(1或-10^26)] n(预分配 个序列号保存在内存中) 预分配n [CACHE n(预分配n个序列号保存在内存中) | NOCACHE] nocycle（默认） [cycle | nocycle（默认）] 序列的使用： 序列的使用： 序列名.NEXTVAL DUAL； SELECT 序列名.NEXTVAL FROM DUAL；
数据字典: 数据字典: user_indexes 提问：索引是不是越多越好？ 提问：索引是不是越多越好？
Oracle数据库对象 Oracle数据库对象
视图
视图：是由一个或多个表（或其它视图）中的 视图：是由一个或多个表（或其它视图） 数据的一种定制的表示，是用一个查询定义， 数据的一种定制的表示，是用一个查询定义， 所以可以认为是一个存储的查询。 所以可以认为是一个存储的查询。
Oracle数据库对象 Oracle数据库对象
可使用伪列访问值
– CURRVAL
返回序列的当前值
序列
– NEXTVAL
返回序列首次引用时的起始值 以后对 nextval 的引用将使用 INCREMENT BY 子句增加序列 值，并返回新值 删除序列： 删除序列： DROP SEQUENCE 序列名 修改序列: 修改序列: ALTER SEQUENCE 序列名 INCREMENT BY n CACHE n; 不能修改start 不能修改start with
Oracle数据库对象 Oracle数据库对象
视图
注意：视图可以和基本表一样被查询，但是利用视图进行 但是利用视图进行 注意：视图可以和基本表一样被查询，但是
数据增，删，改操作，会受到一定的限制。 如果视图包含以下内容就会是一个不可更改的视图： 如果视图包含以下内容就会是一个不可更改的视图：
1.连接操作符 1.连接操作符 2.DISTINCT 操作符 3.集合操作符 3.集合操作符 4.聚合函数 4.聚合函数 5.GROUP BY 子句 6.伪列rownum或表达式 6.伪列rownum或表达式
功能： 功能：
通过视图检索数据(隐藏了数据的复杂性) 通过视图检索数据(隐藏了数据的复杂性) 复杂的查询编写为视图(为用户简化了SQL命令) SQL命令 复杂的查询编写为视图(为用户简化了SQL命令) 限制视图只能访问基表部分特定的数据（隔离了基表的结构变化, 限制视图只能访问基表部分特定的数据（隔离了基表的结构变化, 保护了表中的敏感数据，为表提供附加的安全性） 保护了表中的敏感数据，为表提供附加的安全性）