Oracle11g基础学习---------（3）管理数据库对象

学习Oracle从哪里入手呢，在这里，我将带你走进Oracle的大门，一天一章，7天入门不是梦

1. 同义词

1.1方案
方案是数据库对象的集合，为了区分各个集合，需要给集合命名，其名称即为方案名 
方案中包含各种对象，如表、视图、索引、同义词、序列、数据库链接等对象 
一般情况下，一个用户对应一个方案，在创建用户的同时为该用户创建一个与用户名同名的方案，并作为该用户的缺省方案 
如果访问一个表时没有指明该表属于哪一个用户方案，则系统将会自动地在访问表前加上缺省的方案名 
数据库中一个对象的完整名称为“方案名.对象名”，而不是“用户名.对象名” 

1.2 同义词概述
同义词是方案对象的一个别名，常用于简化对象和提高对象访问的安全性
同义词并不占用实际存储空间，只在数据库字典中保存同义词的定义
可以创建同义词的对象主要包括表、视图、同义词、序列、存储过程等对象
Oracle中可以创建两种类型的同义词：公用同义词和方案同义词

1.3 公用同义词与方案同义词
公用同义词（public synonym）
公用同义词由一个特殊的用户组PUBLIC所拥有，数据库中所有的用户都可以使用公用同义词
SYS用户创建的数据字典视图就是公用同义词的示例
方案同义词（schema synonym）
方案同义词由创建它的用户所拥用，也称为私有同义词（private synonym），用户可以控制其他用户是否有权使用属于自己的方案同义词 
方案同义词常在应用开发中使用，为应用开发提供命名上的解决方案 
当代码引用一个未限定的表、视图、同义词、序列、函数等对象时，Oracle会按以下顺序来查看这3个位置是否有被引用的对象
当前用户拥有的对象 
由当前用户拥有的一个方案同义词 
公用同义词 
如果在这3个地方都没有找到该对象的名称，将会出现错误提示，如“ORA-00942：表或视图不存在”
 
1.4 创建同义词
创建方案同义词 
需要在自己的方案中创建方案同义词时，用户必须具有CREATE SYNONYM系统权限 
需要在其他的方案中创建方案同义词时，用户必须具有CREATE ANY SYNONYM系统权限 
语法
CREATE [OR REPLACE] SYNONYM synonym_name [schema.]object;
其中，OR REPLACE表示如果同义词已经存在，将重新覆盖新建同义词 
创建同义词
创建公用同义词
需要创建公用同义词时，用户必须具有CREATE PUBLIC SYNONYM系统权限
语法
CREATE [OR REPLACE] PUBLIC SYNONYM synonym_name [schema.]object; 

1.5 使用同义词
使用方案同义词 
使用方案同义词可分为方案用户使用和其他用户使用 
方案用户使用自己的方案同义词 
方案用户具有对象的所有权限，所以可以像使用原对象一样使用该对象的同义词
使用同义词
使用公用同义词 
与方案同义词不同，如果使用公用同义词来访问其他方案中的对象，就不需要在该公用同义词前面添加方案名 
如果用户没有被授予访问该公用同义词所引用的对象的对象权限，仍然不能使用该公用同义词
其他用户使用另一个用户创建的方案同义词 
在方案同义词前面加上方案名作为前缀的方式来使用其他用户所创建的方案同义词
通过自己的方案同义词访问其他方案中的对象
通过在自己的方案中创建指向其他方案中对象的方案同义词

1.6 删除同义词 
删除同义词后，同义词所引用的基础对象不会受影响
删除方案同义词
用户能够删除自己方案中的任何方案同义词，但若需要删除其他用户方案中的方案同义词，必须拥有DROP ANY SYNONYM系统权限
语法
DROP SYNONYM synonym_name
删除公用同义词 
需要删除公用同义词时，用户必须具有DROP PUBLIC SYNONYM系统权限
语法
DROP PUBLIC SYNONYM synonym_name

