oracle讲解Chapter3.ppt

1、数据库对象,第三章,目标,同义词 序列 视图 索引,一、同义词 2- 1,数据库对象的替换名称，用作表、视图、序列、过程、存储函数、程序包、实体化视图或其他同义词的别名或替换名称 优点： 简化 SQL 语句 隐藏对象的名称和所有者 为分布式数据库的远程对象提供了位置透明性 提供对对象的公共访问,同义词 2- 2,同义词类型 私有 公用 CREATE SYNONYM 命令用于创建同义词 DROP SYNONYM 命令用于删除同义词 USER_SYNONYMS 包含有关同义词的信息,同义词的创建及使用,语法 CREATE PUBLIC SYSNONYM FOR objcet; 示例 SELECT

2、* FROM Marketing.vendor_master; CREATE SYSNOSYM vendor_master FOR Marketing.vendor_master; 删除 DROP PUBLIC SYNONYM schema.synonym; DROP SYNONYM vendor_master;,注意事项,私有同义词是由普通用户创建的，并且有创建该同义词的用户才能使用 公用同义词是由DBA创建的，任何数据库用户都可以使用它。用于隐藏基表的身份，并降低SQL语句的复杂性 当公用对象和本地对象具有相同的名称时，本地对象优先，但在删除本地对象之后，可以像平常一样使用公用同义词,二、

3、序列2 - 1,生成唯一、连续的整数 用于生成主键值 Create Sequence 语句用于创建序列 序列可以是升序排序，也可以是降序排序,序列语法,CREATE SEQUENCE INCREMENT BY n START WITH n MAXVALUE n MINVALUE n CYCLE|NOCYCLE CACHE n|NOCACHE; 注：INCREMENT BY n:指定序列数字之间的整数间隔 START WITH n:指定要生成的第一个序列号 MINVALUE n:指定序列的最小值 MAXVALUE n:指定序列可以生成的最大值 CYCLE:指定序列即使达到了最大值，还应继续生成值

4、,通常 是循环从头开始产生序列值 CACHE:允许更快地生成序列号，Oracle分配序列号，并将其保存在内存中以便更快地访问,示例,示例 CREATE SEQUENCE venseq INCREMENT BY 1 START WITH 1 MAXVALUE 10 MINVALUE 1 CYCLE CACHE 4;,序列应用,访问序列 nextval:第一次使用nextval时,将返回该序列的初始值 currval:返回序列的当前值,即最后一次引用nextval时返回的值 SELECT vdnseq.NEXTVAL FROM dual; SELECT venseq.CURVAL FROM dua

5、l; INSERT INTO vendor_master(vencode,venname) VALUES(V | venseq.nextval,vijay),修改序列,Alter Sequence 可对序列进行修改，需要 设置或删除最小或最大值 更改增加值 更改高速缓存的序列号的数字 不能用于修改START WITH参数的值 语法： ALTER SEQUENCE INCREMENT BY n MAXVALUE nMINVALUE n CYCLE|NOCYCLE CACHE n|NOCACHE; 示例： ALTER SEQUENCE member_num INCREMENT BY 1 MAXVA

6、LUE 999 MINVALUE 1;,删除序列,Drop Sequence 语法： DROP SEQUENCE ; 示例 DROP SEQUENCE member_seq; 查看序列： select * from seq;,三、视图 5- 1,经过定制的表示方式，用来显示来自一个或多个表的数据 也称为“虚拟表”或“已存储的查询” 基表 - 创建视图所依据的表,视图5 - 2,视图的优点 提供了另外一种级别的表安全性 隐藏数据的复杂性 简化用户的 SQL 命令 将应用程序与基表定义的修改隔离开来 从另一个角度提供数据,创建视图,CREATE OR REPLACE VIEW viewname(a

7、lias,alias,) AS subquery WITH CHECK OPTION CONSTRAINT constraintname WITH READ ONLY CONSTRAINT constraintname OR REPLACE:存在，就替换 WITH CHECK OPTION:只能插入或更新视图可以访问的行 WITH READ ONLY:不能在视图上执行任何DML操作,创建视图(续),示例 CREATE VIEW emp_dept10(no, name, salary) AS SELECT empno, empname, salary FROM employee WHERE de

8、ptno = 10;,创建视图(续),CREATE VIEW emp_dept10(no,dno,name, salary) AS SELECT empno,deptno, empname, salary FROM employee WHERE deptno = 10 WITH CHECK OPTION CONSTRAINT empenv UPDATE emp_dept10 SET no=20 WHERE dno=10; 不允许更新这些行,创建视图(续),CREATE OR REPLACE VIEW empview AS SELECT * FROM emp WITH READ ONLY UPD

9、ATE empview SET deptno=33; 不允许更新只读视图,ORDER BY 视图,ORDER BY 子句可以用来按特定的顺序对行进行排列 CREATE OR REPLACE VIEW empview2 AS SELECT * FROM EMP ORDER BY deptno DESC;,联接视图(SQL语句是多个表的连接),通过联接多个表来创建视图 DML 只能修改一个基表 通过INSTEAD OF触发器来修改,视图中的函数,在视图中可以使用函数和表达式 CREATE VEIW itemsold AS SELECT itemcode,SUM(qty_ord) sold_qty

