11-12-2 第05讲 索引,同义词,数据库链接

数据库模式对象•表、视图•索引•同义词•数据库链接•序列•MERGEINTOtdestdUSINGtsrcsON(s.srckey=d.destkey)WHENMATCHEDTHENUPDATESETd.destdata=d.destdata+s.srcdataWHENNOTMATCHEDTHENINSERT(destkey,destdata)VALUES(srckey,srcdata)复习oracle集合操作•UNION•UNIONALL•INTERSECT•MINUS数据库模式对象•表、视图•索引•同义词•数据库链接•序列创建视图2.使用CREATEVIEW语句创建视图PL/SQL中用于创建视图的语句是CREATEORREPLACEVIEW语句。语法格式：CREATE[ORREPLACE][FORCE|NOFORCE]VIEW[schema.]view_name[(column_name[,…n])]ASselect_statement[WITHCHECKOPTION][WITHREADONLY]创建视图【例1】创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。CREATEORREPLACEVIEWCS_KCASSELECTXS.XH,KCH,CJFROMXS,XS_KCWHEREXS.XH=XS_KC.XHANDZYM='计算机'WITHCHECKOPTION;4.3.2创建视图【例4.50】创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。CREATEORREPLACEVIEWCS_KC_AVG(num,score_avg)ASSELECTXH,AVG(CJ)FROMXS_KCGROUPBYXH;4.3.3查询视图1.SELECT语句查询视图【例3】查找计算机专业的学生学号和选修的课程号。SELECTXH,KCHFROMCS_KC;索引•如何提高查询效率?索引概述如何提高查询效率?•索引是数据库中用于存放表中每一条记录位置的一种对象。可以通过该结构迅速访问表中的数据并进行完整性检查。•创建索引需要占用许多存储空间，而且在向表中添加和删除记录时，数据库需要花费额外的开销来更新索引。•索引可以从逻辑设计和物理实现两个方面来分类。3.3索引3.3.1索引的分类索引按存储方法分类，可以分为2类：B*树索引和位图索引。(1)B*树索引。B*树索引的存储结构类似书的索引。该索引就是通常所见唯一索引、聚簇索引等等，Btree用在OLTP(联机事务处理),加快查询速度。OLTP是面向顾客的,用于事务和查询处理。树形索引2–3树示例包含1个或者2个关键码；有2个子女或者3个子女；叶子结点在同一层。183312233048101520212431454750523.3索引3.3.1索引的分类（2）位图索引。位图索引储存主要用来节省空间，减少ORACLE对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。其主要用在OLAP（Onlineanalyticalprocessing)，数据仓库方面用到，目的是在加快查询速度，节省存储空间。例如：性别列只有“男”和“女”两种值，所以适合位图索引索引3.3索引索引按功能和索引对象还有以下类型：(1)唯一索引。唯一索引意味着不会有两行记录相同的索引键值。(2)非唯一索引。不对索引列的值进行唯一性限制的所以称为非唯一索引。(3)分区索引。所谓分区索引是指索引可以分散地存在于多个不同的表空间中，其优点是可以提高数据查询的效率。(4)未排序索引。未排序索引也称为正向索引。Oracle10g数据库中的行是按升序排序的，因此创建索引时不必指定对其排序而使用默认的顺序。3.3索引索引按功能和索引对象还有以下类型：(5)逆序索引。逆序索引也称为反向索引。该索引同样保持索引列按顺序排列，但是颠倒已索引的每列的字节。反向索引是B*Tree索引的一个分支，Oracle推出它的主要目的就是为了降低在并行服务器（OracleParallelServer）环境下索引叶块的争用。(6)基于函数的索引。基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或者表达式计算索引列的值。3.3.3创建索引创建索引有三种方法：•随数据库表创建•使用Oracle10g管理控制台工具单独创建•使用SQL命令创建索引。3.3.3创建索引2.在OEM中创建索引对索引进行分区有2种方法：全局分区索引与本地分区索引的区别就是索引的分区键是否与表的分区键一致，一致的是本地分区索引，不一致的是全局分区索引。3.3.3创建索引3.利用SQL命令建立索引在使用SQL命令创建索引时，必须满足下列条件之一：(1)索引的表或簇必须在自己的模式中；(2)必须在要索引的表上具有INDEX权限；(3)必须具有CREATEANYINDEX权限。3.3.3创建索引语法格式：CREATE[UNIQUE∣BITMAP]INDEX/*索引类型*/[schema.]index_name/*索引名称*/ON[schema.]table_name(column_name[ASC∣DESC],…n,[column_expression])∣CLUSTER[schema.]cluster_name[INITRANSinteger][MAXTRANSinteger][PCTFREEinteger][PCTUSEDinteger][TABLESPACEtablespace_name][STORAGEstorage_clause][NOSORT][REVERSE]3.3.3创建索引3.利用SQL命令建立索引UNIQUE：指定索引所基于的列(或多列)值必须唯一。默认的索引是非唯一索引。BITMAP：指定建成位映射索引而不是B*索引。Schema：表示包含索引的方案。ONtable_name：建立table_name表索引。column_expression：创建基于函数的索引。ONCLUSTER：创建cluster_name簇索引。NOSORT：数据库中的行以升序保存，在创建索引时不必对行排序。REVERSE：指定以反序索引块的字节，不包含行标识符。3.3.3创建索引【例3.12】为KC表的课程名列创建索引。•CREATEINDEXscott.kc_name_idx•ONscott.KC(KCM)•TABLESPACEusers;【例3.13】为XS表的姓名列创建索引，指定索引的物理和存储特征值，数据库中的行以升序保存。•CREATEINDEXscott.xm_idx•ONscott.XS(XM)•TABLESPACEUSERS•INITRANS2•MAXTRANS255•NOSORT;反转键索引(了解)•颠倒已索引的每列的字节。反向索引是B*Tree索引的一个分支，Oracle推出它的主要目的就是为了降低在并行服务器（OracleParallelServer）环境下索引叶块的争用。•例如，在emp示例中，可以使用下面的命令创建反转键索引：–createindexxm_idx–onxs(xm)reverse;基于函数的索引•创建基于函数的索引与创建普通B树索引没有大的区别，只需要在列上添加函数即可。•【例】在xs表的xm列上创建基于LOWER函数的索引，如下：•CREATEINDEXname_lower_index•ONxs(LOWER(xm))•TABLESPACEusers;5.5.3创建位图索引•位图(位映射)索引与B树索引不同，位图索引不存储ROWID值，也不存储键值，它一般在包含少量不同值的列上创建。•例：在xs表的xb列上创建索引，则应该创建位图索引。创建位图索引的简单语法形式如下：–CREATEBITMAPINDEXindex_name–ONtable_name(column_name[,...])–[TABLESPACEtablespace_name];–createbitmapindexxb1onxs(xb)tablespaceusers;索引•selectindex_name,table_namefromuser_indexeswheretable_name='XS';3.3.4维护索引2.利用ALTERINDEX命令维护索引语法格式：ALTERINDEX[schema.]index_name[INITRANSinteger][MAXTRANSinteger][PCTFREEinteger]/*建立索引的物理和存储特征值*/[STORAGEstorage_clause]/*为索引建立存储特征*/[RENAMETOnew_index_name]3.3.4维护索引2.利用ALTERINDEX命令维护索引【例3.14】修改例3.12中创建的索引kc_name_idx。ALTERINDEXxm_idxINITRANS2MAXTRANS128;【例3.15】重命名索引kc_name_idx。ALTERINDEXxb_idxRENAMETOxsxb_idx;3.3.5删除索引2.利用SQL命令删除索引语法格式：DROPINDEX[schema.]index_name其中，schema是包含索引的方案。index_name是要删除的索引名称。【例3.16】删除XSCJ数据库中表XS的一个索引名为XS_NAME_IDX的索引。DROPINDEXxsxb_idx;模式对象•表、视图、索引、同义词、数据库链接、序列5.8同义词•Oracle支持为表、索引或视图等模式对象定义别名，也就是为这些对象创建同义词。Oracle中的同义词主要分为如下两类。–公有同义词：在数据库中的所有用户都可以使用。–私有同义词：由创建它的用户私人拥有。不过，用户可以控制其他用户是否有权使用自己的同义词。3.4.1创建同义词2.利用CREATESYNONYM命令创建同义词语法格式：CREATE[PUBLIC]SYNONYM[schema.]synonym_nameFOR[schema.]object[@dblink]【例3.18】创建同义词。(1)为XSCJ数据库的XS_KC表创建公用同义词XS_KC。CREATEPUBLICSYNONYMXS_KCFORSYSTEM.XS_KC;3.4.1创建同义词★在分布式数据库中，为了识别一个数据库对象如表和视图，必须规定主机名，服务器名，对象的拥有者和对象名。例如605_1.orcl.system.xs同义词不但可以应用在表的命名中，也可以应用在视图、序列、存储过程和函数以及包中，因此它的应用范围更广泛.3.4.1创建同义词(2)为XSCJ数据库XS表创建远程数据库同义词。CREATEPUBLICSYNONYMXSFORSCOTT.XS@MY_LINK;(3)为XSCJ数据库的CS_XS视图创建公用同义词CS_XS。CREATEPUBLICSYNONYMKCFORSCOTT.KC;3.4.2使用同义词【例3.19】SYSTEM用户查询XSCJ数据库XS表中所有学生的情况。SELECT*FROMKC;如果没有为XSCJ数据库XS表创建同义词XS，那么SYSTEM用户查询XS表则需指定XS表的所有者。SELECT*FROMSCOTT.KC;3.4.3删除同义词利用DROPSYNONYM命令删除同义词语法格式：DROP[PUBLIC]SYNONYM[schema.]synonym_name说明：PUBLIC表明删除一个公用同义词。Schema指定将要删除的同义词的用户方案。synonym_name为将要删除的同义词名称。【例3.20】删除公用同义词CS_XS。DROPPUBLICSYNONYMCS_XS;•全限名包括：主机名，服务器名，对象的拥有者和对象名。•当使用远程数据库时，必须指定远程数据库对象的全限名----同义词中只有全限名的两部分---所有者和