第05讲 索引同义词数据库链接

1、数据库模式对象,表、视图 索引 同义词 数据库链接 序列,MERGE INTO tdest dUSING tsrc sON (s.srckey = d.destkey)WHEN MATCHED THENUPDATE SET d.destdata = d.destdata + s.srcdataWHEN NOT MATCHED THENINSERT (destkey, destdata) VALUES (srckey, srcdata),复习,oracle集合操作,UNION UNION ALL INTERSECT MINUS,数据库模式对象,表、视图 索引 同义词 数据库链接 序列,创建视图,

2、2.使用CREATE VIEW语句创建视图 PL/SQL中用于创建视图的语句是CREATE OR REPLACE VIEW语句。语法格式： CREATE OR REPLACE FORCE | NOFORCE VIEW schema.view_name ( column_name ,n ) AS select_statement WITH CHECK OPTION WITH READ ONLY,创建视图,【例1】创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。 CREATE OR REPLACE VIEW CS_KC AS

3、 SELECT XS.XH,KCH,CJ FROM XS,XS_KC WHERE XS.XH=XS_KC.XH AND ZYM= 计算机 WITH CHECK OPTION;,4.3.2创建视图,【例4.50】创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。 CREATE OR REPLACE VIEW CS_KC_AVG(num,score_avg) AS SELECT XH,AVG(CJ) FROM XS_KC GROUP BY XH;,4.3.3查询视图,1.SELECT语句查询视图 【例3】查找计算机专

4、业的学生学号和选修的课程号。 SELECT XH,KCH FROM CS_KC;,索引,如何提高查询效率?,索引 概述,如何提高查询效率? 索引是数据库中用于存放表中每一条记录位置的一种对象。可以通过该结构迅速访问表中的数据并进行完整性检查。 创建索引需要占用许多存储空间，而且在向表中添加和删除记录时，数据库需要花费额外的开销来更新索引。 索引可以从逻辑设计和物理实现两个方面来分类。,3.3索引,3.3.1索引的分类 索引按存储方法分类，可以分为2类：B*树索引和位图索引。 (1) B*树索引。B*树索引的存储结构类似书的索引。该索引就是通常所见唯一索引、聚簇索引等等， Btree用在OLTP

5、(联机事务处理), 加快查询速度。OLTP是面向顾客的,用于事务和查询处理。,树形索引,2 3 树示例,包含1个或者2个关键码； 有2个子女或者3个子女； 叶子结点在同一层。,18 33,12,23 30,48,10,15,20 21,24,31,45 47,50 52,3.3索引,3.3.1索引的分类 （2）位图索引。位图索引储存主要用来节省空间，减少ORACLE对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。其主要用在OLAP（On line analytical processing) ，数据仓库方面用到，目的是在加快查询速度，节省存储空间。

6、 例如：性别列只有“男”和“女”两种值，所以适合位图索引,索引,3.3索引,索引按功能和索引对象还有以下类型： (1) 唯一索引。唯一索引意味着不会有两行记录相同的索引键值。 (2) 非唯一索引。不对索引列的值进行唯一性限制的所以称为非唯一索引。 (3) 分区索引。所谓分区索引是指索引可以分散地存在于多个不同的表空间中，其优点是可以提高数据查询的效率。 (4) 未排序索引。未排序索引也称为正向索引。Oracle 10g数据库中的行是按升序排序的，因此创建索引时不必指定对其排序而使用默认的顺序。,3.3索引,索引按功能和索引对象还有以下类型： (5) 逆序索引。逆序索引也称为反向索引。该索引同样

7、保持索引列按顺序排列，但是颠倒已索引的每列的字节。反向索引是B*Tree索引的一个分支，Oracle推出它的主要目的就是为了降低在并行服务器（Oracle Parallel Server）环境下索引叶块的争用 。 (6) 基于函数的索引。基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或者表达式计算索引列的值。,3.3.3创建索引,创建索引有三种方法： 随数据库表创建 使用Oracle 10g管理控制台工具单独创建 使用SQL命令创建索引。,3.3.3创建索引,2. 在OEM中创建索引 对索引进行分区有2种方法： 全局分区索引与本地分区索引的区别就是索引的分区键是否与表

8、的分区键一致，一致的是本地分区索引，不一致的是全局分区索引。,3.3.3创建索引,3. 利用SQL命令建立索引 在使用SQL命令创建索引时，必须满足下列条件之一： (1) 索引的表或簇必须在自己的模式中； (2) 必须在要索引的表上具有INDEX权限； (3) 必须具有CREATE ANY INDEX权限。,3.3.3创建索引,语法格式： CREATE UNIQUEBITMAP INDEX /*索引类型*/ schema.index_name /*索引名称*/ ON schema.table_name (column_nameASCDESC,n, column_expression) CLUS

