Oracle数据库应用论文

Oracle数据库应用 模式对象论文110015120周磊模式对象介绍模式（schema）是数据的逻辑结构或者说模式对象的汇总。一个模式对应一个数据库用户，并且名字和数据库用户名相同。每个用户都有一个单独的模式。模式对象可以通过SQL创建（DDL）和操作（DML）。模式对象是数据的逻辑存储结构。数据对象和磁盘上保存其信息的物理文件并不一一对应。Oracle在数据库的一个表空间上保存模式对象。每个对象的数据物理的保存在表空间的一个或者多个数据文件上。对某些对象如表、索引、聚集来说，你可以指定在表空间的数据文件上Oracle可以分配多大的磁盘空间来存储这个对象。模式和表空间没有什么联系：一个表空间可以包换来自不同模式的对象，模式对象可以包含在不同的表空间上。表概述表是Oracle数据库内数据存储的基本单位。数据是按照行和列来存储。你用一个表名（比如employees）、一系列列来定义表。你为每列指定一个列名（比如employee_id,last_name和job_id等），一个数据类型（比如VARCHAR2,DATE，NUMBER）和一个宽度。宽度可以是数据类型（比如日期）预先定义好的。如果列是NUMBER类型，定义范围和精度就可以了。行是关联到一个记录的列信息的集合。你可以指定表中每个列的规则。这些规则叫做完整性约束条件。NOTNULL就是一个完整性约束的例子。这个约束条件要求这列的每一行都要包含一个值。你还可以指定表列在保存在数据文件之前将数据加密。加密阻止了越过数据库访问控制来直接使用操作系统工具查看数据文件的现象。在你创建表之后，使用SQL语句插入行数据。表的数据就可以使用SQL查询、删除和更新。表的建立：SQL>CREATETABLEjxzy.switch（>OFFICE_NUMNUMBER（3,0）NOTNULL,>SWITCH_CODENUMBER（8,0）NOTNULL,>SWITCH_NAMEVARCHAR2（20）NOTNULL）;表的修改：SQL>ALTERTABLEjxzy.switch>ADD（DESCVARCHAR2（30））;表的删除：SQL>DROPTABLEjxzy.switch>CASCADECONSTRAINTS表的压缩：Oracle的表压缩特性通过减少数据块中重复值来压缩数据。数据块（磁盘页）中保存的压缩数据是自包含的。就是说，一个数据块中需要重新创建解压缩的所有数据信息对本数据块是可用的（一个块上的信息需要解压缩才能读取，但同样的信息在另一块上不需要）。一个块上的所有行和列的重复值都在块开始的地方保存一次，这个地方也叫块的符号表。这些（重复）值出现的地方都替代为指向符号表的链接。除了开始的符号表之外，压缩数据库块看起来和通常数据库块非常类似。适用于通常数据库块的所有数据库特性和函数也适合压缩数据库块。包括表和实体化视图的数据库对象都可以压缩。对于分区表来说，你可以选择压缩部分或者全部分区。压缩属性可以用在表空间、表或者分区表。如果在表空间级别声明压缩属性，那么其上创建的所有表默认都是压缩的。你可以修改一个表（或者表空间、分区表)的压缩属性，但只对新加入表的数据有效。因此，一个表或者分区可能同时包含压缩块和正常块。这样确保数据大小不能因为压缩而增加；在压缩会增加块使用时，块不会使用压缩。完整性约束管理数据库数据的完整性指数据的正确性和相容性。数据完整型检查防止数据库中存在不符合语义的数据。完整性约束是对表的列定义一组规则说明方法。ORACLE提供如下的完整性约束.NOTNULL非空PRIMATYKEY主键一个表只能有一个，非空UNIQUE唯一关键字CHECK表的每一行对指定条件必须是true或未知(对于空值)FOREIGAKEY外键唯一性约束(Uniqueconstraint)唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTERTABLE语句修改。语法：某列定义非空约束：SQL>ALTERTABLEoffice_organization>MODIFY(descVARCHAR2(20)>CONSTRAINTnn_descNOTNULL)某列定义唯一关键字：SQL>ALTERTABLEoffice_organization>MODIFY(office_nameVATCHAR2(20)>CONSTRAINTuq_officenameUNIQUE)定义主键约束，主键要求非空：SQL>CREATETABLEswitch(switch_codeNUMBER(8)>CONSTRAINTpk_switchcodePRIMARYKEY,)使主键约束无效：SQL>ALTERTABLEswitchDISABLEPRIMARYKEY定义外键：SQL>CREATETABLEPOLE(pole_codeNUMBER(8),>office_numnumber(3)>CONSTRAINTfk_officenum>REFERENCESoffice_organization(office_num)>ONDELETECASCADE);定义检查：SQL>CREATETABLEoffice_organization(>office_numNUMBER(3),>CONSTRAINTcheck_officenum>CHECK(office_numBETWEEN10AND99);注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首先禁用或删除外部键(foreignkey)。删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤：1、在唯一性约束保护的数据列上创建非唯一性索引。2、添加唯一性约束主键（PrimaryKey）约束表有唯一的主键约束。表的主键可以保护一个或多个列，主键约束可与NOTNULL约束共同作用于每一数据列。NOTNULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样，主键由B-tree索引增强。创建主键约束使用CREATETABLE语句与表一起创建，如果表已经创建了，可以使用ALTERTABLE语句。索引概述索引是与表相关的一种结构，它是为了提咼数据的检索速度而建立的。因此，为了提咼表上的索引速度，可在表上建立一个或多个索引，一个索引可建立在一个或几个列上。索引的建立：SQL>CREATEINDEXjxzy.idx_switch>ONswitch（switch_name）>TABLESPACEjxzy;索引的修改：SQL>ALTERINDEXjxzy.idx_switch>ONswitch（office_num,switch_name）>TABLESPACEjxzy;索引的删除：SQL〉DROPINDEXjxzy.idx_switch;Oracle还支持函数索引和指定应用程序或使用范围的域索引。增加或者减少索引并不需要修改任何的SQL语句的语法。索引只是数据的快速访问途径。它只影响执行速度。假定一个数据被索引，索引就会直接指向包含这个值的行的位置。索引在逻辑上和物理上都和关联表的数据独立。你可以任何时候创建和删除索引而不会影响基表以及其他的索引。如果你删除索引，所有的应用程序可以继续运行。但是，以前访问删除的索引的数据会降低速度。索引是一个独立的结构，还是需要存储空间的。索引创建后Oracle自动维护和使用索引。不需要用户额外操作,Oracle会自动将数据改变（比如增加行、修改行或删除行）反映到相关索引上。索引数据的获取性能基本保持一致，甚至增加了新行也是如此。但是一个表上存在多个索引还是会降低更新、删除和插入的性能，因为Oracle必须更新表的相关索引。优化器可以使用现存的索引来构建另一个索引。这样会创建一个更快的索引。唯一和非唯一索引索引可以是唯一或者非唯一的。唯一索引保证表在索引列（一个或多个）没有重复的行值。非唯一索引并不强迫限制列值（唯一）。Oracle推荐显示的使用CREATEUNIQUEINDEX语句来创建唯一索引。通过主键或唯一约束来创建唯一索引并不一定会创建新索引，而且创建的索引也不确保是唯一索引。复合索引复合索引（也叫关联索引）是一个在一个表的多个列上创建的索引。复合索引的列可以是任何顺序的，并且不需要在表中相邻。在SELECT语句的WHERE子句引用了复合索引的全部或者起始列的情况下，复合索引可以加快获取数据的速度。因而，定义中使用的列的顺序非常重要。通常，最常访问或最高选择率的行放在前面。索引的内部结构Oracle使用B树索引来加快数据访问。没有索引的情况下，你不得不依次扫描数据来找到值。对于n行来说，平均的查询行数为n/2。这个不会随着数据量的增加而缩放。考虑将一个值的排序列表分成多个块宽度的范围（页块）上。块的末尾除了块的指针之外都可以保存在一个搜索树中，n条数据花费log（n）的时间就可以找到。这就是Oracle索引隐含的基本原理。图5-7B树索引的内部结构B树索引的较高的块（树枝块）包含指向低级别索引块的索引数据。最低级别的索引块（叶块）包含每个索引数据和用来定位实际行的对应的rowid。页块是双向关联的。索引列的包含的字符集数据是基于数据库的字符集的二进制字符。对于唯一索引，每个数据值对应一个rowid。对于非唯一索引，排序序列中包含rowid，所以非唯一索引通过索引键和rowid排序。包含null的所有键值不索引，除非是聚集索引。两行都包含null，不违反唯一约束（因为Null是互不相同的）。索引属性有两种类型的块：搜索使用的树枝块页块保存的值树枝块树枝块保存下列内容：两个键之间需要用来分叉的最小键前缀指向包含键值的子块如果块拥有n个键,，那么它们包含n+1个指针。键和指针的数量由块大小限制。页块所有的页块到根块的高度都相同。页块保留下列信息：每行的全部键值表行的ROWID所有的键和ROWID对都是左右相连的。他们按照（key,ROWID）排序。B树结构的优势B树结构有如下优势：树的所有页块高度相同，所以获取索引上任何地方的任何数据所需的时间大致相同B树索引自动保持均衡状态B树索引的所有块平均来说是3/4满的B树对大多数查询提供了优秀的检索性能，包括精确识别和范围搜索插入、更新和删除都是高效的，并维护键顺序以便快速的获取数据B树索引对于小表和大表同样优秀，并且不会随着表尺寸的增长而降低性能索引唯一扫描索引唯一扫描是最高效的访问数据方式之一。这个访问方式用来从B树索引返回数据。在B树唯一索引的所有行指定相等条件时，优化器会选择索引唯一扫描。索引范围扫描索引范围扫描是访问选择性数据的通常操作。它可以是有限制的（两边都有界限）或者无限制的（在一边或者两边）。数据按照索引列的升序返回数据。值相同的多个行按照ROWID升序保存。键压缩键压缩使你可以压缩索引或索引组织表的主键列值的一部分，这样可以减少重复数据造成的存储瓶颈。通常，索引中键分为两类，一类是分组键，一类是唯一键。如果键没有定义为唯一键，Oracle将rowid加到分组键中。键压缩是一种减少分组键，并保存它的方法，这样可以由多个唯一键共享。位图索引索引的目的是为表中包含一个给定键值的行提供一个访问指针。在通常的索引中，通过针对每个键保存包含这个键值的行的rowid达到这种效果。Oracle针对每个rowid重复的保存键值。对于位图索引来说，每个键值使用一个位图，在通常索引中，每个键值对应一系列的rowid。位图的每个位对应一个潜在的rowid。如果设置了位，就意味着对应rowid的行包含这个键值。位图索引虽然采用了不同的内部表现，但是通过一个映射函数将位转换为实际的rowid，这样位图索引提供了通常索引同样的功能。如果不同的键值数量很少，那么位图索引是非常节省空间的。位图索引针对WHERE子句的多个条件的索引进行高效的合并。符合条件的某些而不是全部的行在访问表之前就被过滤掉了。这常常戏剧化的提高了反应时间。■rp基数使用位图索引对于低基数列非常有优势：低基数行就是哪些不同值的数量和表行数比非常小。如果列的不同值的数量少于表行数的I%，或者列值重复100次以上，那么这个列就是位图索引的候选者。即使一列重复次数不多，只要高基数列经常在WHERE子句中包含复杂的条件查询也可以。例如，一个拥有100万的行，1万个不同值的列适合做位图索引。这列的位图索引比B树索引要好，尤其是这列经常和其他列关联查询时。B树索引对于高基数列非常高效：就是说拥有很多可能的值的列，例如CUSTOMER_NAME或PHONE_NUMBER。在某些情况下，B树索引可能远远大于索引数据。适当使用的话，位图索引比比对应的B树索引小很多。在特定查询和类似的条件下，位图索引可以戏剧化的提高查询性能。一个查询中的WHERE子句中的AND和0R条件可以在转换位图结果到rowid之前在位图上直接执行对应的布尔操作来快速获得。如果目标行数较小，查询可以很快返回，而不需要对于全表扫描结果重新排序。位图索引和空值和大多数其他类型的索引不一样，位图索引可以包含NULL值的行。对NULL索引对有些类型的SQL语句非常有用，例如聚集函数COUNT的查询。分区表的位图索引和其他索引一样，你可以在分区表上创建位图索引。唯一的限制是位图索引必须是分区表的本地索引，不能是全局索引。只有非分区表才支持全局位图索引。位图关联索引除了单个表的位图索引之外，你可以创建位图关联索引，这是一个关联两个或者更多表的位图索引。一个位图关联索引可以显著的减少那些必须通过限制条件关联的数据的容量。对于表中每个列的每个值，位图索引保存了一个或多个表的行的rowid。在数据仓库环境中，关联条件是主键列或者维度表列或者事实表的外键列的内部相等关联。位图关联索引比实体化关联视图在存储上更有效率,是更好的实体化关联视图的替代方法。这是因为实体化关联视图不能压缩事实表的rowido视图概述视图是一个或多个表或者其他视图的数据的定制展示。一个视图将查询的输出作为一个表。因而，一个视图可以被认为是一个存储的查询或虚拟表。你可以在大部分可以使用表的地方使用视图。视图的建立：SQL>CREATEVIEWjxzy.pole_well_viewAS>(SELECTpole_path_numASpath,poleASdevice_numFROMpole>UNION>SELECTpipe_path_numASpath,>wellASdevice_numFROMwell);视图的替换：SQL>REPLACEVIEWjxzy.pole_well_viewAS>(SELECTpole_path_numASpath,poleASsupport_deviceFROMpole>UNION>SELECTpipe_path_numASpath,wellASsupport_deviceFROMwell);视图的删除：SQL〉DROPVIEWjxzy.pole_well_view;因为视图来源于表，所以它们之间有类似的地方。例如，你可以像表一样定义1000个列的视图。你可以查询视图，在某些限制下还可以更新、插入和删除视图中的数据。视图上执行的操作实际上影响的是视图基表的数据，并且服从基表的完整性约束条件和触发器。你不能直接在视图上显示定义触发器，但你可以在视图引用的基表上定义。Oracle还支持视图上的逻辑约束。视图如何存储和表不同，视图并不分配存储空间，也不包含实际数据。相反，视图是一个查询的定义，是从视图引用的表中抽取数据的查询。这些表叫做基表(basetable)。基表可以是表、也可以是视图(或者实体化视图)。因为视图基于其他对象，视图只需要在数据字典中存储视图的定义(存储的查询)，而不需要其他的存储。视图如何使用视图提供了一种针对基表的数据进行不同的展现方式。视图是非常强大的，因为它可以针对不同的用户调整展现。视图经常用在：提供额外的安全性，预先限制了只能访问表的某些行或某些列例如图5-5显示了STAFF视图没有显示基表employees的salary或commission_pct列隐藏数据复杂性例如，一个视图可以使用join定义，从多个表中对关联的列或行进行聚集。但是，视图隐藏了这个信息是来源于多个表的事实。简化用户的语句例如，视图允许用户从多个表中查询数据却并不用知道如何执行关联(join)将应用程序和基表的定义隔离例如，如果一个视图引用了一个四列表的三列，这时候这个表增加了一个新列，然后视图定义不受影响，使用视图的应用程序不受影响。表示一些必须使用视图的查询例如，一个视图可以定义为关联一个表和一个groupby视图，或者视图可以定义为一个表和union视图保存复杂查询例如，一个查询可能对表信息进行大量的计算。通过将查询保存为视图，你可以在查询视图时每次都执行计算。依赖和视图因为视图是一个引用其他对象（表、实体化视图或其他视图）的查询的定义，所以视图依赖它引用的对象。Oracle自动处理视图依赖。例如，如果你删除了视图的一个基表然后重新创建了它，Oracle会检查新的基表是否适合已有的视图定义。对象视图在Oracle对象关系数据库中，有一种对象视图让你可以获取、更新、插入和删除关系型数据，就好像它作为一个对象类型保存一样。你还可以定视图的类型为对象数据类型，比如object、REF和聚集（嵌套表和VARRAYS）。内联视图内联视图不是模式对象。它是一个使用别名（关联名）的子查询，你可以在SQL语句中像视图一样使用它。序列概述关于Oracle的序列（Sequence）使用序列是一数据库对象，利用它可生成唯一的整数。一般使用序列自动地生成主键值。对我们程序员来讲，精力时间有限，我们只学最有用的知识。大家请看：1） 建立序列命令CREATESEQUENCE[user.]sequence_name[incrementbyn][startwithn][maxvaluen|nomaxvalue][minvaluen|nominvalue];[NOCYCLE]-INCREMENTBY:指定序列号之间的间隔，该值可为正的或负的整数，但不可为0•序列为升序。忽略该子句时，缺省值为1.STARTWITH:指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。MAXVALUE:指定序列可生成的最大值。也可以不进行设置NOMAXVALUE:为升序指定最大值为1027，为降序指定最大值为-1.MINVALUE:指定序列的最小值。NOMINVALUE:为升序指定最小值为1.为降序指定最小值为T026.NOCYCLE:一直累加，不循环2） 更改序列命令ALTERSEQUENCE[user.]sequence_name[INCREMENTBYn][MAXVALUEn|NOMAXVALUE][MINVALUEn|NOMINVALUE]；修改序列可以:修改未来序列值的增量。设置或撤消最小值或最大值。改变缓冲序列的数目。指定序列号是否是有序。1，第一次NEXTVAL返回的是初始值2,可以alter除start至以外的所有Sequence参数。如果想要改变start值，必须dropsequence再re-create.删除序列命令DROPSEQUENCE[user.]sequence_name；用于从数据库中删除一序列。牛刀小试创建一个序列号的语句：--CreatesequencecreatesequenceNCME_QUESTION_SEQminvalue1maxvalue999999999999startwith1incrementby1nocache;//////////////////////////////SQL中取序列号的用法：SELECTNCME_QUESTION_SEQ.nextvalFROMdualSELECTNCME_QUESTION_SEQ.CURRVALFROMdualSELECTNCME_QUESTION_SEQ.nextvalFROMdualSELECTNCME_QUESTION_SEQ.CURRVALFROMdual注意：在使用序列的时候,有时需要有用户名,就像这样：insertintosystem.CONSERVATOR(CONSERVATORNAME,CONPASS,CONTRUENAME,CONSEX,CONID)values('JG','123456','000',0,system.CONID.nextval);ORACLE序列的使用1、CreateSequence你首先要有CREATESEQUENCE或者CREATEANYSEQUENCE权限，CREATESEQUENCEemp_sequenceINCREMENTBY1--每次加几个STARTWITH1 --从1开始计数NOMAXVALUE --不设置最大值NOCYCLE -- 一直累加，不循环CACHE10NOORDER; --并行时取得序列的顺序一旦定义了emp_sequence，你就可以用CURRVAL,NEXTVALCURRVAL二返回sequence的当前值NEXTVAL二增加sequence的值，然后返回sequence值比如：emp_sequence.CURRVALemp_sequence.NEXTVAL可以使用sequence的地方：-不包含子查询、snapshot、VIEW的SELECT语句INSERT语句的子查询中INSERT语句的VALUES中UPDATE的SET中可以看如下例子：INSERTINTOempVALUES(empseq.nextval,'LEWIS','CLERK',7902,SYSDATE,1200,NULL,20);SELECTempseq.currvalFROMDUAL;但是要注意的是：-第一次NEXTVAL返回的是初始值;随后的NEXTVAL会自动增加你定义的INCREMENTBY值，然后返回增加后的值。CURRVAL总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句