Oracle9i第9章 视图、序列、索引和同义词.ppt

第八章视图 序列 索引和同义词 建立视图 视图的使用与基表类似 要有视图名及相应的列 生成一个视图可使用CREATEVIEW命令 其语法格式如下 CREATEORREPLACEFORCEVIEWviewnameAS WITHREADONLY WITHCHECKOPITION ORREPLACE 允许新视图替换已存在的一个同名视图 FORCE 即使在基本表不存在或对这些表无访问权限时 也可建立视图 不过 这个视图在表被建立和被授权访问它们之前一直处于无效状态 WITHREADONLY 只允许查询访问视图 WITHCHECKOPITION 如果关联视图使用此选项建立 将完全不能插入 但能删除和修改 CREATEORREPLACEFORCEVIEWTMP VIEWASSELECTa empno a ename b dnameFROMempa DEPTbwherea deptno b deptno 使用视图 一旦建立了一个视图 就可以把它当作一个表从中查询数据 CREATEORREPLACEFORCEVIEWTMP VIEWASSELECTa empno a ename b dnameFROMempa DEPTbwherea deptno b deptno select fromtmp view Oraclec允许对视图执行UPDATE INSERT和DELETE语句 如果一个视图包括了基本表中的所有列 并仅有一个WHERE子句来限制那些返回的行 那么Oracle将允许更新这个视图 对于没有保存至少一个基本表的主键的那些视图是不能被更新的 另外一些处理汇部数据 基于表达式的列 联合和某种关联的视图也不能被更新 比如 带有INTERSECT UNION或MINUS等集合操作符的视图带有GROUPBY CONNECTBY或STARTWITH子句的视图带有AVG SUM或MAX等组函数的视图带有DISTINCT的功能的视图 如果不清楚指定的视图是否可以更新 或可以更新哪些列 可以查询USER UPDATABLE COLUMNS数据字典视图 例如查询上例中建立的视图TMP VIEW是否可以更新可以执行如下操作 selectcolumn name updatablefromuser updatable columnswheretable name TMP VIEW 重新编译视图 任何时间改变一个视图的查询所引用的对象时 Oracle将标记该视图无效 再次使用视图前它必须被重新编译 从新编译一个视图语法如下 ALTERVIEWviewnameCOMPILE alterviewbookauthor viewcompile alterviewbookonwebcompile 重建视图 修改视图所能处理的操作只是重新编辑视图 若想改变视图的定义 必须重建视图 若想修改视图bookonweb的定义为所有上网的且出版日期在1995年1月1日之后的书目信息 需要重建视图bookonweb createorreplaceviewbookonwebasselect frombookinfowherestatus y andpublishtime 1 1月 95 withreadonly 删除视图 可以使用DROPVIEW命令从数据库中删除视图 其格式为 dropview schema viewname dropviewbookauthor view也可以使用OEM通过右击视图从弹出菜单中选择 删除 来删除一个视图 查看视图信息 视图创建后 可以通过数据字典中的一些有关视图信息的视图来查看视图 查询用户bookcreator所拥有的全部视图的名称 类型selectview namefromall viewswhereowner BOOKCREATOR 序列管理 Oracle序列是一个连续的数字生成器 比如希望表的主键是一个数字 并且希望每次向表中插入新行时那个数字能自动增加 或正在建立审计跟踪时 需要知道生成这些审计跟踪记录的确切的顺序 而靠一个时间戳通常不足以实现这些功能 序列只存在于数据字典中 序列号可以被设置为上升 下降 可以没有限制或重复使用直到一个限制值 创建序列 创建序列使用CREATESEQUENCE语句 其语法格式如下 CREATESEQUENCE schema sequencenameKEYWORD下面的例子将创建一个序列 初始值是1 并且每次访问自动增加1 SQL CREATESEQUENCEsqc plan key2STARTWITH13INCREMENTBY14ORDER5NOCYCLE STARTWITH1用于指定初始值是1 INCREMENTBY1用于指定每次访问自动增加1 如果INCREMENTBY后面跟一个负值 则序列将是降序的 ORDER用于保证每个序列的值都将比先前的值大 使用NOCYCLE设置序列值在达到限制值以后不能重复 这是缺省设置 当试图产生MAXVALUE 1的值时 将会产生一个异常 其它关键字还有 MINVALUE 定义序列可以生成的最小值 这是降序序列中的限制值 缺省情况下该值为NOMINVALUE 对于升序为1 对于降序为 10E26 MAXVALUE 序列能生成的最大数字 这是升序序列中的限制值 缺省的MAXVALUE为NOMAXVALUE 对于升序为10E26 对于降序为 1 CYCLE 设置序列值在达到限制值以后可以重复 CACHE 定义序列值占据的内存块的大小 缺省值为20 NOCACHE 在每次序列号产生时强制数据字典更新 保证在序列值之间没有间隔当创建序列时 STARTWITH值必须等于或大于MINVALUE 改变序列 改变序列的办法也有两种 一种是通过命令行ALTERSEQUENCE命令改变序列的选项 另一种是通过在OEM中右击序列名 从弹出菜单中选择 编辑 来进行改变 对于序列的当前值是无法更改的 如果应用中确有这种要求 那么只能删除此序列后 再使用STARTWITH子句重建此序列 修改一个序列 使用ALTERSEQUENCE实现 如修改序列stuenno的cache属性为30 且不可循环使用ALTERSEQUENCEstuennonocyclecache30 删除序列 使用DROPSEQUENCE命令来删除一个序列 其语法格式为 DROPSEQUENCEsequence name也可以通过OEM来删除序列 简单地右击想删除的序列 并从弹出的菜单中选择 移去 当一个序列被删除后 其相关的权限也理所当然的被删除了 但建立在其上的同义词或触发器将会被保留 只不过处于无效状态 不能被使用 序列的应用 在Oracle中建立自动增加字段 需要执行下面的操作 定义需要作为数字增加的列希望表DEPT的主键字段DEPTNO 每次插入行时自动增加1 表DEPT的结构如下所示 名称是否为空 类型 DEPTNONOTNULLNUMBER 6 DNAMEVARCHAR2 30 LOCVARCHAR2 60 注意 DEPTNO是主键 其值类型是NUMBER型 且长度是6位 所以将要建立的序列的最大值不能超过999999 CREATESEQUENCEsqc dept keyNOCYCLEMAXVALUE999999STARTWITH1 可通过nextval和currval伪列来引用序列 每引用一次序列的伪列nextval 就会按照序列的定义产生一个新的序列码 通过序列的伪列currval可以反复利用当前的序列码 可以在如下场合使用序列的nextval和currval伪列 Insert语句的values子句中Select语句中的前面选择的表列名中Update语句中的set子句 在如下场合不能够使用nextval或currval伪列 子查询视图或快照的定义查询带有distinct操作符的select语句带有groupby或orderby子句的select语句通过union intersect或minus与另一个select语句相结合的select语句中Select语句的where子句中Createtable或altertable语句中的一个表列的default值定义中检查约束的条件中 想了解一个已存在的序列信息 可以通过数据字典DBA SEQUENCES ALL SEQUENCES和USER SEQUENCES等几个视图来查看 无论什么时候当插入一行时 从序列中定一个触发器来设置列的值在DEPT表中建立插入触发器 使得每次插入一行时 DEPTNO字段自动通过访问序列sqc dept key来获取值 CREATEORREPLACETRIGGERtri dept setdeptnoBEFOREINSERTONdeptFOREACHROWDECLAREp nextnodept deptno type BEGINSELECTsqc dept key NEXTVALINTOp nextnoFROMdual new deptno p nextno END 在建立上面的触发器后 DEPT表中的字段DEPTNO总是使用所建立的序列来设置 即使插入语句为DEPTNO指定了一些其它的值 也是这样 定义一个触发器来防止更新此列 有了前面3步已经实现了对DEPT表主键自动增加的功能 但如 有人更新了一条已经存在的记录 并将主键值改变为一个可能和将来插入相冲突的值该怎么办 所以这个值不能被更新 下面定义一个触发器来防止此列被更新 CREATEORREPLACETRIGGERtri dept updeptnoBEFOREUPDATEOFdeptnoONdeptFOREACHROWBEGINRAISE APPLICATION ERROR 20000 不能更新自动增加类型的主键值 END 测试结果 首先向表DEPT中输入一行数据 查询其结果 然后再将此行数据字段DEPTNO更新 具体操作如下所示 SQL insertintodeptvalues 203 203 203 SQL select fromdept SQL updatedeptsetdeptno 3 索引管理 Oracle支持两种基本的索引类型 B Tree索引和位映射索引 B Tree索引也是最常用的索引 它使用一个倒置的有序的树形结构来加速访问表中行的速度 位映射索引常被用于那些包含较少唯一值的列 位映射索引总是对表中的每一行包含一条记录 记录的大小取决于索引列中唯一值的数量 因为位映射索引是由位的长字符串组成 如果列中的唯一值越多 需要位的长字符串就更长 在表EMP中存在下面一些数据 SQL selectempno ename deptnofromemp EMPNOENAMEDEPTNO 3926ChendeJun2027101Haoxin7033927Zhanzou3022911GehenCao7031923BoWei4105836Jiangqin5034926Luoqun7006627Litao8028312Guohua4109536ShunJian5038926Liuquan700 对ENAME字段建立B Tree索引 则其倒置的树型结构如下所示 B Tree索引有以下几个特点 它们保持数据有序 使得查询某一范围的记录更容易 对于用复合列索引 可用前沿列来快速查询 即使那个查询并未引用索引中所有列 它们自动保持平衡 由于所有的叶子结点在同一层 所以检索一条记录所需要的时间对索引中的全部数据是一致的 性能保持相对连续 即使当被索引的表的大小增加时也如此 雇员表EMPNO是否退休状态 3926YesC7101YesE3927NoU2911NoO1923YesE 如果要在是否退休列上建立位映射索引 则其概念视图如下所示 EMPNOYesNo 392610710110392701291101192310 如果在状态列上建立位映射索引 则其概念视图如下所示 EMPNOCEUO 3926100071010100392700102911000119230100 从上面看出 如果所在列在只有2个唯一值 Yes No 时 位映射索引只需要求2位的字符串就能表示 而当其有4个唯一值时 就至少需要有4位的字符串才能表示 所以当列的唯一值很多时 会导致位映射索引很大 所以对于那些有多个唯一值的列 不适合使用它们 一个列中唯一值数量越多 位映射索引效率就越低 另外位映射索引仅在Oracle企业版中可用 创建索引 使用CREATEINDEX命令创建索引的语法如下所示 CREATEUNIQUEINDEXindx nameONtablename colnames TABLESPACEtablespacename STORAGE storage setting NOLOGGING NOSORT REVERSE UNIQUE 指定索引的每一条记录是唯一的 TABLESPACE 指定索引的表空间 STORAGE 指定存储参数 如果不指定将采用表空间默认的存储参数 NOLOGGING 指定建立索引而不向数据REDO日志文件记录索引的建立工作 使用此参数可以提高创建速度 缺点是由于没写入日志文件 所以如果通过日志文件恢复数据库以后 将无法恢复此索引 必须重建 NOSORT 指定创建索引时不用对表中索引数据进行排序 如果表中的数据已经被排序 可以使用此选项 如果在建立索引期间 发现表中的数据实际并未排序则将产生异常 REVERSE 指定建立反向索引 所谓反向索引是在每个索引记录中反转字节 比如chendj的反向索引值是jdnehc 当有大量索引记录以相同的字母开始时 并且反向这些字母将删除那个集合时 可以考虑采用此选项 但如果本身正在查询一个范围内的数据时 就不适合使用REVERSE选项 为表DEPT的DNAME字段建立一个索引的例子 CREATEUNIQUEINDEXINDX DEPT DNAMEONDEPT DNAME TABLESPACEINDXSTORAGE INITIAL10KNEXT20KMAXEXTENTSUNLIMITED NOLOGGINGREVERSE 建立B 树索引Create unique index 模式 索引名称on 模式 表名称 列名 asc desc 列名 asc desc Createindexbooknm indexonbookinfo bookname Createindexauthorinfo indexonauthorinfo birthdate hometown briefhistory Createindexbook press indexonbookinfo pressnameasc 建立位映射索引Createbitmapindex 模式 索引名称on 模式 表名称 列名 asc desc 列名 asc desc 设有一个汽车数据表carinfo 记录非常多 有1000多万条记录 其中包含了大量的低基数列 如color 汽车颜色 make 汽车品牌 model 汽车型号 和year 出厂日期 等 这些列各自包含的值数目不超过100 createbitmapindexcar bmp indexoncarinfo color make model year 查询索引信息 列出当前用户所拥有表中的所有索引的信息的命令 selectindex name index type uniquenessfromuser indexes 查询某个索引对应的表名 表列 升降序等信息selectindex name table name column name column position descendfromuser ind columnswhereindex name BOOKNM INDEX 修改索引 在建立索引后可以改变它 但是 在大多数情况下 仅限于改变其物理特点 不能向索引中增加列 Oracle允许进行下列变化 将一个索引移动到另一个表空间使用不同的存储参数重建索引对索引更名重新分配不使用的空间或分配一个新的空间标志此索引不可被使用其实重新将一个索引定位到另一个表空间 实际上需要索引完全重建 只不过 Oracle使这个过程自动化了 开始或终止对一个索引的使用情况进行监控 可以使用ALTERINDEX命令来改变索引 ALTERINDEXindx dept dnameRENAMETOindx dept dname new 另外使用REBUILD子句可以进行许多物理变化 重建索引时 Oracle通过使用一套新的存储参数从临时本中重建索引 通过重建indx dept dname索引来改变其参数 ALTERINDEXindx dept dnameREBUILDTABLESPACEusersNOLOGGINGSTORAGE INITIAL5KNEXT5K ALTERINDEXBOOK PRESS INDEXREBUILDTABLESPACEUSERSONLINESTORAGE INITIAL5KNEXT5K Tablespaceusers表明在users表空间里重建索引 Online指定在重建过程中希望用户访问表 把索引BOOKNM INDEX的名称改为BOOKNAME INDEXALTERINDEXBOOKNM INDEXRENAMETOBOOKNAME INDEX SELECTindex name table name column name descendFromuser ind columnswhereindex name BOOKNAME INDEX 也可以在OEM中 通过可视化的GUI界面来修改一个索引 其创建的步骤如下所示 启动OEM并注册到数据库 打开索引文件夹 选取准备更改的索引 右击此索引并从弹出菜单选择编辑 将弹出编辑窗口 其界面和新建索引类似 修改相关信息后点 确认 OEM将执行索引的修改操作 删除索引 可以使用DROPINDEX命令从数据库中删除索引 DROPINDEXINDX DEPT DNAME DROPINDEXAUTHORINFO INDEX 不能够直接删除与主键或唯一键约束相关的索引 必须先删除相应的主键或唯一键约束 在OEM中 可以通过在索引名字上右击并从弹出菜单中选择 移去 来删除一个索引 聚簇 聚簇是 clusters 一种可选的数据库对象 它将经常在相同数据块中一起使用的表进行物理分组 使经常被同时访问的表在物理位置上可以存储在一起 聚簇分为表簇 tablecluster 和哈希簇 hashcluster 一个表簇由若干个共享着相同数据块的表构成 这些表具有一个或多个相同的表列并且常常一起使用 同一个表簇中的各个表中相关的列称为簇键 clusterkey 簇键用一个簇索引 clusterindex 来进行索引 对于簇中的多个表 簇键值只存储一次 创建表簇之前的准备 主要用于执行查询操作 而不是插入或更新操作 表经常一起被查询或连接 创建表簇 CREATECLUSTER 模式 表簇名称 列名1数据类型 表列名2数据类型 物理属性组句 SIZE数目 K M TABLESPACE表空间名称 存储属性组句 size用于预计簇键和其相关的表列所需要的平均存储空间字节数 估计能够装入一个成簇的数据块内的簇键个数 限制了成簇数据块内簇键的个数 最大化优化了簇内键值的存储效率 簇和簇索引可以创建在不同的表空间里 在表bookinfo和authorinfo里都有表列authorname 把这两个表生成簇 oracle会把authorname表列放在同一个数据块里 建立表簇book author 以authorname为簇键 Createclusterbook author authornamevarchar2 20 pctused80pctfree5size600tablespaceusersstorage initial200knext300kminextents2maxextents20pctincrease33 创建表簇内的表 clusteredtables 创建完表簇后 就可以在表簇内创建表了 Createtablebookinfo booknonumber 38 primarykey booknamevarchar2 40 notnull isbnvarchar2 20 bookkindvarchar2 10 authornamevarchar2 20 notnullreferencesauthorinfo Clusterbook author authorname Createtableauthorinfo authornamevarchar2 20 primarykey birthdatedate hometownvarchar2 40 briefhistoryvarchar2 200 contactvarchar2 40 Clusterbook author authorname 创建簇索引 clusterindexes 在向簇内表插入数据之前 可以为簇建一个索引 Createindex索引名称oncluster表簇名称 tablespace表空间名称 存储属性组句 为表簇book author创建簇索引book author index Createindexbook author indexonclusterbook authortablespaceusersstorage initial50knext50kminextents2maxextents10pctincrease33 pctfree5 查看聚簇信息 列出当前用户所拥有的所有聚簇信息的命令selectcluster name tablespace name key size cluster typeFromuser clusters 修改聚簇信息 可修改已创建的表簇的如下属性 物理属性 PCTFREE PCTUSED INITRANS MAXTRANS和存储属性 存储簇键信息所需的平均空间 即SIZE 默认的并行度修改表簇book author的size为900Alterclusterbook authorSize900 Selectcluster name key size cluster typeFromuser clustersWherecluster name BOOK AUTHOR 删除聚簇 如果一个簇里的各个表不再需要了 就可以删除这个簇 当删掉一个簇时 簇里的表和相应的簇索引同时也被删掉 dropclusterstuenroll cluster 如果删除的簇中含有一个或多个表 想同时删除 dropcluster聚簇名称includingtables 如果簇中的一个或多个表包含外键 参照了这个簇以外的表 想删除这个簇和内部的表 dropcluster聚簇名称includingtablecascadeconstraints 哈希簇 在哈希簇中 表是基于哈希值组织的 在表的主键值上使用哈希函数可以得到这个哈希值 适用于哈希簇 大多数查询均为等值查询大小稳定不适用于哈希簇对表的查询返回结果不是单个值 而是一组键值大小不稳定应用程序 创建哈希簇 Createcluster 模式 哈希簇名称 列名1数据类型 表列名2数据类型 物理属性组句 size数目 k m tablespace表空间名称 存储属性组句 hashis hashkeys数目 Hashkeys数目用来指定哈希簇使用的哈希函数可以产生各不相同的哈希值的数量上限 Hashis用来指定一个用户的哈希函数 否则oracle使用内部定义哈希函数 不能够在哈希簇里创建簇键 也没有必要在哈希簇里创建索引 创建一个表stuenroll登记学生注册信息 表大小比较稳定 学生序号stuno 经常被等值查询 Createclusterstuenroll cluster stunonumber 5 0 tablespaceusersstorage initial250knext50kminextents1maxextents3hashisstunohashkeys150 Hashisstuno表示直接采用stuno作为哈希值 无需任何哈希函数 如果簇键的取值各不相同 分布平均 可直接采用簇键值作为哈希值 建完哈希簇后 就可以在簇里建表了Createtablestuenroll stunonumber 5 0 primarykey Clusterstuenroll cluster stuno 修改哈希簇与表簇基本相同 但对于哈希簇 size hashis和hashkeys属性均不能在altercluster命令中修改 如果想修改必须重新创建哈希簇 然后把数据信息从原来的哈希簇复制到新建的哈希簇里 同义词管理 同义词是一个对象 表 视图 序列 过程 函数 包 快照或其它同义词 的一个代替的名字 同义词能使多个用户使用同一个对象而不用将模式作为前缀在对象前面 通过使用CREATESYNONYM和DROPSYNONYM命令来管理同义词 对另一个数据对象而言同义词是一个别名 Oracle支持两种类型的同义词 公共的和专有的 其中公共同义词是针对所有用户的 相对而言专有同义词则只针对对象拥有者或被授予权限的用户 同义词的另一个用途是能使两个应用程序用两个不同名字指向同一个表 创建同义词 采用命令方式建立同义词只要具有CREATESYNONYM权限 就可以建立同义词 如果建立的是公共同义词 则需要有CREATEPUBLICSYNONYM权限 建立了同义词 并不意味着就能访问此同义词代表的对象 必须要由目标对象的所有者授予你相应的访问权限 此同义词才能被正确访问 使用命令创建同义词的语法如下 CREATE PUBLIC SYNONYM schema synonym nameFOR schema object