9、TER schema.cluster_name INITRANS integer MAXTRANS integer PCTFREE integer PCTUSED integer TABLESPACE tablespace_name STORAGE storage_clause NOSORT REVERSE,3.3.3创建索引,3. 利用SQL命令建立索引 UNIQUE：指定索引所基于的列(或多列)值必须唯一。默认的索引是非唯一索引。 BITMAP：指定建成位映射索引而不是B*索引。 Schema：表示包含索引的方案。 ON table_name：建立table_name表索引。 column

10、_expression：创建基于函数的索引。 ON CLUSTER：创建cluster_name簇索引。 NOSORT：数据库中的行以升序保存，在创建索引时不必对行排序。 REVERSE：指定以反序索引块的字节，不包含行标识符。,3.3.3创建索引,【例3.12】为KC表的课程名列创建索引。 CREATE INDEX scott.kc_name_idx ON scott.KC(KCM) TABLESPACE users; 【例3.13】为XS表的姓名列创建索引，指定索引的物理和存储特征值，数据库中的行以升序保存。 CREATE INDEX scott. xm_idx ON scott.XS(X

11、M) TABLESPACE USERS INITRANS 2 MAXTRANS 255 NOSORT;,反转键索引(了解),颠倒已索引的每列的字节。反向索引是B*Tree索引的一个分支，Oracle推出它的主要目的就是为了降低在并行服务器（Oracle Parallel Server）环境下索引叶块的争用 。 例如，在emp示例中，可以使用下面的命令创建反转键索引： create index xm_idx on xs(xm) reverse;,基于函数的索引,创建基于函数的索引与创建普通B树索引没有大的区别，只需要在列上添加函数即可。 【例】在xs表的xm列上创建基于LOWER函数的索引，如下

12、： CREATE INDEX name_lower_index ON xs(LOWER(xm) TABLESPACE users;,5.5.3 创建位图索引,位图(位映射)索引与B树索引不同，位图索引不存储ROWID值，也不存储键值，它一般在包含少量不同值的列上创建。 例：在xs表的xb列上创建索引，则应该创建位图索引。创建位图索引的简单语法形式如下： CREATE BITMAP INDEX index_name ON table_name ( column_name , . ) TABLESPACE tablespace_name ; create bitmap index xb1 on x

13、s(xb) tablespace users;,索引,select index_name,table_name from user_indexes where table_name=XS;,3.3.4维护索引,2. 利用ALTER INDEX命令维护索引 语法格式： ALTER INDEX schema.index_name INITRANS integer MAXTRANS integer PCTFREE integer /*建立索引的物理和存储特征值*/ STORAGE storage_clause /*为索引建立存储特征*/ RENAME TO new_index_name,3.3.4维

14、护索引,2. 利用ALTER INDEX命令维护索引 【例3.14】修改例3.12中创建的索引kc_name_idx。 ALTER INDEX xm_idx INITRANS 2 MAXTRANS 128; 【例3.15】重命名索引kc_name_idx。 ALTER INDEX xb_idx RENAME TO xsxb_idx;,3.3.5删除索引,2. 利用SQL命令删除索引 语法格式： DROP INDEX schema.index_name 其中，schema是包含索引的方案。index_name是要删除的索引名称。 【例3.16】删除XSCJ数据库中表XS的一个索引名为XS_NAM

15、E_IDX的索引。 DROP INDEX xsxb_idx;,模式对象,表、视图、索引、同义词、数据库链接、序列,5.8 同 义 词,Oracle支持为表、索引或视图等模式对象定义别名，也就是为这些对象创建同义词。Oracle中的同义词主要分为如下两类。 公有同义词：在数据库中的所有用户都可以使用。 私有同义词：由创建它的用户私人拥有。不过，用户可以控制其他用户是否有权使用自己的同义词。,3.4.1创建同义词,2. 利用CREATE SYNONYM命令创建同义词 语法格式： CREATE PUBLIC SYNONYM schema.synonym_name FOR schema.object

16、dblink 【例3.18】创建同义词。 (1) 为XSCJ数据库的XS_KC表创建公用同义词XS_KC。 CREATE PUBLIC SYNONYM XS_KC FOR SYSTEM.XS_KC;,3.4.1创建同义词,在分布式数据库中，为了识别一个数据库对象如表和视图，必须规定主机名，服务器名，对象的拥有者和对象名。 例如 605_1.orcl.system.xs 同义词不但可以应用在表的命名中，也可以应用在视图、序列、存储过程和函数以及包中，因此它的应用范围更广泛.,3.4.1创建同义词,(2) 为XSCJ数据库XS表创建远程数据库同义词。 CREATE PUBLIC SYNONYM X

17、S FOR SCOTT.XSMY_LINK; (3) 为XSCJ数据库的CS_XS视图创建公用同义词CS_XS。 CREATE PUBLIC SYNONYM KC FOR SCOTT.KC;,3.4.2使用同义词,【例3.19】SYSTEM用户查询XSCJ数据库XS表中所有学生的情况。 SELECT * FROM KC; 如果没有为XSCJ数据库XS表创建同义词XS，那么SYSTEM用户查询XS表则需指定XS表的所有者。 SELECT * FROM SCOTT.KC;,3.4.3删除同义词,利用DROP SYNONYM命令删除同义词 语法格式： DROP PUBLIC SYNONYM sche

18、ma.synonym_name 说明： PUBLIC表明删除一个公用同义词。Schema指定将要删除的同义词的用户方案。synonym_name为将要删除的同义词名称。 【例3.20】删除公用同义词CS_XS。 DROP PUBLIC SYNONYM CS_XS;,全限名包括：主机名，服务器名，对象的拥有者和对象名。 当使用远程数据库时，必须指定远程数据库对象的全限名-同义词中只有全限名的两部分-所有者和表名-如果表在远程数据库中-创建一个数据库链接-指定远程数据库中一个对象的访问路径.,3.5.1 创建数据库链接,远程服务器的配置 net configuration assistant Sh

19、ow parameter service_name,3.5.1 创建数据库链接,在主机上编辑tnsnames.ora文件配置bdb数据库的tns别名tns_b，如下： tns_b = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.70.9.12)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME =ORCL1) ) 可以采用sqlplus username/passwordtnsname来测试创建的tns别名是否正确。,3.5.1 创建数据库链接,界面创建数据库链