10、FROM order_detail GROUP BY itemcode; 注意事项 不能选择伪列，如currval,nextval 视图包括联接,集合操作符,分组函数,DISTINCT子句，不能执行删除、更新、插入操作 在视图中所作的修改将影响基表，反之亦然 分组函数和GROUP BY子句也可以包含在视图中 使用函数时，应为列指定一个别名,删除视图,DROP VIEW 语句 从数据库中删除视图 DROP VIEW empview; 有关视图的信息 查询 USER_VIEWS,四、索引10 - 1,与表关联的可选结构 加快 SQL 语句的执行 减少磁盘 I/O CREATE INDEX 语句用于

11、创建索引 在逻辑上和物理上独立于表中的数据 Oracle 自动维护索引,1、唯一索引,确保在定义索引的列中没有重复的值 Oracle 自动为主键和唯一键列创建唯一索引 CREATE UNIQUE INDEX 语句用于创建唯一索引 CREATE UNIQUE INDEX ind2 ON order_master(orderno);,在表的多个列上创建的索引 也称为“连接索引” 组合索引中的列可以按任意顺序排列 对于在 WHERE 子句中包含多个列的查询，可以提高数据访问速度 CREATE INDEX combind1 ON order_detail(orderno,itemcode);,2、组合

12、索引,表的数据存储在与其关联的索引中 对表数据的增删改只会导致对索引的更新,5、索引组织表,普通表与索引组织表的对比,索引10 - 7,索引的分类,B*tree:平衡树 适用于OLTP Bitmap:位图 适用于OLAP,索引说明,Oracle的索引主要包含两类：BTree和位图索引。默认情况下大多使用Btree索引，该索引就是通常所见 唯一索引、聚簇索引等等，Btree用在OLTP，加快查询速度。位图索引是Oracle的比较引人注目的地方，其主要用在OLAP（联机数据分析）方面，也就是数据仓库方面用到，目的是在加快查询速度是，节省存储空间。通常情况下，索引都要耗费比较大的存储空间，位图采用了

13、压缩技术实现磁盘空间缩减。Btree用在高基数（即列的数据相异度大），位图用在低基数列。位图索引的基本原理是在索引中使用位图而不是列值。通常在事实表和维表的键之间有很低的集的势（cardinality），使用位图索引，存储更为有效，与B*Tree索引比较起来，只需要更少的存储空间，这样每次读取可以读到更多的记录，而且与B*Tree索引相比,位图索引将比较,连接和聚集都变成了位算术运算,大大减少了运行时间，从而得到性能上的极大的提升。,什么时候采用位图索引,基数是指某个列可能拥有的不重复数值的个数。比如说在一个中华人民共和国公民的信息表中，包含着十几亿条的记录。但是在这些记录中，有几个比较特殊的

14、列，其指包含几个有限的重复数值。如性别这一字段，其就只有男与女两个可能值;如在民族一列内也只有56个不重复的值;如在出身地一列内，中国只有34个省市自治区。 有时候用户可能需要根据这些列来查询相关的数据。如公司搞活动的时候，可能需要确认一下活动有没有涉及到宗教的禁忌，如可能涉及到回族的禁忌的话，那么就需要查询一下员工信息系统，看看公司有没有回族的员工。如公司可能在三八妇女节的时候，会给女员工提供一定的福利，为此也需要在数据库中拉出女员工的名单，此时也就是根据性别这个字段来查询数据。等等。如果用户查询的列的基数非常的小，只是有限的几个固定值，如性别、民族、行政区等等。要为这些基数值比较小的列建立

15、索引时，就需要建立位图索引。如果一定要说一个具体的标准的话，那么如果基数值在整个表记录的2%以内或者字段内容的重复值在100次以上，则通过位图索引可以起到不错的效果。,将索引键拆分为前缀项和后缀项 在一个索引块中，通过后缀项共享前缀项即可达到压缩的目的 节省磁盘空间 在一个索引块中可以存储更多的键 COMPRESS 子句用于创建这种索引 CREATE INDEX indexname ON tablename(c1,c2,) COMPRESS 1;,7、键压缩索引(了解),分区索引 索引存储在不同的分区中并对应不同的表空间，与表分区一样 分区索引的类型 本地索引 本地前缀索引 本地无前缀索引 全

16、局索引 全局前缀索引 全局无前缀索引,8、分区索引,分区索引,CREATE INDEX indexname ON tablename(c1,c2,) GLOBAL/LOCAL PARTITION BY RANGE(c2) PARTITION VALUES LESS THAN(v1) TABLESPACE tb1, PARTITION VALUES LESS THAN(v2) TABLESPACE tb2; 本地索引 CREATE INDEX myind ON ord_mast(orderno) LOCAL; 分区索引中的分区键应引用与基础表分区相同的行 使用USER_SEGMENTS查看分区索引 SELECT segment_name,partition_name,segment_type,tablespace_name FROM user_segments WHERE segment_name=MYIND;,全局索引,引用存储在多个基础分区中的行，如果索引是基于索引列的左前缀进行分区的，则称该索引是全局前缀索引 基于多列进行分区的表为例，在第一列上创建索引，称前缀索引，在第二列上创建索引，则会显示错误消息 示例：参见P9