OCP->047(Les10 Creating Other Schema Object)[20171213]

千家信息网最后更新 2024年09月22日OCP->047(Les10 Creating Other Schema Object)[20171213]目的： 创建简单和复杂视图 视图复取数据 创建、维护和使用序列 创建和维护索引 创建私有和公有同义词
数据库对象：

Object	Description
Table	Basic unit of storage; composed of rows
View	Logically represents subsets of data from one or more tables
Sequence	Generates numeric values
Index	Improves the performance of some queries
Synonym	Gives alternative names to objects

视图【View】 什么是视图？ 视图呈现逻辑子集或数据的组合。视图是基于表或其他视图的逻辑表。一个视图不包含自己的数据，而是像一个窗口，通过窗口可以查看或更改表中的数据。视图所基于的表称为基表。视图是存储SELECT 声明中的数据字典。
视图的好处： 视图可限制数据的访问，因为视图只显示选定的列。 简化查询，视图进行简单的查询来检索复杂连接。 提供数据独立性。一个视图可以从多个表中检索数据。 视图可根据特定标准为用户提供对数据的访问权限。
简单视图和复杂视图 简单视图： 仅仅从一个表导出数据 不包含函数或数组 视图可以执行DML操作 复杂视图： 从多个表导出数据 包含函数或数组 视图不允许DML操作
创建视图语法： CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery--子查询中可包含一个复杂的select查询。 [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];
OR REPLACE ：已存在的将进行覆盖重建。 FORCE ：无论基表是否存在，都进行创建。 NOFORCE ：使用基表存在的才能创建(默认值) view_name ：视图名称 alias ：别名为视图查询选择的表达式指定名称（别名数必须与视图选择的表达式数量相匹配。） subquery ：子查询是一个完整的SELECT语句（您可以在SELECT列表中使用别名）。 WITH CHECK OPTION ：指定仅可以插入或更新视图可访问的那些行 WITH READ ONLY ：确保在此视图上不能执行DML操作
视图上执行DML操作： 简单视图可以执行DML操作 含以下条件则不能对视图执行DML操作 -组函数 -GROUP BY子句 -DISTINCT 关键字 -ROWNUM 关键字 -列上含有表达式，如（salary * 12） -基表NOT NULL的列未在视图列表中。 移除视图： DROP VIEW view_name;
序列【Sequence】 序列是一个生成数值的数据库对象。可以创建序列，然后用来生成数值。 序列的特征： -能自动产生唯一的数值 -它是共享对象 -能用于主键值 -替换应用代码 -它的值缓存在内存中提升访问效率
序列是用户创建的数据库对象，可以由多个用户共享以生成整数。 您可以定义一个序列来生成唯一的值，或者重复使用相同的数字。 序列的典型用法是创建一个主键值，每个行必须是唯一的。 该序列由内部Oracle例程生成并递增（或递减）。 这可以是一个节省时间的对象，因为它可以减少编写序列生成例程所需的应用程序代码量。 序列号是独立存储和生成的。 因此，相同的序列可以用于多个表格。
创建序列的语法： CREATE SEQUENCE sequence_name [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
INCREMENT BY n：指定序列号之间的间隔，其中n是一个整数(如果忽略此值，则序列增加1) START WITH n：指定要生成的第一个序列号（如果省略此值，则序列以1开头） MAXVALUE n：指定序列可以生成的最大值 NOMAXVALUE：指定递增序列的最大值10^27，对递减序列指定-1(默认选项) MINVALUE n：指定最小序列值 NOMINVALUE：为递增序列指定最小值1，对于递增序列指定-（10^26）(默认选项) CYCLE | NOCYCLE：指定序列达到最大值或最小值是否继续生成值(NOCYCLE是默认选项) CACHE| NOCACHE：指定Oracle服务器预分频和保留在内存中的值(默认情况下，Oracle服务器缓存20个值)

CREATE SEQUENCE sequence_t INCREMENT BY 1 START WITH 10 MAXVALUE 999 NOCACHE NOCYCLE; 序列生成后需要NEXTVAL和 CURRVAL伪列引用序列值 NEXTVAL：伪列用于从指定序列中提取连续的序列号。必须使用序列名称来限定nextval。当引用sequence.nextval是，会生成一个新的序列号，并将当前序列置于currval。 CURRVAL：伪列用于引用当前用户刚生成的序列号。必须使用nextval在当前用户会话生成一个序列号，然后才能引用currval。 16:53:05 SQL> select sequence_t.nextval from dual; NEXTVAL ---------- 10 16:53:17 SQL> select sequence_t.currval from dual; CURRVAL ---------- 10
缓存序列的值： 在内存中缓存序列，可以快速访问这些序列值。当第一次参考序列时，将会自动填充缓存。从缓存的序列中检索下一个序列值，在使用到最后的序列值之后，序列的下一个请求会将另一个序列的缓存拉入内存中缓存。 序列的丢失： -连续生成序列的值时，在事务中回滚会导致数值丢失。 -系统的崩溃，导致缓存中的序列丢失。 -多表共用同一序列，会导致序列丢失。
修改序列： ALTER SEQUENCE sequence_name INCREMENT BY 20 MAXVALUE 99999 NOCACHE NOCYCLE;
删除序列： DROP SEQUENCE sequence_name;
索引【Index】 索引是数据库对象，用于提高某些查询的性能。创建主键或唯一性约束时数据库也会自动创建。
索引： -它是一个schema对象 -它使用指针关联每一个行提高访问速度 -它减少磁盘I/O，快速访问本地数据 -它依赖表 -数据库服务器自动维护和使用它。
Oracle服务器索引是一个模式对象，可以通过使用指针来加速行的检索。 索引可以显式或自动创建。 如果在列上没有索引，则会发生全表扫描。 索引提供对表中行的直接和快速访问。 其目的是通过使用索引路径快速定位数据来减少磁盘I / O。 该索引由Oracle服务器自动维护和使用。 索引创建后，用户不需要直接的活动。 索引在逻辑上和物理上独立于索引表。 这意味着它们可以随时创建或删除，并且不会影响基表或其他索引。 注意：删除表格时，相应的索引也会被删除。 索引类型： 唯一性索引：PRIMARY KEY和UNIQUE约束会自动创建唯一性索引。 非唯一性索引：用户自定义创建索引。 注意：创建唯一性索引时，建议创建一个唯一性约束，因为它会隐式的创建一个唯一性索引。
创建索引语法： CREATE INDEX schema.index_name ON schema.table_name (column[,column]...);
创建索引的条件： -列上的资料跨度大 -列上的资料有大量的空值 -一个或多个列经常在WHERE子句或连接条件中一起使用 -该表很大，大多数查询预计将检索少于2％到4％的行 不建议创建索引： -索引列没有用于SQL语句查询 -检索资料大于总资料的2%或4% -该列上有大量的更新操作 -该列查询被用于表达式
注意：索引越多并不是更好，表上索引越多不会产生更快的查询。在有索引列的表上进行DML操作意味索引也必须更新。索引依赖表被删除，索引也会跟着删除。
删除索引： DROP INDEX schema.index_name ; 重建索引： ALTER INDEX schema.index_name REBUILD [PARALLEL n];

14:40:24 SQL> create index hr.hr_emp_01 on hr.emp_01(salary); 14:40:51 SQL> alter index hr.hr_emp_01 rebuild parallel 4; 14:41:06 SQL> drop index hr.hr_emp_01;
同义词【Synonym】 同义词是能够以其他名称调用表(其他对象)的数据库对象。可以创建同义词，给一个表(或其他对象)的替代名称。 创建语法： CREATE [PUBLIC] SYNONYM synonym_name FOR schema.object_name; 同义词分类： PUBLIC:公有同义词，其他用户均可以访问。 PRIVATE:私有同义词，只能是创建用户所拥有或访问(默认值)。 删除同义词： DROP [PUBLIC] SYNONYM synonym_name; 注意：public公有同义词用户必须拥有drop public synonym权限才能删除。 14:57:16 SQL> drop public synonym dbmonitor_book2 15:00:20 2 ; drop public synonym dbmonitor_book2 * ERROR 在行 1: ORA-01031: 權限不足 15:00:25 SQL> drop synonym dbmonitor_book2 15:00:31 2 ; 已刪除同義字.