20、接 数据库服务器名 用户名和口令,3.5.1 创建数据库链接,利用CREATE DATABASE LINK命令创建数据库链接 语法格式： CREATE PUBLIC DATABASE LINK dblink_name CONNECT TO user IDENTIFIED BY password USING connect_string; connect_string网络服务器名 【例3.22】为orcl1数据库创建一个名为MY_PLINK的公用链接。 create public database link my_link connect to SYSTEM identified by orcl

21、1 using ORCL1 ;,3.5.2使用数据库链接,创建了数据库链接，就可以使用远程数据库的对象了。 【例3.23】查询远程数据库XSCJ表KC中的所有课程情况。 SELECT * FROM ADMIN.KCMY_PLINK; 上述查询将通过MY_PLINK数据库链接来访问KC表，也可以为该表创建一个同义词。 【例3.24】为XSCJ远程数据库表KC创建一个同义词。 CREATE PUBLIC synonym kc FOR system.kcMY_LINK; 这时数据库对象的全限定标志已被定义，其中包括通过服务名的主机和实例、通过数据库链接的拥有者(ADMIN)和表名(KC)。,实际应用

22、: 深圳ORACLE数据库，北京的数据库service-tobeijing SQLcreate public database link beijing connect to scott identified by tiger using tobeijing; SQLselect * from empbeijing; 为了使有关分布式操作更透明，ORACLE数据库里有同义词的对象synonym.,3.5.2使用数据库链接,查看数据库链接-dba_objects select owner,object_name from dba_objects where object_type=DATABAS

23、E LINK;,3.5.2使用数据库链接,3.5.3删除数据库链接,2. 利用DROP DATABASE LINK删除数据库链接 语法格式： DROP PUBLIC DATABASE LINK dblink_name dblink_name为要删除的数据库链接名称。 【例3.25】删除公用数据库链接MY_PLINK。 DROP PUBLIC DATABASE LINK my_link; 注意：公用数据库链接可由任何有相应权限的用户删除，而私有数据库链接只能由SYS系统用户删除。,序列,序列(sequence)定义存储在数据字典中。序列通过提供唯一整数值的顺序表用来简化程序设计工作. 不管哪个用户或进程使用了序列生成器中的一个值，则下一个用户或者进程所使用的值是上一个值的后继值 。,创建序列,2.利用SQL命令创建序列 CREATE SEQUENCE schema. sequence_name START