2. 序列

2.1 序列概述
序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数
与视图、约束相似，序列也不占用实际的存储空间，而只在数据字典中保存序列的定义
引入序列的理由或序列的主要用途
主键、外键值应用需求
流水号应用需求
序列的生成与定义的内容

2.2 创建序列
需要在自己的方案中创建序列时，用户必须具有CREATE SEQUENCE系统权限 
需要在其他的方案中创建序列时，用户必须具有CREATE ANY SEQUENCE系统权限

2.3 使用序列
NEXTVAL和CURRVAL列
NEXTVAL列返回序列生成的下一个值
CURRVAL列返回序列生成的当前值
序列的初始化
在第一次引用CURRVAL列之前，必须引用过一次NEXTVAL列，用于初始化序列的值，否则会出现错误提示

2.4 更改序列
需要更改自己方案中的序列时，用户必须具有ALTER SEQUENCE系统权限
需要更改其他方案中的序列时，用户必须具有ALTER ANY SEQUENCE系统权限
不能修改序列中的起始值，如果要修改起始值，必须删除序列再重建
更改序列包括
修改MAXVALUE和MINVALUE
修改INCREMENT增量值
修改缓存中的序列的数目
语法：
ALTER SEQUENCE sequnce_name
[INCREMENT BY n2]
[{MAXVALUE n3 | NOMAXVALUE}]
[{MINVALUE n4 | NOMINVALUE}]
[{CACHE n5 | NOCACHE}]
[{CYCLE | NOCYCLE}]
需要删除自己方案中的序列时，用户必须具有DROP SEQUENCE系统权限
需要删除其他方案中的序列时，用户必须具有DROP ANY SEQUENCE系统权限 
语法
DROP SEQUENCE sequence_name

2.5 删除序列
需要删除自己方案中的序列时，用户必须具有DROP SEQUENCE系统权限
需要删除其他方案中的序列时，用户必须具有DROP ANY SEQUENCE系统权限 
语法
DROP SEQUENCE sequence_name

3. 视图
3.1视图概述
视图（VIEW）是由SELECT子查询语句定义的一个逻辑表，只有定义而无数据，是一个“虚表” 
视图是查看和操作表数据的一种方法 
视图的优点
提供各种数据表现形式
提供某些数据的安全性
隐藏数据的复杂性
简化查询语句
执行特殊查询
保存复杂查询

3.2 创建视图
需要在当前方案中创建视图时，用户必须具有CREATE VIEW系统权限
需要在其他方案中创建视图时，用户必须具有CREATE ANY VIEW系统权限
视图的拥有者必须被明确授予访问在视图定义中所参考的所有基础对象的权限
语法：
CREATE [OR REPLACE] [FORCE] VIEW view_name
[(alias1,alias2..)]
AS select_statement
[WITH CHECK OPTION [CONSTRAINT constraint] ]
[WITH READ ONLY];

参数	说明
OR REPLACE 	如果视图已经存在，该选项将重新创建该视图 
FORCE 	无论基表是否存在，都将创建视图 
view_name 	指定创建视图时的名称 
alias 	指定由视图的查询所选择的表达式或列的别名 
select_statement 	创建视图时的SELECT语句 
WITH CHECK OPTION 	在使用视图时，检查涉及的数据是否能通过SELECT子查询的WHERE条件，否则不允许操作并返回错误提示 
WITH READ ONLY 	创建的视图只能用于查询，而不能用于更改数据。该子句不能与ORDER BY子句同时存在 
创建普通视图
使用WITH READ ONLY选项创建只读视图
使用WITH CHECK OPTION选项创建约束视图

3.3 强制创建视图 
正常情况下，如果基本表不存在，则创建视图会失败
但如果创建视图的语句没有语法错误，则只要使用FORCE选项即可创建该视图，这种强制创建的视图被称为带有编译错误的视图
此时，这种视图处于失效状态，不能执行该视图，但之后随着基础表的创建，该视图就可以正常运行了

