Oracle学习笔记九 数据库对象
Oracle 数据库对象又称模式对象，数据库对象是逻辑结构的集合，最基本的数据库对象是表。
其他数据库对象包括：
同义词
同义词是现有对象的一个别名。它的作用有：
1.简化SQL语句
2.隐藏对象的名称和所有者(隐藏原始对象内容)
3.提供对对象的公共访问
同义词共有两种类型：
公有同义词可被所有的数据库用户访问。
私有同义词只能在其模式内访问，且不能与当前模式的对象同名。
创建或替换现有同义词
create or replace synonym emp_syn for scott.emp;
删除同义词
drop synonym emp; drop public synonym emp_syn
使用同义词
Create public synonym Newer for scott.emp2; Select * from Newer ;
序列
序列是用于生成唯一、连续序号的对象，序列可以是升序的，也可以是降序的，可以用于表示数据的数量。我们可以使用CREATE SEQUENCE语句创建序列。
Create sequence 序列名 Start with 1 --指序列开始大小，默认为1 Increment by 1 --每次的增量，默认为1 Maxvalue 2000 --序列的最大数 Minvalue 1 --序列的最小数 Nocycle --当序列最大了将不再生成整数（默认） Cache 10; --保留在内存中整数的个数，默认20
注意：序列在使用的时候，如果给的是主键，为防止出现不连续的情况，应该设置为NOCACHE。
从user_sequences中查出所有的序列信息。
select * from user_sequences;
通过序列的伪列来访问序列的值
1.NEXTVAL 返回序列的下一个值
2.CURRVAL 返回序列的当前值
注意：序列在使用的时候，如果给的是主键，为防止出现不连续的情况，应该设置为NOCACHE。
INSERT INTO toys (toyid, toyname, toyprice) VALUES ( toys_seq.NEXTVAL, ‘TWENTY’, 25); INSERT INTO toys (toyid, toyname, toyprice) VALUES ( toys_seq.NEXTVAL, ’MAGIC PENCIL’, 75); --toys_seq.NEXTVAL 指定序列的下一个值
SELECT toys_seq.CURRVAL FROM dual; --检索序列的当前值
使用ALTER SEQUENCE语句修改序列，不能更改序列的START WITH参数
ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;
使用DROP SEQUENCE语句删除序列
DROP SEQUENCE toys_seq;
视图
视图以经过定制的方式显示来自一个或多个表的数据，视图可以视为“虚拟表”或“存储的查询”，创建视图所依据的表称为“基表”。
视图的优点有：
1.提供了另外一种级别的表安全性
2.隐藏的数据的复杂性
3.简化的用户的SQL命令
4.隔离基表结构的改变
5.通过重命名列，从另一个角度提供数据
例：
CREATE VIEW stud_view AS SELECT studno, studname, subno FROM Stud_details;
通过这个视图，查询到的结果：
创建视图
创建视图的语法：
CREATE [OR REPLACE] [FORCE] VIEW view_name [(alias[, alias]...)] AS select_statement [WITH CHECK OPTION][CONSTRAINT C_NAME] [WITH READ ONLY]; --禁止对表进行更新删除操作 --W ITH CHECK OPTION CONSTRAINT _NAME ：表示增加一个检查约束，INSERT和UPDATE时候必须要求: --INSERT时候要求插入的数据必须符合定义时候设置的where条件一致，UPDATE 时候只能改除where条件列以外的其他数据列或者修改的条件列的值符合where条件 --
使用 WITH CHECK OPTION 选项创建视图
create or replace view pause_view as select * from order_master WHERE ostatus = 'p' with check option constaint chk_pv;
使用 ORDER BY 子句创建视图
create or replace view ord_ven as select * from vendor_master order by venname;
联接视图:两个表的结果
create view Stud_sub_view as select Studno, Studname, Submrks, Subname from Stud_details, Sub_Details where Stud_details.Subno=Sub_details.Subno;
创建外联接视图
CREATE VIEW ven_ord_outj_view AS SELECT vm.vencode, venname, orderno, odate, ostatus FROM vendor_master vm, order_master om WHERE vm.vencode = om.vencode(+);
等同于
SELECT vm.vencode, venname, orderno, odate, ostatus FROM vendor_master vm LEFT OUTER JOIN order_master om ON vm.vencode = om.vencode;
视图上的DML语句
在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE
视图上的DML语句有如下限制：
1.只能修改一个底层的基表
2.如果修改违反了基表的约束条件，则无法更新视图
3.如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图
4.如果视图包含伪列或表达式，则将无法更新视图
如果往视图中添加数据，数据可以添加到基表，但如果插入的数据不符合视图的where等条件的要求，则视图中不会显示。如果希望插入的数据要符合视图定义的时候，where要确则需要添加参数。
如果要从视图中删除基表的数据，被删除的这条数据必须符合视图的查询条件，才能通过视图删除该数据。
键保留表
键保留表是理解连接视图修改限制的一个基本概念。该表的主键列全部显示在视图中,并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连接视图中也是键值，那么就称这个表为键保留表。
因为 Studno 既是Stud_details 中的主键，也是联接结果中的主键，因此Stud_details是键保留表
视图中的函数
视图中可以使用单行函数、分组函数和表达式
CREATE VIEW item_view AS SELECT itemcode, LOWER(itemdesc) item_desc FROM itemfile;
删除视图
使用DROP VIEW语句删除视图
DROP VIEW toys_view;
索引
索引是与表相关的一个可选结构，使用索引可以提高 SQL 语句执行的性能，减少磁盘I/O，索引在逻辑上和物理上都独立于表的数据，Oracle 能够自动维护索引。
索引的原理：索引的工作原理是与一个叫做ROWID的概念紧密相关的，Oracle在创建数据表时，默认会为每个数据表建立一个隐含字段，叫ROWID。在向数据表中插入记录时，系统将自动为每条记录分配惟一的一个ROWID号，利用这个ROWID号，就可以快速定位到记录。
操作索引
使用 CREATE INDEX 语句创建索引：
创建标准索引：Create index 索引名 on 表名(列名);
CREATE INDEX item_index ON itemfile (itemcode) TABLESPACE index_tbs;
重建索引
ALTER INDEX item_index REBUILD;
删除索引
DROP INDEX item_index;
索引的类型
索引有各种类型，除了标准索引外，还有一些特殊类型的索引：
唯一索引
唯一索引确保在定义索引的列中没有重复值，Oracle 自动在表的主键列上创建唯一索引
使用CREATE UNIQUE INDEX语句创建唯一索引
CREATE UNIQUE INDEX item_index ON itemfile (itemcode);
组合索引
组合索引是在表的多个列上创建的索引，索引中列的顺序是任意的，如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度。
CREATE INDEX comp_index ON itemfile(p_category, itemrate);
反向键索引
反向键索引反转索引列键值的每个字节，通常建立在值是连续增长的列上，可以使数据均匀地分布在整个索引上
创建索引时使用REVERSE关键字
CREATE INDEX rev_index ON itemfile (itemcode) REVERSE;
ALTER INDEX rev_index REBUID NOREVERSE;
位图索引
位图索引适合创建在低基数列上，位图索引不直接存储ROWID，而是存储字节位到ROWID的映射，使用位图索引可以减少响应时间，节省空间占用。
CREATE BITMAP INDEX bit_index ON order_master (orderno);
索引组织表
索引组织表的数据存储在与其关联的索引中，索引中存储的是行的实际数据，而不是ROWID，它基于主键访问数据。
CREATE TABLE 命令与 ORGANIZATION INDEX 子句一起用于创建索引组织表。
CREATE TABLE ind_org_tab ( vencode NUMBER(4) PRIMARY KEY, venname VARCHAR2(20) ) ORGANIZATION INDEX;
普通表与索引组织表的比较
|
普通表 |
索引组织表 |
|
ROWID 唯一地标识行 |
主键唯一地标识行 |
|
隐式的 ROWID 列 |
没有隐式的 ROWID 列 |
|
基于 ROWID 的访问 |
基于主键的访问 |
|
顺序扫描返回所有行 |
完全索引扫描返回所有行，并按主键顺序排列 |
|
支持分区 |
不支持分区 |
基于函数的索引
基于一个或多个列上的函数或表达式创建的索引，表达式中不能出现聚合函数，不能在LOB类型的列上创建，创建时必须具有 QUERY REWRITE 权限，当查询使用了函数的时候，索引将不再使用，所以要建立函数索引。
CREATE INDEX lowercase_idx ON toys (LOWER(toyname));
SELECT toyid FROM toys WHERE LOWER(toyname)='doll';
索引中的分区
我们可以将索引存储在不同的分区中。
与分区有关的索引有三种类型：
1.局部分区索引 － 在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致
2.全局分区索引 － 在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关
3.全局非分区索引 － 在分区表上创建的全局普通索引，索引没有被分区
获取索引的信息
与索引有关的数据字典视图有：
1.USER_INDEXES － 用户创建的索引的信息
2.USER_IND_PARTITIONS － 用户创建的分区索引的信息
3.USER_IND_COLUMNS － 与索引相关的表列的信息
SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS ORDER BY INDEX_NAME, COLUMN_POSITION;