3.4 在连接视图上执行DML操作
对于在视图上进行的所有DML操作，最终都会在基础表的数据上完成
对视图进行更新（包括UPDATE、DELETE、INSERT）操作，则会受到某些限制，即并非在所有的视图上都可以执行全部的DML操作
对于连接视图，有些可以更新，但有些不能更新
可更新连接视图
不包含集中运算符（UNION、UNION ALL、INTERSECT、MINUS等）
不包含DISTINCT关键字
不包含GROUP BY、ORDER BY、CONNECT BY 或START WITH子句
不包含子查询
不包含分组函数
需要更新的列不是由列表达式定义的
表中所有的NOT NULL列均属于该视图
还需要遵守更新标准，也就是只能对“键值保存表”进行更新
键值保存表
如果连接视图中的一个基础表的主键（主键、唯一键）在它的视图中仍然存在，则称这个基础表为键值保存表

3.5 查询视图信息
使用USER_UPDATABLE_COLUMNS数据字典查询视图的可更新列
使用USER_VIEWS数据字典查询视图的定义信息

3.6 删除视图
可以删除当前模式中的各种视图，需要删除其他方案中的视图时，必须拥有DROP ANY VIEW系统权限 
语法
DROP VIEW view_name

4. 索引
4.1 索引概述
通过在表中的一个或多个列上创建索引，就能为数据的检索提供快捷的存取路径，减少查询时的硬盘I/O操作，加快数据的检索速度
与其他具有独立存储结构的方案对象类似，索引需要占用实际的存储空间
一旦创建了索引，在表上执行DML操作时，Oracle就会自动地对索引进行维护
如果将表看成一本书，则索引的作用类似于书中的目录

4.2 创建索引
即使在表中创建了索引，Oracle也不是机械地为该表上的所有查询都使用索引，而是根据查询的具体情况决定是否使用索引
在运行查询语句之前，Oracle一般需要对其进行优化。优化的目的是找到运行该查询语句的最佳途径
优化器会将使用全表扫描所需的资源开销与使用索引所需的资源开销进行对比，如果使用全表扫描所需的资源开销更节省，则不会使用索引
使用全表扫描所需的资源与表中数据量的大小密切相关。一般而言，当表中数据量达到一定数量时，优化器才会考虑使用索引

4.3 索引的分类
单列索引与复合索引
一个索引可以由一个或多个列组成，用于创建索引的列被称为“索引列”
单列索引是基于单个列所创建的索引，复合索引是基于多列所创建的索引
唯一索引与非唯一索引
唯一索引是索引列值不能重复的索引，非唯一索引是索引列值可以重复的索引
无论是唯一索引还是非唯一索引，索引列都允许取NULL值
默认情况下，Oracle创建的索引是非唯一索引
标准（B-tree index，B树）索引
在使用CREATE INDEX语句创建索引时，默认创建的就是B树索引
B树索引可以是单列索引或复合索引、唯一索引或非唯一索引
B树索引按B树结构组织并存放索引数据
位图索引 
基数是指某个列可能拥有的不重复值的个数。例如，Sex列的基数为2（性别只能是男或女），MaritalStatus列的基数为3（婚姻状况只能是未婚、已婚、离异）
对于一些基数很小的列，B树索引处理方式的效率比较低
对于基数很小、只存在有限的几个固定值的列（如性别、婚姻状态、行政区、职称），为了加快查询效率，应该在这些列上创建位图索引
当某列的基数与表的总行数的比例小于1%时，Oracle建议在列上创建位图索引
在表上创建一个单独的位图索引是无意义的，位图索引的作用来源于与其他位图索引的结合
函数索引 
在Oracle中，不仅能够对表中的列创建索引，还可以对包含有列的函数或表达式创建索引，这种索引被称为“函数索引” 
根据函数或表达式的结果的基数情况，函数索引既可以采用普通的B树索引，也可采用位图索引

4.4 创建各类索引
唯一索引
在emp表的ename列上创建一个唯一索引idx_emp_ename，创建之后该表中ename列就不允许出现重复值
复合索引
如果SELECT语句中的WHERE子句引用了复合索引中的所有列或大多数列，则使用复合索引可以显著地提高查询速度
创建此类索引时，应该注意定义中使用的列的顺序，通常，最频繁访问的列应该放置在列表的最前面
位图索引
由于emp表的job列、deptno列的取值范围有限，并且经常需要基于这些列进行查询、统计、汇总工作，所以应该基于这些列创建位图索引
函数索引
为了使在查询条件中包含函数（包括SQL的内置函数或用户自定义函数）和表达式的查询语句的执行效率提高，可以适当地创建函数索引
在创建函数索引时，Oracle首先对包含索引列的函数值或表达式值进行求值，然后对求值后的结果进行排序，最后再将结果存储到索引中

4.5 合并、重建索引
随着不断地对表进行更新操作，表的索引中会产生越来越多的存储碎片，这将对索引的工作效率产生负面影响
用户可以通过两种方式来清除碎片：合并索引、重建索引

4.6 监视索引的使用情况
已经创建的索引是否能够有效地工作，取决于在执行SQL语句的过程中Oracle是否会使用到该索引
Oracle提供了一种比较简便的方法来监视索引的使用情况，即在v$object_usage数据字典动态性能视图中记录索引的使用情况
步骤
使索引处于被监视状态，查询v$object_usage，了解索引的被监视情况 
执行SQL语句，查看被监视索引的使用情况
关闭索引监视状态

4.7 查看索引信息
使用user_indexes数据字典视图查看一个表中所有的索引信息
INDEX_NAME：表示索引名
INDEX_TYPE：表示索引类型。其中，FUNCTION-BASED NORMAL表示基于函数的B树索引，BITMAP表示位图索引，NORMAL表示普通的B树索引
UNIQUENES：表示该列是否是唯一索引

4.8 创建索引的原则
一般不需要为数据量很小的表创建索引
对于数据量比较大的表，如果经常需要查询的记录数小于表中所有记录数的15%，则可以考虑为该表创建索引
应该为大部分列值不重复的列创建索引
对于取值范围较大的列（如ename列），应该创建B树索引；对于取值范围较小的列（如sex列），应该创建位图索引
对于包含很多个NULL值，但是经常需要查询所有非NULL值记录的列，应当创建索引
不能在LONG或BLOB等大对象数据类型的列上创建索引
如果在大部分情况下只需要对表执行只读操作，就可以为该表创建更多的索引以提高查询速度
如果在大部分情况下需要对表执行更新操作，则应该为少创建一些索引，以提高更新速度

 总结
方案是数据库对象的集合，一个用户一般对应一个方案
同义词是方案对象的一个别名，常用于简化对象和提高对象访问的安全性
公用同义词由一个特殊的用户组PUBLIC所拥有，数据库中所有的用户都可以使用公用同义词
序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数
视图（VIEW）是由SELECT子查询语句定义的一个逻辑表，只有定义而无数据，是一个“虚表”。视图是查看和操作表数据的一种方法
使用视图有诸多优点，如提供各种数据表现形式、提供某些数据的安全性、隐藏数据的复杂性、简化查询语句、执行特殊查询、保存复杂查询等
在表中的一个或多个列上创建索引，能够为数据的检索提供快捷的存取路径，减少查询时的硬盘I/O操作，加快数据的检索速度
对于经常需要进行连接（join）查询的多个表而言，在用于连接的列上创建索引能够显著地提高查询的速度
如果在大部分情况下只需要对表执行只读操作，则可以为该表创建更多的索引，以提高查询速度