农信银行培训教材

农信银行培训教材 交流材料 目 录 1、培训对象: ......................................................................................................................... 4 2、Oracle的基本使用及配置。 ............................................................................................. 4 3、常用工具介绍 ..................................................................................................................... 4 3.1、Oracle客户端产品的使用 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 。 .......................................................................... 4 3.2、PL/SQL工具介绍 ................................................................................................... 4 4 数据操作和数据库对象 ....................................................................................................... 4 4.1 多表查询 .................................................................................................................... 4 4.2 数据修改 .................................................................................................................... 5 4.3 事务控制命令 ............................................................................................................ 6 4.4 表的创建与修改 ........................................................................................................ 7 4.5 视图 .......................................................................................................................... 8 4.6 其他数据库对象和数据字典 .................................................................................. 9 5、常用SQL函数介绍 ......................................................................................................... 11 6、SQL编写原则 .................................................................................................................. 14 6.1格式 规范 编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载 ................................................................................................................... 14 6.2变量绑定规范 ........................................................................................................... 15 6.3表连接 ....................................................................................................................... 16 6.4查询语句规范 ........................................................................................................... 16 6.5DML语句规范(加上综述，稍做说明) .............................................................. 16 7、数据库开发规范 ............................................................................................................... 17 7.1、查询语句的使用原则 ............................................................................................ 17 7.2、DML语句的调整原则 .......................................................................................... 29 7.3、PL/SQL的使用原则 ............................................................................................. 32 7.4、数据库编码规范 .................................................................................................... 48 7.5、PL/SQL书写规范 ................................................................................................. 49 7.6、规范命名中英对照表 ............................................................................................ 52 7.7、典型案例 ................................................................................................................ 57 8、索引................................................................................................................................... 59 8.1、索引种类介绍 ........................................................................................................ 59 8.2 各种索引的结构分析 .............................................................................................. 59 8.3 各种索引使用场合及建议 ...................................................................................... 63 8.4 索引跟踪引用(性能优化监测) ............................................................................... 64 8.6、常见Hints技术 ..................................................................................................... 72 8.7、使用原则 ................................................................................................................ 74 9、分区(参见Oracle数据库高级技术交流计划-大批量数据处理技术.ppt) .................... 74 9.1. 分区表的创建 ......................................................................................................... 75 9.2. 分区表操作 ............................................................................................................. 78 9.3. 分区表索引的使用: ............................................................................................. 81 9.4. 分区表的维护: ..................................................................................................... 83 10、物化视图 ......................................................................................................................... 84 10.1、物化视图分类 ...................................................................................................... 84 10.2、创建方式(Build Methods) .............................................................................. 85 10.3、查询重写(Query Rewrite) .............................................................................. 85 10.4、刷新(Refresh) ................................................................................................. 85 10.5、物化视图日志 ...................................................................................................... 85 10.6、物化视图分区 ...................................................................................................... 86 10.7、物化视图系统参数 .............................................................................................. 86 10.8、物化视图例子 ...................................................................................................... 86 11、数据导入导出工具 ......................................................................................................... 87 11.1 SQL*Loader ............................................................................................................ 87 11.2 数据导入与导出 .................................................................................................... 88 12、数据库优化及使用技巧 ................................................................................................. 89 12.1、oracle性能调整的十大要点 ............................................................................... 89 12.2、几个简单的步骤大幅提高Oracle性能 ............................................................. 89 13、数据仓库 ......................................................................................................................... 89 1、培训对象: 1、一部分是有数据库应用开发基础的，但主要是INFORMIX数据库方面的。其中，个别有一定的ORACLE方面的开发 2、另一部分是基本没有数据库开发经验的，主要是刚毕业的应届生。 2、Oracle的基本使用及配置。 1.1、Oracle客户端连接数据库的配置。 3、常用工具介绍 3.1、Oracle客户端产品的使用方法。 3.2、PL/SQL工具介绍 4 数据操作和数据库对象 本章学习目标: , 掌握多表查询的概念和方法 , 熟悉数据库数据的插入、修改、删除等操作命令，并能执行简单操作 , 掌握数据库对象的定义、用途以及实现方法 4.1 多表查询 单表查询比较简单，但是实际应用最多的还是多表查询，多表查询即是把多个表按照一定的关系连接起来，在用户看来好像是查询一个表一样。 4.1.1 简单的两表查询 在关系数据库管理系统中，数据存储在不同的表里，这些表都是相关的，用户可以使用SQL语句对这些相关信息进行查询。SELECT语句必须和FROM语句联用。 在多表查询当中必须指定各列所属的表，两个表的关系在WHERE子句中列出。 4.1.2 三表查询和多表查询 要执行3个或更多表格联合查询，必须遵循以下步骤: ?先把两个表格按一定的条件组合到一起。 ?把组合的结果同第3个表格组合到一起。 ?重复第2步，直到把所有表格组合到一起。 4.1.3 一些连接操作设置符号 通过使用四个集合操作符UNION、UNION ALL、INTERSECT和MINUS，Oracle提供了将两个或者多个SQL查询结合进一个单独的语句的能力。 使用集合操作符的查询称为复合查询(compound query)，Oracle为复合查询的编写制定了需要遵循的指南: , 在构成复合查询的各个单独的查询中，SELECT表中值的数量和数据类型相匹配 , 用户不许在复合查询所包含的任何单独的查询中规定ORDER BY子句 , 用户不许在BLOB、LONG等大数据对象上使用集合操作符 , 用户不许在集合操作符SELECT列表中使用嵌套表或者数组集合。 1.UNION UNION语句将第一个查询中的所有行与第二个查询的所有行相加，消除重复行并且返回结果。 2.UNION ALL UNION ALL语句与标准的UNION语句工作方式基本相同，唯一不同的是UNION ALL不会从列表中滤除重复行。 3.INTERSECT INTERSECT操作会获取两个查询，对值进行汇总，并且返回同时存在于两个结果集中的记录。只由第一个查询、或者第二个查询返回的那些行不会包含在结果集中。 4.MINUS MINUS集合操作会返回所有从第一个查询中返回，但是没有从第二个查询中返回的那些记录。 4.2 数据修改 在数据库应用中，经常要对存放的数据进行更新操作，以满足不断变化的需求。因此对数据进行插入、修改、删除就成为必不可少的一项工作。 4.2.1 数据的插入 在插入数据时，应首先确认基表已经创建，然后确定基表的结构，基表的各列顺序、类型以及是否是非空(NOT NULL)，可以通过DESC命令来查看，以保证插入数据的类型与基表列的类型匹配。若插入字符型和日期型数据，要用单引号括起来。具体语法如下: insert into 数据表(字段名1,字段名2,……) values(字段名1的值, 字段名2的值,……)。 由于字段的类型不同，在书写字段值的时候要注意格式。 数值型字段，可以直接写值。 字符型字段，其值上要加上单引号。 日期型字段，其值上要加上单引号，同时还要注意年、月、日的排列次序。 在数据的插入语句中，插入列排序和插入值要一一对应。字符型和日期型字段要加上单引号，非空列必须有值。 4.2.2 数据的修改 在Oracle中，对数据的修改是通过使用UPDATE命令来实现的。具体语法如下: update 数据表 set 字段名1=新的赋值,字段名2=新的赋值,…… where 条件 4.2.3 数据的删除 对表中数据进行删除是使用DELETE命令来实现的。 4.3 事务控制命令 1. 事务的概念 事务是指一个或多个SQL语句所组成的序列，是对数据库操作的逻辑单位。连续地执行提交操作(COMMIT)或回退操作(ROLLBACK)之间的操作称为一个事务。Oracle的RDBMS要确保数据库的一致性是基于事务而不是基于单条SQL语句的。 对事务的控制包含事务提交、事务回滚(或撤消)和设立检查点。 2. 事务的提交 数据库数据的更新提交以后，这些更新操作就不能再撤消。事务的提交有3种方式: , 显式提交 在SQLPLUS中，使用COMMIT命令来提交所有未提交的更新操作就是显式提交。 , 隐式提交 有些命令，如ALTER、AUDIT、COMMENT、CONNECT、CREATE、DISCONNECT、DROP、EXIT、GRANT、NOAUDIT、REVOKE、RENAME命令，以及退出SQL*PLUS都隐含COMMIT操作，而无须指明该操作。只要使用这些命令，系统就会提交以前的更新操作，就像使用了COMMIT命令一样。 , 自动提交 用户可以使用SET命令来设置自动提交环境。经过设置后，SQL*PLUS会自动提交用户的更新工作。也就是说，一旦设置了自动提交，用户每次执行INSERT、UPDATE或DELETE命令，系统就会立即自动进行提交。 3(事务回滚 尚未提交的INSERT、UPDATE或DELETE更新操作可以使用ROLLBACK命令进行撤消。 4.4 表的创建与修改 数据以表的形式存储在数据库中。 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 或用户是数据库表的所有者，表的每一列定义为一个数据类型，存储数据必须满足该数据所在列的属性。 4.4.1 生成一个简单表 表的生成可以使用CREAT TABLE命令来实现。 一个新表可以从已有的表格或视图中来创建，新表的列必须已经在原图或视图中存在，列属性和数据类型不能改变，但列名可以改。另外，用户也可以指定新表的存储参数。 ALTER TABLE用来修改表定义。 1(增加列 ALTER TABLE有多种形式，对表增加新列的语法如下: ALTER TABLE < table_name > ADD ([,< column define>]...); 指出要增加列的表，ADD后的指出新增加列的名字及其数据类型，其格式同于CREATE TABLE语句中的定义，只有表中无数据时，新增加的列才可选NOT NULL;当不作选择时，隐含为NULL。新增加的列在表尾部，该列初值均为NULL。另当只增加一列时，ADD后的“()”可省略。 2(修改列 修改列的语法为: ALTER TABLE MODIFY ([][NULL | NOT NULL] [,[][NULL | NOT NULL]]...); :指出要对列作修改的表 :指出要作修改的列名 [data_type]:可取CHAR(n), NUMBER, NUMBER(m, n), NUMBER(n), DATE等中的一种, 如不作选择, 表示该列数据类型不作修改 [NULL | NOT NULL]:一个列要改为NOT NULL，要求该列当前不含空值;如不选此项, 则缺省而保持原有值。 修改列必须遵循以下规则: , 可以增加列的宽度或数字的精度。 , 减少列宽度时，列值必须为空。 , 当数据类型被修改时，列值必须为空。 3( 删除列 删除列的语法为: ALTER TABLE DROP COLUMN 如果此列存在索引或约束，必须使用附加功能CASCADE CONSTRAINTS。 这个操作会将表重新写入磁盘，并且移走被删除列的数据。对于更大的表，用户可以使用另一个操作来避免对表进行整体重写: ALTER TABLE SET UNUSED COLUMN ( [,] [,]……) 此命令不会对表进行重写，也不会回收空间。在语句执行之后，列只是会被简单忽略。如果需要将这些列删除，回收存储空间，可使用以下语句: ALTER TABLE DROP UNUSED COLUMNS; 4.4.2 表的重命名与删除 1.删除表 当表或视图不需要时，可以用DROP语句删除，其格式为: DROP TABLE [CASCADE CONSTRAINTS]; 当表被删除时，系统自动地删除表中的数据和在此表上建立的各种索引, 也删除了在该表上授予的操作权限和触发器。删除表并未删除在该表上定义的视图和别名, 但这些视图和别名已被标志为非法并不能应用, 用户应使用其它方法删除它们。当此表格存在约束时，必须使用附加功能CASCADE CONSTRAINTS。 2( 重命名表 使用RENAME命令可以对表进行重命名。此命令对视图和私有同义词也有效。当对一个表格进行重命名后，Oracle将自动更新相应的约束、索引和与此表相关的权限，而且Oracle将标志那些以此表为参考的视图、同义词、存储过程和函数为非法。 用RENAME来重命名的语法为: RENAME < old_name > TO < new_name >; 4.5 视图 视图(View)是SQL语言提供的一种特殊类型的表，它可以由一个基表中选取的某些行和列组成，也可以由几个表中满足一定条件的数据组成。视图就像是基表的窗口，它反映了一个或多个基表的局部数据。但视图本身并不实际存放数据，数据来自于基表，视图仅是对应基表的部分数据，因此视图只是一个虚表。 生成一个视图可使用CREATE VIEW命令。其格式为: CREATE [OR REPLACE][FORCE] VIEW [()] AS [WITH CHECK OPTION]; :定义新的视图名 :查询语句, 它从有关表中为视图选取满足一定条件的数据 [()]:是可选项, 当不选时, 新视图的列名与SELECT所选数据的名称相同; 当选择时, 则为 SELECT 所选数据重新取了个新的列名, 它们按顺序对应。 WITH CHECK OPTION:也是可选项, 当选择时, 向视图插入数据时, 用户必需保证数据要满足所选的检查条件。 [OR REPLACE]:通过使用此选项，可以重新定义视图，而且不损失以前授予的任何访问特权(如果删除后重新创建视图，所有相关联的授权也将被删除)。 [FORCE]:用户也可以使用FORCE来生成带有错误的视图，通常状况下如果视图存在错误，它将无法生成。但是，如果需要生成一些带错误的视图，如所参考的表格不存在，可以使用这种方法。这种方法生成的视图为非法，以后用户可以修改此错误，即生成相应的表格，随后此视图被重新编译。 注意:如果用户使用*来选择所有的列生成视图，当修改表格或增加列时，必须重新生成此视图。 删除视图使用命令DROP VIEW。此视图的定义从数据字典中被删除，相应的权限也被删除，其他一些视图和存储程序需要参考此视图的，也被标志为非法。 4.6 其他数据库对象和数据字典 数据库对象除了表格和索引以外还有很多，如约束、触发器和同义词等。它们也是数据库所必需的对象。数据字典也是一系列的视图，它通过不同的视图呈现给不同的用户。用来对数据库的描述、管理及维护。 4.6.1 索引(Index) 1( 平衡树索引(B-tree index) 平衡树，又叫B树，是现代关系型数据库中最为普通的索引。B树索引用由底向上的顺序来对表中的列数据进行排序。B树索引不但存储了相应列的数据，还存储了ROWID，此ROWID用来标志表格中相应行的剩余数据。索引以树形结构的形式来存储这些值，在检索时，Oracle将先检索列数据。 2( 位图索引 位图索引(Bitmap Index)并不重复存取索引列的值，每一个值被看作一个键，相应的行的ID置为一个位(BIT)。位图索引适合于仅有几个固定值的列，如职员表中的性别列，性别只有男和女两个固定类型。不能用位图索引来生成具有惟一性的索引(UNIQUE KEY)和反键索引(REVERSE KEY)。 4.6.2 约束 Oracle利用完整性约束机制以防止无效的数据进入数据库的基表，如果任何DML执行结果破坏完整性约束，该语句被回滚并返回一个错误。利用完整性约束实施数据完整性规则有下列优点: , 定义或更改表时，不需要程序设计便很容易地编写程序并可消除程序错误，其 功能由Oracle控制。所以说明完整性约束优于应用代码和数据库触发器。 , 对表所定义的完整性约束存储在数据字典中，所以由任何应用进入的数据都必 须遵守与表相关的完整性约束。 , 具有最大的开发能力。当由完整性约束所实施的事务规则改变时，管理员只需 改变完整性约束的定义，所有应用自动地遵守所修改的约束。 , 由于完整性约束存储在数据字典中，数据库应用可利用这些信息，在SQL语句 执行之前或由Oracle检查之前就可立即反馈信息。 , 完整性约束说明的语义是清楚地定义，故对每一说明规则可实现性能优化。 , 由于完整性约束可临时地被屏蔽(DISABLE)，以致在装入大量数据时可避免约 束检索的开销。当数据库装入完成时，完整性约束可容易地使其重新启动，任何破 坏完整性约束的新数据在例外表中列出。 Oracle的DBA和应用程序开发者对列值输入可使用的完整性约束有下列类型。 , NOT NULL约束 , UNIQUE约束 , PRIMARY KEY约束 , FOREIGN KEY约束 , CHECK约束 4.6.3 同义词 同义词(Synonyms)是指向其他数据库表的数据库指针。当创建一个同义词时就指定了一个同义词名字和同义词所引用的对象。当引用同义词名字时，Oracle服务器会自动地用同义词定义的对象名字来代替同义词的名字。 同义词有两种类型:私有(PRIVATE)和公有(PUBLIC)。私有同义词是在指定的模式中创建的，并且只允许拥有它的模式访问。公共同义词由PUBLIC模式所拥有，所有的数据库模式都可以引用它们。 4.6.4 过程、函数和包 包、过程和函数与它们的源代码一起存在数据字典中。一个存储过程是一个操作的代码单元，可以被传递参数，并能够返回值。一个存储函数是一个代码单元，可以被传递参数，并能够返回一个值。一个包是一个过程、变量和函数的集合，包按照功能被逻辑地分组。 可以通过DBA_OBJECTS和DBA_SOURCE视图查看包、过程和函数的有关信息。 4.6.5 触发器 触发器是存储过程，当针对一个表发生特定的动作时，就会激活它。触发器可以被编码。当针对一个表进行插入、更新、删除或3种操作的结合时激活触发器，也可以在某行被影响或某条语句出现时被激活。触发器经常用于加强数据完整性约束和业务规则，这些业务规则对于内置的Oracle引用完整性约束来说实在是太复杂了。关于数据触发器的数据可以在DBA_TRIGGERS视图中找到。 4.6.6 数据字典 数据字典(Data Dictionary)通过不同的视图呈现给不同的用户，这些视图拥有带有不同前缀的相同名称。这些数据可以被划分为以下几类: , 对象的存储信息，例如表空间、段和区间 , 表、列、簇和索引 , 视图、同义词和序列 , 过程、包、触发器和它们的源代码 , 用户、角色、环境资源文件和权限 , 性能信息 , 锁和审计信息 对于一个授权的数据库系统管理员用户，最适合使用的视图是加了前缀DBA_的视图，如DBA_TABLES、DBA_INDEXES、DBA_SEGMENTS等。这些视图提供了所有模式中的每个数据库 对象的有关信息，通常由模式的拥有者管理。对于其他用户，可以使用ALL_视图和USE_视 图。另外，性能视图是公共可用的，是以V$开头的，如V$SGASTAT、V$SESSION、V$SYSSTAT、 V$STATNAME等。 5、常用SQL函数介绍 在对数据的操作过程中，经常需要改变数据输出形式或进行数据运算输出等，这时可以 考虑使用函数。SQL语言中的函数包括单行SQL字符函数、转换格式函数、多行函数以及 DECODE函数等。 5.1 单行SQL字符函数 , ABS函数 格式:ABS(n) 此函数返回n的绝对值。 , ACOS函数 格式:ACOS(n) 此函数返回n的反余弦值。 , ASIN函数 格式:ASIN (n) 此函数返回n的反正弦值。 , ATAN函数 格式:ATAN (n) 此函数返回n的反正值。 , CELL函数 格式:CELL (n) 此函数返回大于或等于n的最小整数值。 , COS函数 格式:COS (n) 此函数返回n的余弦值。 , COSH函数 格式:COSH (n) 此函数返回n的双曲线余弦函数。 , EXP函数 格式:EXP (n) 此函数返回e的n次幂。 , FLOOR函数 格式:FLOOR (n) 此函数返回小于或等于n的最大整数值。 , LN函数 格式:LN (n) 此函数返回n的自然对数，即以E为底的对数。 , LOG函数 格式:LOG (m,n) 此函数返回以m为底的n的对数。 , MOD函数 格式:MOD (m,n) 此函数返回以m除以n的余数。 , POWER函数 格式:POWER (m,n) 此函数返回m的n次幂。 , ROUND函数 格式:ROUND (m,n) 此函数返回舍入到小数点左边或右边m位的n的值。 , SIGH函数 格式:SIGH (n) 此函数返回n的符号值。 , SIN函数 格式:SIN (n) 此函数返回n的正弦值。 , SINH函数 格式:SINH (n) 此函数返回n的双曲线正弦值。 , SQRT函数 格式:SQRT (n) 此函数返回n的平方根。 , TAN函数 格式:TAN (n) 此函数返回n的正切值。 , TANH函数 格式:TANH (n) 此函数返回n的双曲正切值。 , TRUNC函数 格式:TRUNC (n) 此函数功能类似ROUND。 5.2 转换格式函数 格式函数转换规则如表2-3所示。 函数 功能 CHARTOROWID 将包含外部语法ROWID的CHAR或VARCHAR2数值转换为内部的二进制 语法 CONVERT 将字符串CHAR中的字符，从SOUCE_CHAR_SET标识的字符集转换为由 dest_char_set标识的字符集 HEXTORAW 将包含十六制的CHAR转换为一个RAW数值 RAWTOHEX 将RAW数值转换为一个包含十六进制的CHAR值 ROWIDTOCHAR 将一个ROWID数值转换为VARCHAR2数值类型 TO_CHAR(日期转换) 将日期数据类型转换为一个在日期语法中指定语法的VARCHAR2数据 类型的字符串 TO_CHAR(数据转换) 将NUMBER数据类型N转换为一个VARCHAR2数据类型 TO_DATE 该函数将CHAR或VARCHAR2数据类型的值转换为DATE数据类型 TO_MULTI_BYTE 该函数用来将CHAR中的所有单字节字符转换为等价的多字节字符 TO_NUMBER 该函数将CHAR或VARCHAR2数据类型的字符串(CHAR)按照指定的数 值语法(FMT)转换为NUMBER数据类型值 TO_SINGLE_BYTE 将CHAR中所有的多字节的字符转换为它们等价的单字节字符 TRANSLATE USING 将文本TEXT按照指定的转换方式转换为数据库字符集和民族字符集 , DECODE函数 语法:DECODE(,,[,,„][,]) 上式中a为一个表达式，如果m1和a相等，则返回n1;接着是m2和n2，这样一直继续下去，返回n2 n3„如果条件都不满足，则返回b。 5.3 多行函数 , AVG函数 用来返回某一列中所有数值的平均值。 , COUNT函数 返回列数并经常被用于获取某一视图中的总行数。COUNT是惟一一个能用于非数值列的标准SQL集合函数。 , MAX函数 返回某一列中的最大数值。 , MIN函数 返回某一列中的最小数值。 , STDDEV函数 返回某一列中的标准偏差。 , SUM函数 返回某一列中的所有数值的总和。 , VARIANCE函数 返回某一列中的所有数值的方差。 , GROUP BY函数 GROUP BY用来把数据分组。我们可以使用它把数据库返回的数据按要求分类。在GROUP BY函数当中，GROUP BY所指定的列必须在SELECT所指定的列中存在。 , HAVING函数 由于GROUP 函数与WHERE函数不能同时使用。 一个SQL语句可以同时存在WHERE函数和HAVING函数。WHERE函数在分组以前过滤数据，HAVING函数在分组后过滤数据。 , ORDER BY 函数 在一般使用SELECT语句时，结果集中的记录是按照它们在基础表中被找到的顺序返回的， 如果想把输出结果按一定的要求进行排序，可以使用ORDER BY函数。 此函数并不象GROUP BY那样所指定的列必须在SELECT语句当中出现。可以通过在ORDER BY函数的每个字段后面放置ASC(升序)和DESC(降序)来指定想以什么样的顺序排列数据。 6、SQL编写原则 6.1格式规范 a) 书写规范 , 不允许出现隐式的数据类型转换。 , 不应该将空的变量值直接与比较运算符(符号)比较。 , 对于非常复杂的SQL(如SQL语句嵌套超过3层，访问的表个数超过10个)，应该 使用程序实现。 , 定义变量类型时应该使用相关表字段的类型定义，形如%type、%rowtype。 , 存储过程中变量的声明应集中在as和begin 关键字之间，不允许在代码中随意定义。 , 应该使用varchar2 代替varchar 类型。 , 原则上不要使用动态sql，如必须使用动态sql，则需绑定变量。 , 代码中不建议使用goto 语句。 , 所有定义的变量和参数都必须在程序中使用到。 , 要求所有与数据相关的代码应统一使用小写字母书写。 , 应避免使用select * 语句，而应写上必要的字段名。 , insert 语句必须给出字段列表以提高可阅读性。 , 从表中同一记录中获取记录的字段值，应使用同一SQL语句得到，不允许分多条SQL语句。 , 当一个PL/SQL 或SQL 语句中涉及到多个表时， 必须使用别名来限定字段名。 , 确保变量和参数在类型和长度与表数据列类型和长度相匹配。 , 程序块采用缩进风格书写，缩进格数统一为两个空格。 , 一行只写一条语句，不允许把多个语句写在同一行中。 b) 注释规范 函数和过程代码要求按照下列格式书写注释: , 源程序有效注释量须在10%以上，注释语言须准确、易懂、简洁。 , 统一所有程序文件头的注释。 , 所有变量定义需要加注释，说明该变量的用途和函义。 , 注释内容要清晰、明了、含义准确，防止注释二义性。 , 禁止在注释中使用缩写，特别是非常用的缩写。 , 对存储过程的任何修改，都需要在注释最后添加修改人、修改日期及修改原因等信 息。 , 对程序分支必须书写注释。 , 在代码的功能、意图层次上进行注释，提供有用、额外的信息。 , 对代码注释应放在其上方。 , 注释与所描述的内容进行同样的缩排。 , 注释上面的代码应空行隔开。 , 函数应对返回的代码进行详细描述。 , 尽量使用”--”进行注注释。 , 行尾注释须使用”--”。 , 避免在一行代码或表达式的中间插入注释。 , 在程序块的结束行右方加注释，以表明程序块结束。 c) 脚本规范 所有脚本按内容分开存放，并按以下顺序存储:(合成一句话) 1) 创建数据类型脚本 2)创建业务表脚本 3)创建临时表脚本 4)创建视图脚本 5)创建主外键脚本 6)创建索引脚本 7)创建触发器脚本 8)创建存储过程脚本 9)初始化数据脚本 10)创建作业脚本 6.2变量绑定规范 , 在OLTP类型的应用数据库里应该把参数CURSOR_SHARING设置为 SIMILAR/FORCE。 , 在OLAP类型的应用数据库里应该把参数CURSOR_SHARING设置为EXACT。 , 对于执行频繁的程序或SQL语句必须使用绑定变量方式执行SQL语句。 6.3表连接 , 多表连接使用到嵌套循环连接时(NESTED LOOP JOIN)必须确保在查询中使用了小的表作为驱动表。应该把包含参与连接的数据量最少的表放在FROM子句的最右端。 , 在使用有索引的嵌套循环连接时，必须保证其驱动表的关联字段有索引。 , 不允许有表连接数目多于8个表或SQL语句嵌套层数多于5层，如果有必须通过中间表或临时表分步解决或程序去实现。 6.4查询语句规范 编写查询语句时应遵循以下原则: , 使用LIKE运算符时，应该在%的左端提供尽可能多的匹配信息以缩小查询的范围。 , 一般情况下，应避免在索引列上使用函数或其他运算符号。 , 禁止用字段与NULL值等值运算。 , 应尽量使用DECODE、ROLLUP和CUBE等函数替代自行编写代码实现程序功能。 , 应该使用UNION ALL来代替UNION。 , 应该在DISTINCT,GROUP BY,ORDER BY子句涉及到的列上创建索引。 , in子句中返回相对较少的记录时，应该使用关联子查询(表连接)。 , 对于大表的全表扫描的查询语句或返回大数据量的查询操作，应该使用并行查询技术。 , 应尽可能避免使用SELECT ……FOR UPDATE语句而造成系统不必要的加锁。 , 对于主查询中包含较多条件而子查询条件较少的表，应该使用EXISTS运算符;反之，则使用IN。 6.5DML语句规范 , 对于删除全表的操作应该使用truncate table命令来实现。 , 应该使用更改状态标志和插入新的记录来代替删除操作 , 在执行大容量的插入或更新任务时，应该暂时禁用所有受影响的数据表的约束、 触发器，装载完数据后才重新启用约束。 , 在大型的DML批操作中(操作记录>1百万级)，在更改数据表之前，删除全部索引， 完成后，重新建立起索引。 如果在数据被加载到数据库之前其数据已在外部完成排序，则应该在创建索引时使用NOSORT 选项。 7、数据库开发规范 7.1、查询语句的使用原则 7.1.1基本原则 a)、索引的正确使用 合理的使用正确的索引是提高系统执行效率的关键因素，对索引的使用需要注 意以下一些问题: , 过度索引 一般的，使用索引可以缩短查询语句的执行时间，提高系统的执行效率，但是 要避免以下两种过度索引的情况出现: , 对一个表建立了过多的索引，从而造成维护索引所需要的时间超过使用索引所降低 的时间，从而造成整个系统效率的下降，这一般发生在对一些进行大量更新的表上 面。 , 由于索引数据的区分度不够，造成了使用索引而引起的效率的下降，这一般发生在 对数据进行大的统计分析的时候。可以通过指定全表扫描等提示(hint)来避免。 , LIKE运算符 在应用程序中为了使用方便，对字符型变量进行比较时经常使用LIKE运算符 进行字符模式的匹配。 需要注意的是对于LIKE运算，如果通配符%出现在字符串的尾部或中间， LIKE运算符将可以使用索引进行字符串的匹配，否则如果通配符%出现在字符串 的开始，则LIKE必须使用全表扫描的方式去匹配字符串，这将产生较大的系统负 荷。 一般情况下，为了提高系统的效率，我们希望用户能够在通配符的左端提供较 多的数据信息以降低查询的数量。 , 避免在索引列上使用NOT或其他运算符号 如果在含有索引列的查询条件中存在“!=”，那么Oracle会直接执行全表扫描， 除非同时使用其它的查询条件。 如果在含有索引列的查询条件使用了运算符号，那么Oracle也不会使用索引，可以采用别的途径来避免这些情况的发生。 不使用索引例子1: SELECT * FROM EMP WHERE ENAME||JOB='SMITHCLERK'; 不使用索引例子2: SELECT * FROM EMP WHERE EMPNO+1000>7777; , NULL值 NULL值是系统中目前尚无法确定的值，在Oracle数据库系统中NULL是一个比所有的确定值都大的值，然而又不能用大于小于等于运算符来比较，对NULL值的处理只能用是与否来判定，所有的对NULL值的判定都会引起全表扫描，除非同时使用其它的查询条件。 , 复合索引 复合索引是使用多个数据列的索引，一般的我们希望用户的查询的所使用的条件顺序和创建索引时所使用的顺序相同。 b)、避免重复查询 减少查询次数，避免重复查询是开发过程中一个重要的原则。 , 合并子查询 在特定情况下，可以通过合并子查询条件来减少对表的访问次数。 缺点是可读性稍差。 在下面例子的执行计划可以看出，第一个查询语句对DEPT表进行了两次重复访问，而第二个查询语句只是对DEPT表进行了一次访问: 低效: SELECT EMPNO,ENAME FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK') AND ENAME=(SELECT DNAME FROM DEPT WHERE LOC='NEW YORK'); 高效: SELECT EMPNO,ENAME FROM EMP WHERE (DEPTNO,ENAME)=(SELECT DEPTNO,DNAME FROM DEPT WHERE LOC='NEW YORK'); , 使用DECODE函数 在Oracle的8i和9i版本中，在特定情况下可以使用DECODE函数，以避免 重复扫描相同记录或重复连接相同的表。 例子: SELECT * FROM TEST; ID LV VALUE 1 1 10 1 2 10 1 3 10 2 2 20 2 3 20 现需要达到以下的统计目的: ID LV1 LV2 LV3 LV4 1 10 10 10 0 2 0 20 20 0 低效SQL:进行了多次重复的查询操作 SELECT ID,SUM(A1) LV1,SUM(A2) LV2,SUM(A3) LV3,SUM(A4)LV4 FROM ( (SELECT VALUE AS A1,0 AS A2,0 AS A3, 0 AS A4,ID,VALUE FROM TEST WHERE LV=1) UNION ALL (SELECT 0 AS A1,VALUE AS A2,0 AS A3, 0 AS A4,ID ,VALUE FROM TEST WHERE LV=2) UNION ALL (SELECT 0 AS A1,0 AS A2,VALUE AS A3, 0 AS A4,ID ,VALUE FROM TEST WHERE LV=3) UNION ALL (SELECT 0 AS A1,0 AS A2,0 AS A3, VALUE AS A4,ID ,VALUE FROM TEST WHERE LV=4) ) GROUP BY ID; 可以用DECODE函数来得到相同结果，但只需要进行一次查询: 高效SQL: SELECT DISTINCT ID, SUM(DECODE(LV,1,VALUE,NULL)) LV1, SUM(DECODE(LV,2,VALUE,NULL)) LV2, SUM(DECODE(LV,3,VALUE,NULL)) LV3, SUM(DECODE(LV,4,VALUE,NULL)) LV4 FROM TEST GROUP BY ID; 类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。 这种统计性质的问题，也可以使用Oracle提供的高级统计函数:ROLLUP和 CUBE。 c)、减少排序的发生 排序是数据库中执行频度比较大的一种操作，根据排序执行的范围不同又可以分为内排序和外排序。我们希望数据库中的排序操作的数量能够被尽量的减少同时每个排序的时间能够缩短。为此我们可以: , 使用UNION ALL来代替UNION , 添加索引。在表连接的时候使用索引可以避免排序的发生，比如添加了合适的索引， 可以使连接方式由排序合并连接(Sort Merge Join)转变为索引的嵌套循环连接 (Indexed Nestted Loop Join)。 , 在DISTINCT,GROUP BY,ORDER BY子句涉及到的列上创建索引。 , 使用较大SORT_AREA_SIZE , 在用户的临时表空间上使用大的EXTENT。 d)、改写查询语句 , 关联子查询与非关联子查询 对于一个关联子查询，子查询是每行外部查询的记录都要计算一次，然而对于一个非关联子查询，子查询只会执行一次，而且结果集被保存在内存中。 因此，通常在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快;而子查询中只有少量的记录的时候，则非关联子查询将会比关联子查询执行得更快。 关联子查询的形式: SELECT STUFF FROM OUTER_TABLE WHERE KEY IN (SELECT OTHER_STUFF FROM INNER_TABLE ); 非关联子查询的形式: SELECT STUFF FROM OUTER_TABLE WHERE KEY IN (SELECT OTHER_STUFF FROM INNER_TABLE WHERE OUTER_TABLE.KEY=INNER_TABLE.KEY); 在开发过程中，存在一种常有的误区，认为使用Exist子句比In子句高效。其实这两种子句各自的适用范围是不同的，但有时候也可以互相交换使用。在使用过程中，请监控SQL的执行计划，以做出最佳的选择。现把两种子句的比较列表如下: In Exists Not In Not Exists 关联子查In子句是多余自动转换为嵌重写为SELECT 重写为 询 的，可以用标准套的循环连接 DISTINCT外部SELECT 连接来代替 连接 DISTINCT外 部连接 非关联子自动转换为嵌不适合 重写为带有不适合 查询 套的循环连接 MINUS操作符 的嵌套循环连 接 , 尽量用相同的数据类型的数据进行比较，以避免发生数据转换 SQL语言对于数据类型不像JAVA和C++那样进行严格的数据类型检查，不同种数据间可以进行某些运算，但是在做数据操作时需要数据库进行隐含的类型转换，在大数据量的查询中，由于要对每一个数据项做同样的操作，会造成时间和CPU处理能力的浪费。 实际应用中通常发生的隐含的数据类型的转换有: 1( 字符型到数字型的转换。 例如:SELECT „1234? +3 FROM DUAL等 2( 数字型到字符型的转换。 例如:UPDATE DEPT SET EMPNO=5678等 3( 日期型到字符型的转换。 例如:UPDATE EMP SET DNAME=SYSDATE等 上述的转换都是隐含发生的，在实际使用中要避免使用不同类型的数据操作。 e)、使用并行查询 并行查询适合下列情况: , 全表扫描的查询语句 , 返回大数据量的查询所改造的语句 , 其它一些数据操作中的查询子句 一般的对于较大的数据量的查询，我们可以使用提示(hint)来强制数据库使用并行 查询，在Oracle数据库中，并行查询的优先级为语句提示(hint)，表的定义，数据库初 始化参数。 关于并行操作的初始化参数设置请参考重要的初始化参数一节。 f)、减少死锁的发生 在Oracle数据库中大量的数据库的锁都是行级锁，不同的会话间竞争同一条记录 的可能性较小，同时Oracle数据库中提供了自动的死锁 检测 工程第三方检测合同工程防雷检测合同植筋拉拔检测方案传感器技术课后答案检测机构通用要求培训 机制来避免数据库的死锁， 保证数据库系统的可用性。因此一般情况下应用系统不需要特殊的设计来解决系统的 死锁问题，但是在下列情况下系统可能出现死锁。 , 表A上的列n上有一个索引，表B上的列m使用A上的列n作为外键，然后表A 的列n上的索引被删除，此时更新表B上列m将造成对表A的表级锁，会导致死 锁的发生。 , 应用大量的使用SELECT ……FOR UPDATE语句造成系统不必要的加锁。 对于第一种情况要对出现死锁的相关表进行检查，确认是否相关索引被错误的删 除。对于第二种情况要修改应用，避免对数据的不必要的加锁。 g)、集合运算符的使用 Oracle数据库的集合运算包括: UNION, UNION ALL, INTERSECT和MINUS操作。 一般情况下当两个集合中的数据都比较多时，集合运算都是比较耗时的操作，使 用时需要谨慎小心。如果可能，可以使用UNION ALL操作代替UNION操作。 7.1.2、使用连接方式的原则 a)、嵌套循环连接(NESTED LOOP JOIN) , 知识点 高中化学知识点免费下载体育概论知识点下载名人传知识点免费下载线性代数知识点汇总下载高中化学知识点免费下载 描述 嵌套循环连接操作关系到两个表，一个内部表和一个外部表。Oracle比较内部数 据集的每一条记录和外部数据集的每一条记录，并返回满足条件的记录。 嵌套循环连接通常会产生巨大的数据量，所以对嵌套循环连接的使用要有严格 的限制。 当一个嵌套循环连接的内部表中存在索引的情况，嵌套循环连接变为改进的有 索引的嵌套循环连接(INDEXED NESTED LOOP JOIN)，通常有索引的嵌套循环连接 在产生较小的数据量的情况下可以较快的执行。 在使用有索引的嵌套循环连接是必须确保在查询中使用了正确的驱动表和正确 的驱动数据集，通常情况下我们使用包含数据量较小的表作为驱动表。 , 应用原则 一般的嵌套循环连接的速度较慢，产生的数据量较大，应该严格控制其使用。 在使用有索引的嵌套循环连接时，必须保证其驱动表有合适的索引，最好为主 键或唯一键，同时希望在另外一张表在相同的列上有索引。 , 举例 下面给出了两种连接的例子: 对于不存在索引的表EMP和DEPT执行以下操作: select count(*) from dept,emp where dept.deptno=emp.deptno and empno=30 为NESTED LOOP JOIN，因为数据库需要对表DEPT中所有的行进行处理。如 果此时JOB表的JOBNO列上有索引则上述查询的方式转变为有索引的嵌套循环连 接(Indexed Nested Loop Join) *************************************************** * Nested Loop Join * -- table emp has index on jobno column * -- table job has index on jobno column ***************************************************** SQL> set autot on exp SQL> select jobname,sum(sal) 2 from emp e,job j 3 where e.jobno=j.jobno 4 and j.jobno <3 5 group by j.jobname; JOBNAME SUM(SAL) ---------------------------------------- ---------- job name 1 27725 job name 2 26025 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 3 2 NESTED LOOPS 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'JOB' 5 4 INDEX (RANGE SCAN) OF 'IND_JOB_JOBNO' (UNIQUE) 6 3 INDEX (RANGE SCAN) OF 'IND_EMP_JOBNO' (NON-UNIQUE) b)、散列连接(Hash Join) , 知识点描述 散列连接将驱动表加载进内存，使用散列技术将驱动表与较大的表进行连接， 连接过程中，对大表的访问使用了散列访问。散列连接可以提高等连接的速度。 如果可用的散列空间内存足够大，可以加载构建输入，那么散列连接算法能够 很好地运行简单的散列连接，但是并不需要将整个输入放入hash_area_size内存。如 果散列连接中较小的驱动表无法放入hash_area_size，那么Oracle将拆分该散列连接， 并使用temp表空间中的临时段来管理这个溢出。 Oracle推荐将驱动表的hash_area_size设置为驱动表字节总数的1.6倍。 , 应用原则 一般的散列连接发生在一个大表和一个小表做连接的时候，此时小表中的数据 全部被读入内存，其处理的速度较快。 , 举例 **************************************************** * Hash Join * -- table emp has 450000+ rows * -- table dept has round 1000 rows ***************************************************** SQL> set autot on exp SQL> select count(*) "number of dept",sum(sal) "total salary" 2 from emp e,dept d 3 where e.deptno=d.deptno 4 and dname like 'DNAME LINE 100%' 5 ; number of dept total salary -------------- ------------ 260 2609357.21 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=281 Card=1 Bytes=32) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=281 Card=1062 Bytes=33984) 3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=4 Card=106 Bytes=2650) 4 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=265 Card=458752 Bytes=3211264) c)、排序合并连接(Sort Merge Join) , 知识点描述 排序合并连接是指从目标表中读取两个记录数据集，并使用连接字段将两个记 录集分别排序的操作。合并过程将来自一个数据集的每一条记录同来自另一个数据 集与之匹配的记录相连接，并返回记录数据集的交集。 排序合并连接有几种不同的排序方法:外部合并连接，反合并连接和半合并连 接。这些不同的排列方法使得合并步骤服务于不同的目的，可以是找到记录数据集 的交集，也可以是找到满足SQL语句中WHERE子句条件的那些记录。 , 应用原则 一般的排序合并连接是在散列连接不能达到应用的要求或Oracle优化器认为排 序合并连接效率更高的情况下使用。在下述的条件下排序合并连接被使用: , 数据表之间的连接不是等值连接而是其它连接 , 数据库使用的优化模式是基于RBO而不是CBO , 举例 在下述的查询中 ***************************************************** * Sort Merge Join * -- table emp has no index on deptno column * -- table dept has no index on deptno column ***************************************************** SQL> set autot on exp SQL> select dname,sum(sal) 2 from dept d,emp e 3 where d.deptno=e.deptno 4 and d.deptno <5 5 group by dname; DNAME SUM(SAL) ---------------------------------------- ---------- DNAME LINE 1 20475 DNAME LINE 2 19475 DNAME LINE 3 22400 DNAME LINE 4 22675 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 MERGE JOIN 3 2 SORT (JOIN) 4 3 TABLE ACCESS (FULL) OF 'EMP' 5 2 SORT (JOIN) 6 5 TABLE ACCESS (FULL) OF 'DEPT' 7.1.3进行复杂查询的原则 a)、限制表连接操作所涉及的表的个数 对于数据库的连接操作操作，我们可以简单的将其想象为一个循环匹配的过程， 每一次匹配相当于一次循环，每一个连接相当于一层循环，则N个表的连接操作就 相当于一个N-1层的循环嵌套。 一般的情况下在数据库的查询中涉及的数据表越多，则其查询的执行计划就越 复杂，其执行的效率就越低，为此我们需要尽可能的限制参与连接的表的数量。 , 3-5个表的处理方法 对于较少的数据表的连接操作，需要合理的确定连接的驱动表，从某种意义上 说，确定合理的驱动表就是确定多层循环嵌套中的最外层的循环，可以最大限度的 提高连接操作的效率，可见选择合适的驱动表的重要性。 在SQL语句中FROM子句后面的表就是我们要进行连接操作的数据表，Oracle 按照从右到左的顺序处理这些表，让它们轮流作为驱动表去参加连接操作，这样我 们可以把包含参与连接的数据量最少的表放在FROM子句的最右端，按照从右到左的顺序依次增加表中参与连接数据的量。 , 5个表以上的处理方法 对于涉及较多的表(>5+)的数据连接查询，其查询的复杂度迅速增加，其连接的存取路径的变化更大，存取路径的个数与连接的表的个数的阶乘有关:当n=5时存取路径,1X2X3X4X5=120个，而当连接的表的个数为6时存取路径变为1X2X3X4X5X6=720个，数据库优化器对于数据的存取路径的判断近乎为不可能，此时完全依赖与用户的语句书写方式。 一般的对于较多的表的连接，要求开发人员查询返回的结果能够有所预测，同时判断出各个参与连接的表中符合条件的记录的数量，从而控制查询的运行时间。 同时为了提高查询的效率，此时可以把部分表的一些连接所形成的中间结果来代替原来的连接表，从而减少连接的表的数目。 , 对表连接操作涉及的表数目的限制 如果查询语句拥有过多的表连接，那么它的执行计划过于复杂且可控 性降低，容易引起数据库的运行效率低下，即使在开发测试环境中已经经过充分的测试验证，也不能保证在生产系统上由于数据量的变化而引发的相关问题。应该在应用设计阶段就避免这种由于范式过高而导致的情况出现。 因此，我们规定在应用开发中，如果表连接数目多于8个表，必须向软件开发中心技术委员会提出申请，说明该应用设计的理由，经批准后才能使用。 b)、限制嵌套查询的层数 应用中影响数据查询的效率的因素除了参与查询连接的表的个数以外，还有查询的嵌套层数。对于非关联查询，嵌套的子查询相当于使查询语句的复杂度在算术级数的基础上增长，而对于关联查询而言，嵌套的子查询相当于使查询语句的复杂度在几何级数的基础上增长。 因此，降低查询的嵌套层数有助于提高查询语句的效率。 , 对嵌套查询层数的限制 如果查询语句拥有过多的嵌套层数，那么会使该查询语句的复杂度高速增加，应该在数据库设计阶段就避免这种情况出现。 我们规定在应用开发中，如果语句的嵌套层数多于5层，，必须向软件开发中心技术委员会提出申请，说明该应用设计的理由，经批准后才能使用。 c)、灵活应用中间表或临时表 在对涉及较多表的查询和嵌套层数较多的复杂查询的优化过程中，使用中间表 或临时表是优化、简化复杂查询的一个重要的方法。 通过使用一些中间表，我们可以把复杂度为M*N的操作转化为复杂度为M+N 的操作，当M和N都比较大时M+N < create table loc1 as select * from loc2; Table created. Elapsed: 00:00:00.19 SQL> delete from loc1 2 where rowid in 3 (select row_id from 4 (select rowid row_id,rownum row_num from loc1) 5 where row_num >10000); 121088 rows deleted. Elapsed: 00:00:12.37 SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL> drop table loc1; Table dropped. Elapsed: 00:00:00.32 SQL> create table loc1 as select * from loc2; Table created. Elapsed: 00:00:00.22 SQL> create global temporary table rowid_rownum 2 (row_id rowid, row_num number); Table created. Elapsed: 00:00:00.01 SQL> insert into rowid_rownum select rowid,rownum from loc1; 131088 rows created. Elapsed: 00:00:00.25 SQL> delete from loc1 where rowid in 2 (select row_id from rowid_rownum where row_num >10000); 121088 rows deleted. Elapsed: 00:00:10.42 d)、使用一些改写复杂查询的技巧 , 转换连接类型 参见上文的改写查询语句部分 , 把OR转换为UNION ALL , 区分不同的情况使用IN或EXISTS 一般的对于主查询中包含较多条件而子查询条件较少的表使用EXISTS， 对于主查询中包含较少条件而子查询条件较多的表使用IN , 使用合理的连接方式 在不同的情况下使用不同的连接方式:散列连接适用于一个较小的表和较大的表的连接，排序合并连接需要对小表进行排序操作，索引的嵌套循环连接对于一般连接是有效的，但是需要在连接的关键字上有索引的存在。 应用开发人员应该根据不同的情况选取合适的连接方式 , 使用并行查询 如果查询的数据在表中所占的比例较大，可以考虑使用并行查询来提高查询的执行速度。 对于UPDATE/INSERT/DELETE操作的查询部分也可以同样做并行查询的处理。 , 使用PL/SQL过程和临时表代替复杂查询。 对于涉及巨大的表的连接的统计查询，由于可能会造成大量的排序统计工作，使得查询的速度变慢，此时可以考虑使用PLSQL替代原来的查询。 7.2、DML语句的调整原则 DML语句包括Insert、Update、Delete和Merge，其中Merge语句是9i的新特性之一，目前我们的应用系统中使用得较少。在使用DML语句的时候，我们也会遇到性能低下的情况，可以参考以下的内容来做出调整。 7.2.1、Oracle存储参数的影响 Oracle的DML语句出现性能问题的一些情况: , Insert操作缓慢并且占用过多的I/O资源。 这种情况发生在PCTFREE较高且行记录较大，频繁地寻找新的空闲数据块的时候。 在数据对象有(多个)索引的情况下，Insert 操作还需要对索引进行维护，这额外的增加了数据插入的成本，所以对于没有过度索引的表的维护是比较花费资源的。 , Update操作缓慢。 Update操作需要获得操作对象上的独占锁，如果其它的用户已经占有了该对象的非兼容的锁，那么Update操作就需要等待，通常这是非常短的时间，但是如果该用户在操作时被打断，则该用户持有这个锁的时间就可能变长，造成其它用户的等待，这是一个管理上的问题。 如果Update操作扩展了一个Varchar或Blob列导致发生了行迁移的时候，其更新也会变慢。 , Delete操作缓慢。 通常发生在记录被删除，而且Oracle必须将数据块重新连接到该表的Freelist的时候。 由于删除操作会产生大量的undo和redo信息，所以对系统的性能的影响较大。如果可能可以使用更改状态标志和在另外的表中插入新的记录来代替删除操作。 对于删除全表的操作可以用truncate table等命令来实现，在PL/SQL等不支持truncate命令的环境中可以使用动态SQL来实现truncate的功能。对于碎片比较多的系统，删除操作在某些时候涉及到数据块的回收。 另外，当有多个任务想要对一张数据表进行Insert或Update操作的时候，这张数据表的段头可能会产生冲突情况，这种冲突可以表现为出现等待事件:Buffer Busy Waits，此时对于数据库表的处理办法是提高Pctfree的值，降低一个数据块中数据的行数，对于冲突的索引可以使用倒排索引来避免同一数据块中的数据的索引存放在同一索引块中。 , 调整原则 请参考上文相关部分以适当地对这几个参数进行设置，以在有效空间利用和高性能之间获得一个平衡点。 一般情况下对于并发更新比较频繁的对象要降低同一数据块中的数据行数以减少系统对于同一数据块的竞争，以空间换时间以提高性能。对于并发更新竞争不是那么频繁的对象要提高同一数据块中的数据行数，以提高系统空间的利用效率，同时提高缓存的利用率以达到提高系统整体效率的目的。 一般的作为队列使用的表的竞争会比较剧烈，这类表中包含的总的数据行数不会太多，所以可以使用空间来换取效率。 7.2.2、大数据类型的影响 使用大数据类型(RAW，LONG，BLOB，CLOB等)的时候，主要问题在于它们常常会超出普通的数据块大小，导致数据列分散到相邻的数据块。这样，每行记录被访问时，Oracle都会产生两次以上的I/O操作。 , 调整原则 对于使用大数据类型的表，一般情况下其数据的行连接是不可避免的，我们能够做的就是尽量的降低这种事件发生的频率。 一般的，对于单独存储的LOB对象，我们可以指定其使用较大的db_block_size的表空间以控制其使用的数据块的个数，同时减少对LOB对象的访问次数，其中数据块的大小根据各个不同的LOB的平均大小有所不同。 在Oracle9i，小于4k的LOB对象可以和数据存储在一起，这样对数据访问速度的影响较大，这种情况下我们可以按照LOB对象的实际大小而选择不同表空间来存储数据。 7.2.3、DML执行时约束的开销 约束会对DML操作的性能产生影响。 , 完整性约束:时间会耗费在验证每一个数据值是否合法上。 , 主键约束:主键约束是由唯一索引来强制实施的，而且这个索引在插入和更新操作 上的开销使大容量的插入操作和更新操作运行变慢，因为每个值都必须从索引中查 询一次。 , 外键约束:强制实施了交互表之间的数据关系，必须访问外部的数据表以确认当前 值是否合法，才能进行插入。 , 其它约束:对于其它检查的，数据也需要做相应的检查 , 触发器:触发器对DML的执行效率也有较大的影响，特别当触发器的类型为for each row时。 , 调整原则 , 在执行大容量的插入或更新任务时，可以暂时禁用所有与所影响的数据表有关的约 束、触发器，装载数据，最后才重新启用约束 , 在启用约束时，需要考虑非法的数据。 7.2.4、DML执行时维护索引所需的开销 在记录被插入和修改时，表上所有的参与索引都必须实时地进行更新。这通常会产生由于大量排序而增加的系统开销，严重降低系统的执行性能。 , 调整原则 , 在大型的DML批操作中，在更改数据表之前，删除全部索引。在批操作之后，重 新建立起索引。 , 如果索引因为不平衡而产生拆分等额外操作，那么可以通过重建索引操作，也会减 少维护索引所需的时间。 , 如果在数据被加载到数据库之前其数据已在外部完成排序，则在创建索引是可以使 用NOSORT 选项，需要注意的是NOSORT只能使用升序而不能使用降序，并且 不能使用在倒排、分区、位图索引中。 , 在批量数据加载后的创建索引的过程中，可以指定用来排序的表空间已提高排序的 效率，在指定排序表空间是需要注意该表空间的extent的大小，一般情况下该值越 大越好。 7.3、PL/SQL的使用原则 7.3.1、在PL/SQL中使用SQL , 知识点描述 PL/SQL是Oracle公司对SQL的过程化扩展。在标准SQL的基础上面增加了流程控 制、游标、异常处理等机制。PL/SQL是一种第四代的高级编程语言，它可以单独 使用，也可以嵌入其他宿主语言一起使用。PL/SQL使得用户对Oracle数据库的操 作变得非常简单。 , 使用原则 , 相关参数 a) CURSOR_SHARING 在Oracle9i之前，该参数可以设为 exact, force，在Oracle9i之后，该参数可以设 为 exact, force和similar,缺省值为exact。 b) 何种情况下使用 只有在用户的应用已经投入使用的并且SQL语句的共享效果不好，出现 library cache latch的情况下我们推荐使用cursor_sharing作为一种补救手段。 在进行开发的过程中，设定cursor_sharing,exact , 如何使用可以提高效率 a) 尽量使用变量绑定 b) 尽量使用RETURNING子句 c) 使用NOCOPY的编译提示。默认情况下，OUT和IN OUT参数都是按 值传递的。为参数NOCOPY的编译提示表示按照引用传递。 , 使用过程之中会经常出现的错误以及解决方案 在Solaris 上的8.1.7.2版本中会出现大量的ORA-7445错误，升级后错误消失。 ORACLE9i以前的版本中通过DB*LINK的事务可能有ORA-600错误出现。 , 举例 , 使用变量绑定的例子:在sqlplus中定义变量，使用变量做为查询条件。 SQL> define c=1; SQL> select * from t1 where c1=&c; , 使用RETURNING子句的例子:更改某员工工资水平的同时返回更改员工的 姓名和更改后的薪水。 PROCEDURE update_salary (emp_id NUMBER) IS name VARCHAR2(15); new_sal NUMBER; BEGIN UPDATE emp SET sal = sal * 1.1 WHERE empno = emp_id RETURNING ename, sal INTO name, new_sal; END; , 当参数包含大数据结构(如集合，对象类型等)，使用按值传递将会消耗过多 的内存资源。为了避免这种情况，我们在参数传递时使用NOCOPY的编译提 示。 DECLARE TYPE Platoon IS VARRAY(200) OF Soldier; PROCEDURE reorganize (my_unit IN OUT NOCOPY Platoon) IS „„ BEGIN „„ END; 7.3.2、游标 , 知识点描述 为了处理SQL语句，Oracle必须分配一片内存区域，用来存储完成该处理所必需的信息，这就是上下文区域(CONTEXT AREA)。游标(CURSOR)就是一个指向上下文区域的句柄(handle)或指针。必须首先通过游标定位某个位置，然后才能处理该位置的内容。 显式游标用来处理返回多于一行的select语句。显式游标可以由用户自定义， 并通过open,fetch,close步骤来调用。 隐式游标用于处理insert,update,delete和单行select…into语句。隐式游标是系统自定义的，通过PL/SQL引擎打开和关闭，因此，隐式游标不需要用户open,fetch,close。 游标变量动态地指向sql语句。它类似于pl/sql变量，在运行时刻可以拥有不同的取值，因此可以实现在运行时刻与不同的语句相关联。 游标属性附加在游标名字的后面，但游标属性返回的不是类型，它返回的是在表达式中可以使用的数值。游标属性分以下四种: %FOUND:这是一个布尔属性。如果前一个fetch语句返回一个行，那么它就会返回true，否则，返回false。 %NOTFOUND:这是一个布尔属性，如果前一个FETCH语句返回一个行，返回FALSE。仅当前一个FETCH语句没有返回任何行，才返回TRUE。 %ISOPEN:这是一个布尔属性，用来确定相关的游标是否被打开。如果已打开，返回TRUE，否则，返回FALSE。 %ROWCOUNT:这是个数字属性，它返回到目前位置，由游标返回的行的数目。 , 使用原则 , 相关参数 a) cursor_space_for_time b) open_cursors c) session_cached_cursors , 何种情况下使用 , 如何使用可以提高效率 a) 尽量使用变量 对于经常使用的SQL语句，推荐在语句中使用变量来代替常量 ，这样SQL 语句可以在SGA中共享，避免了每次执行对语句的分析，提高了内存和 CPU的使用效率。 b) 空记录测试 在应用开发的过程中，经常需要测试表中是否含有符合某种条件的记录， 一种常见的错误的做法是使用count(*)的方式判断其值是否为0，这是一个 极端低效率的做法，因为它要对全部数据扫描一遍。 推荐使用判断游标属性的方法。 原来的代码: SELECT count(*) INTO :v_tmp FROM … WHERE …. IF v_tmp >0 THEN ……………. END IF; 改进的代码: CURSOR c1 is SELECT /*+ first row */ 1 FROM … WHERE … AND rownum=1; OPEN c1; If c1%NOTFOUND THEN …………… END IF; , 使用过程之中会经常出现的错误以及解决方案 a) “ora-01555” 原因: 以下处理流程，是Oracle开发的经典错误: step1:先打开cursor(以综合前置为例:select 当日所有交易 from 联机表，十多万条); step2:从游标中fetch记录，insert入历史表，delete联机表，如此循环，到达若干条记录后commit; step3:再接着如此处理，直到所有记录处理完毕; step4:处理结束后关闭cursor。 按照ANSI标准，cursor在提交时无效，必须重新打开。但是Oracle允许编写非标准的SQL，应用程序可以跨commit从cursor中获取行，不过commit后rbs段是可以被重用的，而cursor本身又必须保持读一致性，所以容易出现“rollback too old”的错误。 解决建议: 方案1:若干记录commit后立即关闭cursor,再重新打开cursor，继续处理。 方案2:只commit一次或尽量减少commit，但需要较大rbs空间。 b) “PLS-00382: expression is of wrong type” 原因: 有相同的返回类型，但是变量类型不一样的两个游标变量之间赋值，会产生类型不匹配的错误。 例如: DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv1 EmpCurTyp; emp_cv2 TmpCurTyp; BEGIN emp_cv2 := emp_cv1; --会产生类型不匹配的错误 …… END; 解决建议: 将两个游标变量改为相同类型。 上面的例子改为如下: DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv1 EmpCurTyp; emp_cv2 EmpCurTyp; BEGIN emp_cv2 := emp_cv1; …… END; , 举例 , 打开游标时，通过游标属性判断游标纪录是否为空的方法。 declare CURSOR c1 IS SELECT app_id FROM app_def WHERE app_enname = p_app_enname; BEGIN OPEN c1; EXCEPTION WHEN no_data_found THEN BEGIN ...... END; END; , 打开游标前判断游标纪录是否为空的方法。 declare CURSOR c1 IS SELECT app_id FROM app_def WHERE app_enname = p_app_enname; v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM app_def WHERE app_enname = p_app_enname; IF v_count = 0 THEN OPEN c1; ...... END IF; end; 7.3.3、动态PL/SQL , 知识点描述 大多数PL/SQL程序所做的工作都是特定的、预知的工作。例如，一个存储过程可能接受一个员工号码做为输入参数，然后更新该员工的薪水。在这种情况下，UPDATE语句的全文在编译时是确定的。这样的语句是不会在执行阶段发生变化的，这种SQL语句我们称之为静态SQL语句。 但是有些时候，程序必须在执行阶段处理一个变化的SQL语句。例如，一个通用的报表程序为了产生不同的报表必须运行不同的SQL语句。这种语句很可能在执行阶段发生改变，这种SQL语句我们称之为动态SQL语句。 动态SQL语句存储在字符串中，程序在执行阶段建立这个语句。这些字符串必须包含合法的SQL语句，要绑定的变量前要加冒号，变量的名称是无关的。例如，下面两个语句是没有区别的: 'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm' 'DELETE FROM emp WHERE sal > :s AND comm < :c' , 使用原则 , 相关参数 , 何种情况下使用 由于在通常的PL/SQL中只能使用数据操作语言DML和事务控制语言 TCL而不能使用其它一些命令如数据定义语言DDL和系统控制命令，当出现 这种需求是一个做法是调用外部任务，另外一个做法就是使用动态的SQL。 动态 SQL并不在客户端被解释转换，而是由客户端的程序传输到服务器 端后在服务器端解释并执行。 使用动态SQL的最常见的用法有:使用truncate table命令代替delete from table的操作、在系统进行日结、月结、年终结算时创建新的数据表，创建应用 系统使用的临时表等， 下面是通过动态SQL来使用truncate table命令的方法: ... create or replace procedure p_truncate_table(table_name varchar2(30)) as cursor_handle integer; no_query_sql_command varchar2(200); begin no_query_sql_command := 'truncate table ?||table_name; cursor_handle:=dbms_sql.open_cursor; dbms_sql.parse(cursor_handle,no_query_sql_command,1); cursor_handle:=dbms_sql.execute(cursor_handle); dbms_sql.close_cursor(cursor_handle); end; / ... 应当说明的是动态SQL的使用在不同的版本间稍有不同，在使用是需要在 不同的版本上做测试。 , 如何使用可以提高效率 a) 尽量使用变量绑定 b) 使用批量动态SQL(BULK FETCH语句， BULK EXECUTE IMMEDIATE语句， FORALL语句， COLLECT INTO语句， RETURNING INTO子句) , 使用过程之中会经常出现的错误以及解决方案 a) “ORA-00903: invalid table name” 原因: 在动态PL/SQL中数据对象名称不能使用变量绑定。 例如:下面的存储过程 CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name; END; 解决方法: 将变量绑定改为直接字符串拼凑。 CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || table_name; END; , 举例 , 在动态SQL中使用变量绑定的例子 CREATE PROCEDURE fire_employee (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num' USING emp_id; END; 7.3.4、对象 , 知识点描述 对象(object)是一个用户定义的复杂的数据类型，其数据结构包含一些变量，以及对这些变量进行处理的函数和过程。组成数据结构的变量，称为“属性” (attributes)。对属性进行操作的过程和函数，称为“方法”(methods)。当创建一个对象时，实际上是创建了一个对真实世界中的对象的抽象模板。例如，对一个雇员创建对象，这个对象包括的属性有:姓名，部门，职位，等等;包括的方法有:工资计算，考勤计算，等等。 使用对象时，需要创建实例(instance),通过实例来调用对象中的方法。在真实世界中，不难理解这样做的目的。还是以雇员对象为例，只有一个对象，确有很多个雇员，每个雇员的属性都不相同，所以，要为每个雇员创建实例，他们可以共享“雇员对象”这个数据结构，而分别拥有自己的数据。 , 使用原则 , 相关参数 , 何种情况下使用 , 如何使用可以提高效率 , 使用过程之中会经常出现的错误以及解决方案 a) ORA-30625: method dispatch on NULL SELF argument is disallowed 范例: DECLARE Eagle employee; --创建实例 Jacky employee; --创建实例 BEGIN Dbms_output.put_line(Eagle.salary(eagle.rank,eagle.dept)); --通过实例调用对象中的方法 END; 该语句会报ora-30625的错误。 原因: 实例已创建，但没有初始化。 解决建议: 在调用方法前，初始化实例。以上范例中，加上初始化语句: Eagle := NEW employee('EAGLE',24,7,'TACH'); , 举例 例一:创建对象 CREATE OR REPLACE TYPE employee AS OBJECT ( -- 定义属性(attribute) Name VARCHAR2(40), -- 定义方法(method) MEMBER FUNCTION salary (rank NUMBER,dept VARCHAR2) RETURN NUMBER, ); CREATE OR REPLACE TYPE BODY employee AS MEMBER FUNCTION salary (rank NUMBER,dept VARCHAR2) RETURN AGE IS BEGIN ………… END salary; END; 例二:使用对象 DECLARE Eagle employee; --创建实例 Jacky employee; --创建实例 BEGIN Eagle := NEW employee('EAGLE',24,7,'TACH'); --初始化实例 Jacky := NEW employee('JACKY',36,1,'MANAGE'); --初始化实例 Dbms_output.put_line(Eagle.salary(eagle.rank,eagle.dept)); --通过实例调用对象中的方法 END; 7.3.5、大对象类型(LOB) , 知识点描述 一个LOB是一个存储大量数据的数据库字段，例如图形文件或长的文本形式的文档。Oracle提供了四种不同类型的LOB:CLOB、NCLOB、BLOB、BFILE。 LOB类型 说明 CLOB 用于存储单字节字符数据 BLOB 存储没有结构的二进制数据 NCLOB 用于存储定宽的多字节国家字符集数据 BFILE 用于对Oracle数据库以外存储的大型二进制文件进行 只读形式的访问。 LOB数据不是以内联的方式(inline)存储在数据库表中。LOB数据是存储在一个单独的位置上，同时一个“LOB定位符”存储在原始表中。该定位符是一个指向实际数据的指针，实际数据可以存储在和原始数据有不同存储参数的表空间中。 , 使用原则 , 相关参数 , 何种情况下使用 LOB是用于存储非结构化数据的。所谓非结构化数据是指不能被分解为标准组件的数据。例如，一个员工可以分解为姓名(字符串)，标识(数字)，薪水等等。但是，如果给你一张图片，你会发现数据是由一长串0和1的数字流，在数据存储方面，他们不能分解为良好的结构。非结构化的数据往往是非常大的，如文本、图片、视频片断或者音乐等等。一个典型的员工纪录可以有几百个字节，但是即使是少量的多媒体数据也可能有它几千倍那么大。 LOB类型帮助支持Internet应用，随着Internet和内容丰富应用的发展，数据支持这样一种数据类型是非常有需要的:1)可以存储非结构化数据;2)优化处理大量的非结构化数据;3)为存储在数据库内或者数据库外非结构化数据提供统一的访问方式。 由于BLOB没有字符集转换，因此当几种LOB都能满足应用要求时，推荐使用BLOB。 , 如何使用可以提高效率 a) SQL*Loader性能:加载数据到内部LOB。 下表我们可以看到加载数据到内部LOB的各种方法的相对性能的比较， 在后面我们将一一列举每种加载方法。 加载方法 相对性能 字段长度预先确定 最高 字段之间由分隔符分隔 低 长度-值对字段 高 每个文件一个LOB 高 b) 选择内联存储和外联存储的比较。 一般情况下，当LOB的容量小于4K时，Oracle建议对LOB进行内联存储。内联存储在数据操作时效率更高。但是当需要进行大量的基本表操作时，我们不希望LOB进行内联存储，如全表扫描、范围扫描或者在表的非LOB列上面有大量的更新、查询操作。 c) 提高JDBC驱动加载BLOB和CLOB的性能 使用JDBC Thin驱动向LOB中加载数据是比较慢的，因为JDBC Thin驱动实际上是使用DBMS_LOB包。而使用JDBC OCI和JDBC server-side internal 驱动使用本地的LOB API，加载数据会比JDBC Thin驱动快。因为使用本地LOB API没有JDBC Thin驱动实现带来的一些额外消耗。 Oracle建议你使用InputStream和OutputStream来访问和操作LOB数据。因为使用流的方式，JDBC驱动可以合理的控制缓存以减少网络的数据往返。 DBMS_LOB.LOADFROMFILE()加载LOB数据的效率比DBMS_LOB.WRITE()高，但使用DBMS_LOB.LOADFROMFILE()加载数据要求数据必须放在服务端。 d) 适时的释放临时LOB。 在PL/SQL，C (OCI)和Java中，当SQL语句在LOB列上面操作时会返回临时LOB作为结果，临时LOB在PL/SQL块结束时会自动释放。 你可以在任何时候释放不需要的临时LOB，以释放系统系统资源和临时表空间。如果不合理的释放SQL语句返回的临时LOB，临时表空间将会被过度的消耗，而系统性能将会下降。 , 使用过程之中会经常出现的错误以及解决方案 a) 更改LOB段的存储参数时出现ORA-904错误。 问题描述: 更改一个LOB对象的MAXEXTENTS存储参数时得到904错误， SQL> CREATE TABLE t_lob 2 (DOCUMENT_NR NUMBER(16,0) NOT NULL, 3 DOCUMENT_BLOB BLOB NOT NULL 4 ) 5 STORAGE (INITIAL 100k 6 NEXT 100K 7 PCTINCREASE 0 8 MAXEXTENTS 100 9 ) 10 TABLESPACE users; Table created. SQL> select segment_name, segment_type, initial_extent, 2 next_extent, pct_increase, max_extents 3 from user_segments; SEGMENT_NAME SEGMENT_TYPE INITIAL NEXT_EXTENT PCT MAX_EXTENTS ------------------------- ------------ ------- ----------- --- ----------- T_LOB TABLE 106496 106496 0 100 SYS_IL0000030066C00002$$ LOBINDEX 40960 40960 50 2147483645 SYS_LOB0000030066C00002$$ LOBSEGMENT 40960 40960 50 505 SQL> alter table t_lob modify lob (SYS_LOB0000030066C00002$$) 2 (storage (maxextents 200)); alter table t_lob modify lob (SYS_LOB0000030066C00002$$) * ERROR at line 1: ORA-00904: "SYS_LOB0000030066C00002$$": invalid identifier 解决办法:更改LOB对象的列名而不要直接使用LOB段的名字 SQL> select table_name, column_name from user_lobs 2 where segment_name = 'SYS_LOB0000030066C00002$$'; TABLE_NAME COLUMN_NAME ------------- --------------------------------- T_LOB DOCUMENT_BLOB SQL> alter table T_LOB modify LOB (document_blob) 2 (storage (maxextents 200)); Table altered. Explanation ----------- The ALTER TABLE is a command that allows you to modify logical entities, not physical entities. b) 更改LOB时出现ORA-22853错误。 在执行下属语句时出现错误 MODIFY LOB lobitem (storage_clause pctversion cache logging) ORA-22853: invalid LOB storage option specification Cause: A LOB storage option was not specified Action: Specify one of CHUNK, PCTVERSION, CACHE, NOCACHE, TABLESPACE, STORAGE, INDEX as part of the LOB storage clause. 解决办法:把LOB对象的名字放在括号内，正确的命令是: MODIFY LOB (lobitem) (storage_clause pctversion cache logging) , 举例 , 加载字段长度预先确定的LOB数据 在控制文件中，加载入列的LOB的数据长度是确定的，字段长度预先确定可以 使数据解析器非常高效的工作。不幸的是，通常我们很难保证所有的LOB的数据长 度是相等的。 Control File: LOAD DATA INFILE 'sample4.dat' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' (Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT 'FirstStory1.txt') CHAR(32)) Data File (sample4.dat): 007, 008, Secondary Data File (FirstStory1.txt): Once upon the time ... The end, Upon another time ... The end, , 字段之间由分隔符分隔的数据的加载 在LOBFILE文件中，LOB数据由分隔符分隔，这样就允许不同长度的LOB加 载入相同的列。当然，高度的灵活性是以牺牲性能为代价的。加载这种格式的数据 相对而言是比较慢的，因为加载器必须扫描所有数据并查找分隔符字符串。 Control File LOAD DATA INFILE 'sample5.dat' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' (Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT 'FirstStory2.txt') CHAR(2000) TERMINATED BY "\n") Data File (sample5.dat) 007, 008, Secondary Data File (FirstStory2.txt) Once upon a time... The end. Once upon another time... The end. , 加载字段是长度-值对的LOB数据 每个LOB数据前有一个数字标识该LOB的长度。这种方式的性能会比分隔符分隔数据的方式高效，但是这种方式要求你在加载数据之前知道每个LOB的长度。 Control File LOAD DATA INFILE 'sample6.dat' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT 'FirstStory3.txt') VARCHARC(4,2000) ) Data File (sample6.dat) 007, 008, Secondary Data File (FirstStory3.txt) 0031 Once upon a time ... The end. 0000 , 每个LOBFILE仅包含一个LOB的加载方式。 Control File LOAD DATA INFILE 'sample3.dat' INTO TABLE Multimedia_tab REPLACE FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL(5), ext_FileName FILLER CHAR(40), Story LOBFILE(ext_FileName) TERMINATED BY EOF ) Data File (sample3.dat) 007,FirstStory.txt, 008,/tmp/SecondStory.txt, Secondary Data File (FirstStory.txt) Once upon a time ... The end. , 将一个GIF文件john.gif写入BLOB的例子。 /**将一个GIF文件john.gif写入BLOB的例子。*/ import java.sql.*; import java.io.*; import java.util.*; import Oracle.jdbc.driver.*; import Oracle.sql.*; public class LobExample { public static void main (String args []) throws Exception { DriverManager.registerDriver(new Oracle.jdbc.driver.OracleDriver()); // Connect to the database Connection conn=DriverManager.getConnection ("jdbc:Oracle:oci9:@", "scott", "tiger"); conn.setAutoCommit (false); Statement stmt = conn.createStatement (); try { stmt.execute ("drop table persons"); // Create a table containing a BLOB and a CLOB stmt.execute ("create table persons (name varchar2 (30), picture blob)"); stmt.execute ("insert into persons values ('John', EMPTY_BLOB())"); }catch (SQLException e){ // An exception could be raised here if the table did not exist already. } // Select the BLOB ResultSet rset = stmt.executeQuery ("select picture from persons where name = 'John'"); if (rset.next ()) { // Get the BLOB locator from the table BLOB blob = ((OracleResultSet)rset).getBLOB (1); OutputStream ostream = blob.getBinaryOutputStream (); // Declare a file handler for the john.gif file File binaryFile = new File ("john.gif"); FileInputStream istream = new FileInputStream (binaryFile); // Create a tempory buffer byte[] buffer = new byte[1024]; int length = 0; // Use the read() method to read the GIF file to the byte array buffer, //then use the write() method to write it to the BLOB. while ((length = istream.read(buffer)) != -1) ostream.write(buffer, 0, length); // Close the inputstream and outputstream istream.close(); ostream.close(); } // Close all resources rset.close(); stmt.close(); conn.close(); } } 7.3.6、异常处理 , 知识点描述 PL/SQL中，一个警告或错误条件被称为“异常”，这个条件可以是系统本身的，也可以由用户自定义。当满足条件时，程序从当前块中跳出，进入异常处理块，程 序的异常处理由用户自己定义。 使用异常处理的目的是，防止错误的结果被带到外部程序，并且使出错的程序异常 处理后退出，而不是中断后直接返回操作系统，从而提高应用程序的健壮性。 , 使用原则 , 何种情况下使用 在使用的PLSQL块中,如果使用了不能返回的确定结果的SQL语句，应用要 在EXCEPTION中对程序可能出现的异常进行处理，避免出现未处理的出错被传 递到外层块，导致程序逻辑错误。 对于系统已经定义了的异常，应用可以直接使用，对于用户自行定义的异常需 要使用pragma exception_init(exception_name，-SQLCODE)方式来初始化。 对于继续处理的EXCEPTION,可以不记录错误堆栈，对于无法继续处理的程 序，EXCEPTION中至少要获取程序的返回码，返回错误码，错误描述，其名称分别定为out_retcode, out_errcode, out_errm。为了能够进一步分析程序出现异常的原因，最好能够获得出现异常时的调用错误堆栈 , 使用过程之中会经常出现的错误以及解决方案 很多情况下，特别是含有DML语句的程序中，系统希望一个存储过程是一个原子操作，要么不执行，要么完全执行。由于出错后程序跳出当前块，进入异常处理块，所以有可能因为程序只执行了一半而造成数据不一致。为防止这种情况发生，可以将commit尽量放在块的末尾，或者可能发生出错的地方之后。并在异常处理中使用rollback。将没做完的事务回滚。 , 举例 例如:EXCEPTION WHEN NO_DATA_FOUND THEN out_retcode:='L9003'; out_errcode:=SQLCODE; out_errm:=SQLERRM; RETURN; WHEN OTHERS THEN out_retcode:='L9000'; out_errcode:=SQLCODE; out_errm:=SQLERRM; RETURN; 7.3.7、PACKAGE , 知识点描述 包是一个可以将相关对象存储在一起的PL/SQL结构，包含了两个分离的部件:规范(specification)和主体(body)，每个部件都单独被存储在数据字典中。包中可以包含过程、函数、游标和变量。将这些对象放入一个包中的一个好处就是可以从其他的PL/SQL快中引用它们，这样包也提供了用于PL/SQL的全局变量。 , 使用原则 , 何种情况下使用 有以下情况发生时，需要使用包: a) 存在私有函数(或过程)或私有变量，只能被某一函数(或过程)调用时， 将这个私有的函数(或过程)或私有变量放入调用主函数(或过程)所在 的包内。 b) 函数(或过程)的参数里有自定义的类型时，应该建立包，将该类型的定 义和该函数(或过程)的定义都放入包中。 c) 直观的说，当具有一些能够处理相关的任务的程序代码同时希望包装共享 该代码时可以使用包。 , 如何使用可以提高效率 a) 使包尽量大众化，使其可以在未来的应用中被重用。 b) 尽量使用Oracle提供的包，注意不要重复创建Oracle已有的包。 c) 包头反映出应用的设计。应该在包头只声明用户或其他开发人员必须用到 的类型，项和子程序，而将包自己用的对象作为私有变量放在包体中。 d) 当代码改变时，为减少重编译，除非必要，尽量少在包头中声明对象。因 为对包体做改变时，不要求重编译，而对包头做改变时，要求重编译。 e) 减少包中静态的全局变量的使用，避免操作的相互影响。 7.4、数据库编码规范 a)、SQL书写规范 b)、控制选择范围 在SELECT、INSERT和UPDATE等语句中，要使用明确的字段名，从而保持良好的可移植性，例如: SELECT C1,C2 FROM TABLE_NAME; INSERT INTO TABLE_NAME(C1,C2) VALUES(V_C1,V_C2); 避免使用如下语句: SELECT * FROM table_name; INSERT INTO table_name VALUES(v_c1,v_c2); c)、大小写规范 在编写SQL语句时要遵循以下规则: 在整个SQL语句中，一律使用大写。 例: SELECT MYVALUE1,MYVALUE2 FROM MYTABLE WHERE MYVALUE1=?1?; 7.5、PL/SQL书写规范 a) 注释规范 a、 注释应该是有意义的，而不是重述代码。 例: 不好的注释: DECLARE v_temp NUMBER:=0; --Assign 0 tov_Temp 较好的注释: DECLARE v_temp NUMBER:=0; --主循环中用到的临时变量 b、 注释应简洁，易懂，尽量用中文，为了表达准确，名次或操作上也允许用英文。 c、 在每个存储过程、函数和包的开始加入注释，内容包括:本程序的简要功能描述、编写 者、编写日期，而且各存储过程的开头注释应保持统一格式如下: /**************************************************************** -- NAME: Proc_Mobile_InsertB2CPayLog -- PURPOSE: 插入交易日志表。 -- REVISIONS: -- Ver Date Author Description -------- ----------- ------------- -------------------- -- 1.0 2003-11-30 John 1. 创建 -- 1.1 2003-12-08 John 2. 修改 ****************************************************************/ a、 输入输出参数的旁边添加注释，注明此变量的意义 b、 每个块或大分支的开始添加注释，描述块的简要功能，若使用算法，简单描述算法目的 和结果。 b) 大小写规范 尽管PL/SQL语言不区分标识符的大小写，但为了增加代码的可读性， 对大小写风格要求如下: 除了变量名，一律使用大写。 例: SELECT MYVALUE1,MYVALUE2 FROM MYTABLE WHERE MYVALUE1=my_var; 其中my_var为存储过程的变量。 而不应该采用以下的写法: SELECT myvalue1,myvalue2 from MYTABLE where myvalue=?1?; c) 缩进规范 a、 创建存储过程语句中，同一层的CREATE,IS,BEGIN,END这几个关 键字应位于同一列，其它部分依次缩进。 例: 创建过程时: CREATE „„ („„, „„) IS [AS] BEGIN „„ „„ END „„; 创建函数时: CREATE „„ („„, „„) RETURN „„ IS|AS BEGIN „„ „„ END „„; 创建包时: CREATE IS „ PROCEDURE „ „ FUCTION „ „ END „; d) 语句详述 a、变量定义语句 每行至多包含一条语句，例如: 正确写法: v_valid BOOLEAN; v_area VARCHAR2(20); 错误写法: v_valid BOOLEAN; v_area VARCHAR2(20); b、if语句 同一层的IF，ELSEIF,ELSE和END应该开始于同一列,执行语句缩进，例: IF shoe_count < 20 THEN order_quantity := 50; ELSIF shoe_count < 30 THEN order_quantity := 20; ELSE order_quantity := 10; END IF; 如果多层IF嵌套，则内层IF语句要缩进，例: IF condition1 THEN statement1; ELSE IF condition2 THEN statement2; ELSE IF condition3 THEN statement3; END IF; END IF; END IF; c、CASE语句 CASE和END关键字位于同一列，WHEN和ELSE关键字缩进，例: CASE grade WHEN 'A' THEN dbms_output.put_line('Excellent'); WHEN 'B' THEN dbms_output.put_line('Very Good'); WHEN 'C' THEN dbms_output.put_line('Good'); WHEN 'D' THEN dbms_output.put_line('Fair'); WHEN 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('No such grade'); END CASE; d、LOOP语句 同一层的关键字位于同一列，层内的语句或嵌套依次缩进，例: LOOP语句: LOOP ... LOOP ... EXIT outer WHEN ... END LOOP; ... END LOOP outer; WHILE-LOOP语句: WHILE condition LOOP sequence_of_statements END LOOP; FOR-LOOP语句: FOR ctr IN 1..10 LOOP IF NOT finished THEN INSERT INTO ... VALUES (ctr, ...); factor := ctr * 2; ELSE ctr := 10; END IF; END LOOP; 7.6、规范命名中英对照表 注意: 1、 在本表中，部分缩写恰好是Oracle保留字之一，因此这部分单词不能直接作为对象 名，使用时必须加以前缀或与其他标识符一起使用; 2、 由于中文的语义较为复杂，因此部分单词采用多对一的方式，即多个中文对应一个 英文名称。 字段中文名称 字段英文名称 备注(单字段全称，供参考) 账户 ACCT Account 累计 ACML Accumulate 增加 ADD ( ORACLE保留字) Add 地址 ADDR Address 年龄 AGE Age 代理 AGT Agent 金额 AMT Amount 数量 AMT Amount 申请 APPL Apply 授权 AUTH Authorize 有效 AVAIL Availability 平均 AVG Average 返回 BACK Back 响应 BACK Back 余额 BAL Balance 名册 BDRL Beadroll 开始 BGN Begin 买卖 BNA Bid-Ask 分行 BNCH Branch 出生 BORN Born 卡 CARD Card 现钞 CASH Cash 种类 CATE Category 证件 CERT Certificate 收费 CHG Charge 市 CITY City 确认 CNFM Confirm 次数 CNT Count 笔数 CNT Count 代码 CODE Code 备注 COMM Comment 公司 COMP Company 配偶 CST Consort 控制 CTRL Control 联系 CTT Contact 当前 CUR Current 币种 CURR Currency 货币 CURR Currency 数据 DATA Data 日期 DATE ( ORACLE保留字) Date 日 DAY Day 申报 DCLR Declare 消除 DEL Delete 删除 DEL Delete 存款 DEPST Deposit 部门 DEPT Department 下载 DWND Download 美元 DOLLAR Dollar 域 DOMN Domain 电子邮件 EMAIL Email 委托 ENTT Entrust 错误 ERR Error 建立 ESTH Establish 外汇 EXCH Foreign Exchage 指数 EXPT Exponent 失败 FAIL Fail 传真 FAX Fax 费用 FEE Fee 最后 FINAL Finally 标志 FLAG Flag 符号 FLAG Flag 冻结 FREZ Freeze 功能 FUNC Function 收款 GATH Gathering 货物 GOODS Goods 历史 HIS History 港币 HKD Hongkong Dollar 住宅 HOME Home 标识 ID Id 编号 ID Id 转入 IN ( ORACLE保留字) In 收入 INCOME Income 信息 INFO Information 指令 INSTR Instruction 利息 INTT Interest 界面 ITFC Interface 意向 ITT Intent 工作 JOB Job 语言 LAGG Language 最近 LAST Last 上级 LEAD Leader 领导 LEAD Leader 贷款 LEND Lend 级别 LV Level 登录 LGN Logon 清算 LIQT Liquidate 列表 LIST List 限制 LMT Limit 日志 LOG Log 邮政 MAIL Mail 标志 MARK Mark 标记 MARK Mark 最大 MAX ( ORACLE保留字) Maximum 商户 MCHT Merchant 菜单 MENU Menu 最小 MIN ( ORACLE保留字) Minimum 交易市场 MKT Market 管理 MNG Manage 方式 MODE ( ORACLE保留字) Mode 修改 MODF Modify 模块 MODUL Module 月 MONTH Month 婚姻 MRG Marriage 消息 MSG Message 报文 MSG Message 复合 MULT Multiplex 名 NAME Name 姓名 NAME Name 新 NEW New 留言 NOTE Note 数值 NUMERIC Numeric 旧 OLD Old 开通 OPEN Open 操作 OPER Operation 其他 OTHER Other 网点 OTT Outlet 页面 PAGE Page 参数 PARA Parameter 支付 PAY Pay 转帐 PAY Pay 每日 PDAY Per-Day 个人 PERSON Personal 程序 PGM Program 电话 PHONE Phone 端口 PORT Port 职务 POST Post 属性 PPTY Property 价 PRICE Price 价格 PRICE Price 项目 PROJ Project 密码 PSW Password 省 PVC Province 查询 QRY Query 限额 QUOTA Quotation 率 RT Rate 接收 RECV Receive 减少 REDC Reduce 参考 REF Reference 注册 REG Register 汇款 REMIT Remit 租金 RENT Rent 结果 RESULT Result 退货 RETN Return 撤销 REVOKE Revoke 人民币 RMB 保留 RSV Reserve 储蓄 SAV Saving 学校 SCHOOL School 本人 SELF Self 流水号 SEQ Sequence 序号 SERIALNO Serial Number 设置 SET ( ORACLE保留字) Setting 性别 SEX Sex 单 SINGLE Single 拆分 SPLIT Split 职员 STAFF Staff 人员 STAFF Staff 状态 STAT Status 股票 STOCK Stock 机构 STRU Structure 提交 SUBMIT Submit 成功 SUCC Success 服务 SVC Service 系统 SYS System 税 TAX Tax 时间 TIME Time 当日 TODAY Today 交易 TRANS Transaction 转账 TRNSF Transfer 学费 TUIT Tuition 类别 TYP Type 类型 TYP Type 联合 UNION ( ORACLE保留字) Union 单位 UNIT Unit 可用 USABLE Usable 用户 USER ( ORACLE保留字) User 客户 USER ( ORACLE保留字) User 值 VALUE Value 凭证 VCHR Voucher 总量 VOLUME Volume 年 YEAR Year 地区号 ZONE Zone 地区 ZONE Zone 7.7、典型案例 本部分的内容将作为开发中心的一种技术积累，根据开发人员的反馈不断地进行补充完善，从而对典型案例的开发起到规范化的作用，以避免重复劳动，提高开发效率。 下面举出第一个案例:分页问题 分页问题 分页的主要目的是控制对资源的过度访问。分下面几种情况分别进行处理: , 如果待查询的结果集很小，那么建议使用游标来进行获取，或者从 应用层予以控制; , 如果待查询的结果集很大，在分页显示时，每次都去查数据库，对 数据库会造成较大压力，可以考虑一次查询多页放到应用层中，这样既可以减少对数据库的访问，又不会加大应用服务器的压力。可以使用下面的语句来控制获取的记录值: 816以上版本: SELECT a from (SELECT a,row_number() OVER(ORDER BY a) rnum FROM test) WHERE rnum > a_min_value AND rnum < a_max_value; 815以下的8i版本: SELECT a FROM (SELECT rownum rnum,a FROM (SELECT a FROM test ORDER BY a) WHERE rownum < a_max_value) WHERE rnum > a_min_value; 例子: SQL> select empno from emp; EMPNO ---------- 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 14 rows selected. SQL> select empno from 2 ( select empno,row_number() over(order by empno) rnum 3 from emp) 4 where rnum > 0 5 and rnum <= 4; EMPNO ---------- 7369 7499 7521 7566 SQL> select empno from 2 ( select empno,row_number() over(order by empno) rnum 3 from emp) 4 where rnum > 4 5 and rnum <= 8; EMPNO ---------- 7654 7698 7782 7788 8、索引 8.1、索引种类介绍 索引在各种关系型数据库系统中都是举足轻重的组成部分，其对于提高检索数据的速度起至关重要的作用。在Oracle中，索引基本分为以下几种: , B*Tree索引， , 反向索引 , 降序索引 , 位图索引 , 函数索引 首先给出各种索引的简要解释: b*tree index:几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rid快速定位所访问的行。 反向索引:反转了b*tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争。 降序索引:8i中新出现的索引类型，针对逆向排序的查询。 位图索引:使用位图来管理与数据行的对应关系，多用于OLAP系统。 函数索引:这种索引中保存了数据列基于function返回的值，在select * from table where function(column)=value这种类型的语句中起作用。 8.2 各种索引的结构分析 8.2.1 B*Tree索引 B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列(高基数数据列是指该列有很多不同的值)时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块(branch block)和叶块(leaf block)组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列(关键字)范围和另一索引块的地址。 假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75,100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如where column >20 and column <80，那么会先定位到第一个包含20的叶块，然后横向查找其 他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。 8.2.2 反向索引 反向索引是B*Tree索引的一个分支，它的设计是为了运用在某些特定的环境下的。Oracle推出它的主要目的就是为了降低在并行服务器(Oracle Parallel Server)环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节，通过dump()函数我们可以清楚得看见它做了什么。举个例子:1，2，3三个连续的数，用dump()函数看它们在Oracle内部的表示方法。 SQL> select 'number',dump(1,16) from dual 2 union all select 'number',dump(2,16) from dual 3 union all select 'number',dump(3,16) from dual; 'NUMBE DUMP(1,16) ------ ----------------- number Typ=2 Len=2: c1,2 (1) number Typ=2 Len=2: c1,3 (2) number Typ=2 Len=2: c1,4 (3) 再对比一下反向以后的情况: SQL> select 'number',dump(reverse(1),16) from dual 2 union all select 'number',dump(reverse(2),16) from dual 3 union all select 'number',dump(reverse(3),16) from dual; 'NUMBE DUMP(REVERSE(1),1 ------ ----------------- number Typ=2 Len=2: 2,c1 (1) number Typ=2 Len=2: 3,c1 (2) number Typ=2 Len=2: 4,c1 (3) 我们发现索引码的结构整个颠倒过来了，这样1，2，3个索引码基本上不会出现在同一个叶块里，所以减少了争用。不过反向索引又一个缺点就是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用，例如，where column>value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。 8.2.3 降序索引 降序索引是8i里面新出现的一种索引，是B*Tree的另一个衍生物,它的变化就是列 在索引中的储存方式从升序变成了降序，在某些场合下降序索引将会起作用。举个例子，我 们来查询一张表并进行排序: SQL> select * from test where a between 1 and 100 order by a desc，b asc; 已选择100行。 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400) 1 0 SORT(ORDER BY)(Cost=2 Card=100 Bytes=400) 2 1 INDEX (RANGE SCAN) OF 'IND_BT' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400) 这里优化器首先选择了一个索引范围扫描,然后还有一个排序的步骤。如果使用了 降序索引，排序的过程会被取消。 SQL> create index test.ind_desc on test.testrev(a desc，b asc); 索引已创建。 SQL> analyze index test.ind_desc compute statistics; 索引已分析 再来看下执行路径: SQL> select * from test where a between 1 and 100 order by a desc，b asc; 已选择100行。 Execution Plan(SQL执行计划，稍后会讲解如何使用)。 ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400) 1 0 INDEX (RANGE SCAN) OF 'IND_DESC' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400) 我们看到排序过程消失了，这是因为创建降序索引时Oracle已经把数据都按降序 排好了。另外一个需要注意的地方是要设置init.ora里面的compatible参数为8.1.0或以上， 否则创建时desc关键字将被忽略。 8.2.4 位图索引 位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于 低cardinality列(即列的唯一值除以行数为一个很小的值，接近零)，例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。 位图索引可以是简单的(单列)也可以是连接的(多列)，但在实践中绝大多数是简单的。在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。 如果搜索where gender=’Male’,要统计性别是”Male”的列行数的话，Oracle很快就能从位图中找到共3行即第1，9，10行是符合条件的;如果要搜索where gender=’Male’ or gender=’Female’的列的行数的话，也很容易从位图中找到共8行即1，2，3，4，7，8，9，10行是符合条件的。如果要搜索表的值的话，那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。 8.2.5 函数索引 基于函数的索引也是8i以来的新产物，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件: (1)必须拥有QUERY REWRITE(本模式下)或GLOBAL QUERY REWRITE(其他模式下)权限。 (2)必须使用基于成本的优化器，基于规则的优化器将被忽略。 (3)必须设置以下两个系统参数: QUERY_REWRITE_ENABLED=TRUE QUERY_REWRITE_INTEGRITY=TRUSTED 可以通过alter system set,alter session set在系统级或线程级设置，也可以通过在init.ora添加实现。这里举一个基于函数的索引的例子: SQL> create index test.ind_fun on test.testindex(upper(a)); 索引已创建。 SQL> insert into testindex values('a',2); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select /**//*+ RULE*/* FROM test.testindex where upper(a)='A'; A B -- ---------- a 2 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (优化器选择了全表扫描) -------------------------------------------------------------------- SQL> select * FROM test.testindex where upper(a)='A'; A B -- ---------- a 2 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card= 1 Bytes=5) 2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car d=1)(使用了ind_fun索引) 3 各种索引的创建方法 (1)*Tree索引。 Create index indexname on tablename(columnname[columnname...]) (2)反向索引。 Create index indexname on tablename(columnname[columnname...]) reverse (3)降序索引。 Create index indexname on tablename(columnname DESC[columnname...]) (4)位图索引。 Create BITMAP index indexname on tablename(columnname[columnname...]) (5)函数索引。 Create index indexname on tablename(functionname(columnname)) 注意:创建索引后分析要索引才能起作用。 analyze index indexname compute statistics; 8.3 各种索引使用场合及建议 (1)B*Tree索引。 常规索引，多用于oltp系统，快速定位行，应建立于高cardinality列(即列的唯一 值除以行数为一个很大的值，存在很少的相同值)。 (2)反向索引。 B*Tree的衍生产物，应用于特殊场合，在ops环境加序列增加的列上建立，不适合做区域扫描。 (3)降序索引。 B*Tree的衍生产物，应用于有降序排列的搜索语句中，索引中储存了降序排列的索引码，提供了快速的降序搜索。 (4)位图索引。 位图方式管理的索引，适用于OLAP(在线分析)和DSS(决策处理)系统，应建立于低cardinality列，适合集中读取，不适合插入和修改，提供比B*Tree索引更节省的空间。 (5)函数索引。 B*Tree的衍生产物，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。 8.4 索引跟踪引用(性能优化监测) 首先要声明两个知识点: (1)RBO&CBO。 Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的;另一种是CBO(Cost Based Optimizer)基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引没有进行分析，Oracle将会使用RBO代替CBO;如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的方向，自8i版本来已经逐渐取代RBO. (2)AUTOTRACE。 要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE: ? 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请运行utlxplan.sql脚本(它 在$ORACLE_HOME/rdbms/admin目录中)。 ? AUTOTRACE可以通过运行plustrce.sql脚本(它在 $ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行plustrce.sql后会 建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可 以使用AUTOTRACE功能了。 ? AUTOTRACE的默认使用方法是set autotrace on，但是这方法不总是适合各种 场合，特别当返回行数很多的时候。Set autotrace traceonly提供了只查看统计信息而不查询 数据的功能。 SQL> set autotrace on SQL> select * from test; A ---------- 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'TEST' Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) rows processed SQL> set autotrace traceonly SQL> select * from test.test; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'TEST' Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) rows processed Hints是Oracle提供的一个辅助用法，按字面理解就是‘提示’的意思，确实它起 得作用也是提示优化器按它所提供的关键字来选择执行路径，特别适用于sql调整的时候。 使用方法如下: {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */ 具体可参考Oracle SQL Reference。有了前面这些知识点，接下来让我们来看一下 什么时候索引是不起作用的。以下列出几种情况。 (1)类型不匹配时。 SQL> create table test.testindex (a varchar(2),b number); 表已创建。 SQL> create index ind_cola on test.testindex(a); 索引已创建。 SQL> insert into test.testindex values('1',1); 已创建 1 行。 SQL> commit; 提交完成。 SQL> analyze table test.testindex compute statistics for all indexes; 表已分析。 SQL> set autotrace on; SQL> select /**//*+RULE */* FROM test.testindex where a='1';(使用基于rule的优化 器，数据类型匹配的情况下) A B -- ---------- 1 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' 2 1 INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE)(使用了索引 ind_cola) ―――――――――――――――――――――――――――――――――― SQL> select /**//*+RULE */* FROM test.testindex where a=1;(数据类型不匹配的情 况) A B -- ---------- 1 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'(优化器选择了全表扫描) (2)条件列包含函数但没有创建函数索引。 SQL> select /**//*+ RULE */* FROM test.testindex where upper(a)= 'A';(使用了函数 upper()在列a上); A B -- ---------- a 2 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'(优化器选择全表扫描) ---------------------------------------------------------- 创建基于函数的索引 SQL> create index test.ind_fun on test.testindex(upper(a)); 索引已创建。 SQL> insert into testindex values('a',2); 已创建1行。 SQL> commit; 提交完成。 SQL> select /**//*+ RULE*/* FROM test.testindex where upper(a)='A'; A B -- ---------- a 2 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (在RULE优化器下忽略了函数索引选择了全表扫描) ----------------------------------------------------------- SQL> select * FROM test.testindex where upper(a) ='A'; A B -- ---------- a 2 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card= 1 Bytes=5) 2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car d=1)(CBO优化器使用了ind_fun索引) (3)复合索引中的前导列没有被作为查询条件。 创建一个复合索引 SQL> create index ind_com on test.testindex(a,b); 索引已创建。 SQL> select /**//*+ RULE*/* from test.testindex where a='1'; A B -- ---------- 1 2 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE)(条件列表包含前导列 时使用索引ind_com) SQL> select /**//*+ RULE*/* from test.testindex where b=1; 未选定行 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'(条件列表不包括前导列是选择全表 扫描) ----------------------------------------------------------- (4)CBO模式下选择的行数比例过大，优化器采取了全表扫描。 SQL> select * from test.testindex where a='1'; A B -- ---------- 1 2 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5) 1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=1 Bytes=5) (表一共2行，选择比例为50%,所以优化器选择了全表扫描) ―――――――――――――――――――――――――――――――――― 下面增加表行数 SQL> declare i number; 2 begin 3 for i in 1 .. 100 loop 4 insert into test.testindex values (to_char(i),i); 5 end loop; 6 end; 7 / PL/SQL 过程已成功完成。 SQL> commit; 提交完成。 SQL> select count(*) from test.testindex; COUNT(*) ---------- 102 SQL> select * from test.testindex where a='1'; A B ---- ---------- 1 1 1 2 Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5) 1 0 INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5) (表一共102行，选择比例为2/102=2%,所以优化器选择了索引扫描) (5)CBO模式下表很久没分析，表的增长明显，优化器采取了全表扫描。 SQL> select * from test.testindex where a like '1%'; A B ---- ---------- 1 2 1 1 10 10 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 100 100 已选择13行。 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52) 1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52) (表一共102行，选择比例为13/102>10%,优化器选择了全表扫描) ―――――――――――――――――――――――――――――――――― 增加表行数 SQL> declare i number; 2 begin 3 for i in 200 .. 1000 loop 4 insert into test.testindex values (to_char(i),i); 5 end loop; 6 end; 7 / PL/SQL 过程已成功完成。 SQL> commit; 提交完成。 SQL> select count(*) from test.testindex; COUNT(*) ---------- 903 SQL> select * from test.testindex where a like '1%'; A B ---- ---------- 1 2 1 1 10 10 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 100 100 1000 1000 已选择14行。 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52) 1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52) (表一共903行，选择比例为14/903<5%,优化器选择了全表扫描,选择路径是错误的) ――――――――――――――――――――――――――――― 给表做分析 SQL> analyze table test.testindex compute statistics for table for all indexed c olumns for all indexes; 表已分析。 SQL> select * from test.testindex where a like '1%'; A B ---- ---------- 1 2 1 1 10 10 100 100 1000 1000 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 已选择14行。 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=24 Bytes=120) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=4 Card= 24 Bytes=120) 2 1 INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE) (Cost=2 Ca rd=24) (经过分析后优化器选择了正确的路径，使用了ind_cola索引) 8.6、常见Hints技术 1. /*+ ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+ FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化. : 例如 SELECT /*+ FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 3. /*+ CHOOSE*/ 表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐 量; 表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法; 例如: SELECT /*+ CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 4. /*+ RULE*/ 表明对语句块选择基于规则的优化方法. 例如: SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 5. /*+ FULL(TABLE)*/ 表明对表选择全局扫描的方法. 例如: SELECT /*+ FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT'; 6. /*+ ROWID(TABLE)*/ 提示明确表明对指定表根据ROWID进行访问. 例如: SELECT /*+ ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA' AND EMP_NO='SCOTT'; 7. /*+ CLUSTER(TABLE)*/ 提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效. : 例如 SELECT /*+ CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; 8. /*+ INDEX(TABLE INDEX_NAME)*/ 表明对表选择索引的扫描方法. 例如: SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M'; 9. /*+ INDEX_ASC(TABLE INDEX_NAME)*/ 表明对表选择索引升序的扫描方法. 例如: SELECT /*+ INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT'; 10. /*+ INDEX_DESC(TABLE INDEX_NAME)*/ 表明对表选择索引降序的扫描方法. : 例如 SELECT /*+ INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT'; 8.7、使用原则 1. 逻辑主键应使用唯一的成组索引，对任何外键列应采用非成组索引。 2. 在查询显示主表和所有关联表的某条记录时，应使用索引外键。 3. 不要索引大型字符字段。 4. 不要索引常用的小型表。 5. 当查询的行数占整个表总行数的比例<=5%时，应该建立b*树索引。 6. 应该在频繁进行排序或分组(即进行group By或order By操作)的列上建 立索引。 7. 应该在频繁使用distinct关键字进行查询的列上建立索引。 8. 进行表连接时，应该在连接字段上面建立索引。 9. 对于键值频繁更新的索引，应该定期的进行重建。 10. 当对表中的数据量扫描超过10%~20%时，应该避免使用索引。 11. 复合索引创建时应该把最常用的列放在第一位而不太常用的列放在稍后面的 位置。 12. 在OLTP系统中，尽量不要使用函数索引。 13. 索引字段的记录重复较多，而distinct记录的值又较少(一般少于300)时应 该建立位图索引。 14. 不建议对OLTP类应用的表建立超过8个索引。 15. 在对数据进行大的统计分析操作时，应该使用HINT技术通过指定全表扫描 来避免使用索引。 9、分区(参见Oracle数据库高级技术交流计划-大批量数据处理技术.ppt) 在大型的企业应用或企业级的数据库应用中，要处理的数据量通常可以达到几十到几百GB，有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快，但是仍然不能满足用户的需求，为了使用户的大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，以改善大型应用系统的性能。 使用分区的优点: ?增强可用性:如果表的某个分区出现故障，表在其他分区的数据仍然可用; ?维护方便:如果表的某个分区出现故障，需要修复数据，只修复该分区即可; ?均衡I/O:可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能; ?改善查询性能:对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 Oracle数据库提供对表或索引的分区方法有常用四种: , 范围分区 , Hash分区(散列分区) , 复合分区 , 列表分区 下面将以实例的方式分别对这三种分区方法来说明分区表的使用。为了测试方便，我们先建三个表空间。 create tablespace dinya_space01 datafile ?/test/demo/oracle/demodata/dinya01.dnf? size 50M create tablespace dinya_space01 datafile ?/test/demo/oracle/demodata/dinya02.dnf? size 50M create tablespace dinya_space01 datafile ?/test/demo/oracle/demodata/dinya03.dnf? size 50M 9.1. 分区表的创建 9.1.1. 范围分区 范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。 需求描述:有一个物料交易表，表名:material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这时候我们可以使用序号分区三个区，每个区中预计存储三千万的数据，也可以使用日期分区，如每五年的数据存储在一个分区上。 根据交易记录的序号分区建表: SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number(8) not null, 5 item_description varchar2(300), 6 transaction_date date not null 7 ) 8 partition by range (transaction_id) 9 ( 10 partition part_01 values less than(30000000) tablespace dinya_space01, 11 partition part_02 values less than(60000000) tablespace dinya_space02, 12 partition part_03 values less than(maxvalue) tablespace dinya_space03 13 ); Table created. 建表成功，根据交易的序号，交易ID在三千万以下的记录将存储在第一个表空间dinya_space01中，分区名为:par_01，在三千万到六千万之间的记录存储在第二个表空间: dinya_space02中，分区名为:par_02，而交易ID在六千万以上的记录存储在第三个表空间dinya_space03中，分区名为par_03. 根据交易日期分区建表: SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number(8) not null, 5 item_description varchar2(300), 6 transaction_date date not null 7 ) 8 partition by range (transaction_date) 9 ( 10 partition part_01 values less than(to_date(?2006-01-01?,?yyyy-mm-dd?)) tablespace dinya_space01, 11 partition part_02 values less than(to_date(?2010-01-01?,?yyyy-mm-dd?)) tablespace dinya_space02, 12 partition part_03 values less than(maxvalue) tablespace dinya_space03 13 ); Table created. 这样我们就分别建了以交易序号和交易日期来分区的分区表。每次插入数据的时候，系统将根据指定的字段的值来自动将记录存储到制定的分区(表空间)中。 当然，我们还可以根据需求，使用两个字段的范围分布来分区，如partition by range ( transaction_id ,transaction_date), 分区条件中的值也做相应的改变，请读者自行测试。 9.1.2. Hash分区(散列分区) 散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中: SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number(8) not null, 5 item_description varchar2(300), 6 transaction_date date 7 ) 8 partition by hash(transaction_id) 9 ( 10 partition part_01 tablespace dinya_space01, 11 partition part_02 tablespace dinya_space02, 12 partition part_03 tablespace dinya_space03 13 ); Table created. 建表成功，此时插入数据，系统将按transaction_id将记录散列地插入三个分区中，这里也就是三个不同的表空间中。 9.1.3. 复合分区 有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法，如将物料交易的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中: SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number(8) not null, 5 item_description varchar2(300), 6 transaction_date date 7 ) 8 partition by range(transaction_date)subpartition by hash(transaction_id) 9 subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) 10 ( 11 partition part_01 values less than(to_date(?2006-01-01?,?yyyy-mm-dd?)), 12 partition part_02 values less than(to_date(?2010-01-01?,?yyyy-mm-dd?)), 13 partition part_03 values less than(maxvalue) 14 ); Table created. 该例中，先是根据交易日期进行范围分区，然后根据交易的ID将记录散列地存储在三 个表空间中。 9.1.4列表分区 create table addresses (... column definitions ...) pctfree 0 nologging storage ( initial 40m next 40m pctincrease 0 ) partition by list(city_name) (partition addr_p01 values ('WELLINGTON') tablespace addr_data01 ,partition addr_p02 values ('CHRISTCHURCH') tablespace addr_data02 ,partition addr_p03 values ('DUNEDIN','INVERCARGILL') tablespace addr_data03 ,partition addr_p04 values ('AUCKLAND') tablespace addr_data04 ,partition addr_p05 values ('HAMILTON','ROTORUA','TAURANGA') tablespace addr_data05) 9.2. 分区表操作 以上了解了四种分区表的建表方法，下面将使用实际的数据并针对按日期的范围分 区来测试分区表的数据记录的操作。 9.2.1. 插入记录: SQL> insert into dinya_test values(1,12,?BOOKS?,sysdate); 1 row created. SQL> insert into dinya_test values(2,12, ?BOOKS?,sysdate+30); 1 row created. SQL> insert into dinya_test values(3,12, ?BOOKS?,to_date(?2006-05-30?,?yyyy-mm-dd?)); 1 row created. SQL> insert into dinya_test values(4,12, ?BOOKS?,to_date(?2007-06-23?,?yyyy-mm-dd?)); 1 row created. SQL> insert into dinya_test values(5,12, ?BOOKS?,to_date(?2011-02-26?,?yyyy-mm-dd?)); 1 row created. SQL> insert into dinya_test values(6,12, ?BOOKS?,to_date(?2011-04-30?,?yyyy-mm-dd?)); 1 row created. SQL> commit; Commit complete. SQL> 按上面的建表结果，2006年前的数据将存储在第一个分区part_01上，而2006年 到2010年的交易数据将存储在第二个分区part_02上，2010年以后的记录存储在第三个分 区part_03上。 9.2.2. 查询分区表记录: SQL> select * from dinya_test partition(part_01); TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE -------------------------------------------------------------------------------- 1 12 BOOKS 2005-1-14 14:19: 2 12 BOOKS 2005-2-13 14:19: SQL> SQL> select * from dinya_test partition(part_02); TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE -------------------------------------------------------------------------------- 3 12 BOOKS 2006-5-30 4 12 BOOKS 2007-6-23 SQL> SQL> select * from dinya_test partition(part_03); TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE -------------------------------------------------------------------------------- 5 12 BOOKS 2011-2-26 6 12 BOOKS 2011-4-30 SQL> 从查询的结果可以看出，插入的数据已经根据交易时间范围存储在不同的分区中。 这里是指定了分区的查询，当然也可以不指定分区，直接执行select * from dinya_test查询 全部记录。 在也检索的数据量很大的时候，指定分区会大大提高检索速度。 9.2.3. 更新分区表的记录: SQL> update dinya_test partition(part_01) t set t.item_description=?DESK? where t.transaction_id=1; 1 row updated. SQL> commit; Commit complete. SQL> 这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”，可以看到已经成功更新了一条记录。但是当更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会更新数据，请看下面的例子: SQL> update dinya_test partition(part_01) t set t.item_description=?DESK? where t.transaction_id=6; 0 rows updated. SQL> commit; Commit complete. SQL> 指定了在第一个分区中更新记录，但是条件中限制交易ID为6，而查询全表，交易ID为6的记录在第三个分区中，这样该条语句将不会更新记录。 9.2.4. 删除分区表记录: SQL> delete from dinya_test partition(part_02) t where t.transaction_id=4; 1 row deleted. SQL> commit; Commit complete. SQL> 上面例子删除了第二个分区part_02中的交易记录ID为4的一条记录，和更新数据相同，如果指定了分区，而条件中的数据又不在该分区中时，将不会删除任何数据。 9.3. 分区表索引的使用: 分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区 中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。 9.3.1. 局部索引分区的建立: SQL> create index dinya_idx_t on dinya_test(item_id) 2 local 3 ( 4 partition idx_1 tablespace dinya_space01, 5 partition idx_2 tablespace dinya_space02, 6 partition idx_3 tablespace dinya_space03 7 ); Index created. SQL> 看查询的执行计划，从下面的执行计划可以看出，系统已经使用了索引: SQL> select * from dinya_test partition(part_01) t where t.item_id=12; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=187) 1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF ?DINYA_TEST? (Cost= 2 Card=1 Bytes=187) 2 1 INDEX (RANGE SCAN) OF ?DINYA_IDX_T? (NON-UNIQUE) (Cost=1 Card=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 334 bytes sent via SQL*Net to client 309 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed SQL> 9.3.2. 全局索引分区的建立 全局索引建立时global 子句允许指定索引的范围值，这个范围值为索引字段的范 围值: SQL> create index dinya_idx_t on dinya_test(item_id) 2 global partition by range(item_id) 3 ( 4 partition idx_1 values less than (1000) tablespace dinya_space01, 5 partition idx_2 values less than (10000) tablespace dinya_space02, 6 partition idx_3 values less than (maxvalue) tablespace dinya_space03 7 ); Index created. SQL> 本例中对表的item_id字段建立索引分区，当然也可以不指定索引分区名直接对整 个表建立索引，如: SQL> create index dinya_idx_t on dinya_test(item_id); Index created. SQL> 同样的，对全局索引根据执行计划可以看出索引已经可以使用: SQL> select * from dinya_test t where t.item_id=12; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=561) 1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF ?DINYA_TEST? (Cost =2 Card=3 Bytes=561) 2 1 INDEX (RANGE SCAN) OF ?DINYA_IDX_T? (NON-UNIQUE) (Cost=1 Card=3) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 420 bytes sent via SQL*Net to client 309 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 5 rows processed SQL> 9.4. 分区表的维护: 了解了分区表的建立、索引的建立、表和索引的使用后，在应用的还要经常对分区进行维护和管理。日常维护和管理的内容包括:增加一个分区，合并一个分区及删除分区等等。下面以范围分区为例说明增加、合并、删除分区的一般操作: 9.4.1. 增加一个分区: SQL> alter table dinya_test 2 add partition part_04 values less than(to_date(?2012-01-01?,?yyyy-mm-dd?)) tablespace dinya_spa ce03; Table altered. SQL> 增加一个分区的时候，增加的分区的条件必须大于现有分区的最大值，否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。 9.4.2. 合并一个分区: SQL> alter table dinya_test merge partitions part_01,part_02 into partition part_02; Table altered. SQL> 在本例中将原有的表的part_01分区和part_02分区进行了合并，合并后的分区为part_02,如果在合并的时候把合并后的分区定为part_01的时候，系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。 9.4.3. 删除分区: SQL> alter table dinya_test drop partition part_01; Table altered. SQL> 删除分区表的一个分区后，查询该表的数据时显示，该分区中的数据已全部丢失，所以执行删除分区动作时要慎重，确保先备份数据后再执行，或将分区合并。 10、物化视图 Oracle的物化视图提供了强大的功能，可以用在不同的环境中。在不同的环境中，物化视图的作用也不相同。 , 数据仓库中的物化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作 的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到 结果。在数据仓库中，还经常使用查询重写(query rewrite)机制，这样不需要修 改原有的查询语句，Oracle会自动选择合适的物化视图进行查询，完全对应用透明。 , 物化视图和表一样可以直接进行查询。物化视图可以基于分区表，物化视图本身也可以 分区。 , 除了在数据仓库中使用，物化视图还用于复制、移动计算等方面。 , 物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能;物化视 图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性; 物化视图需要占用存储空间;当基表发生变化时，物化视图也应当刷新。 10.1、物化视图分类 物化视图可以分为以下三种类型: 1. 包含聚集的物化视图; 2. 只包含连接的物化视图; 3. 嵌套物化视图。 三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。 创建物化视图时可以指定多种选项，下面对几种主要的选择进行简单说明: 10.2、创建方式(Build Methods) 包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据，而BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE。 10.3、查询重写(Query Rewrite) 包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。 10.4、刷新(Refresh) 指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种:ON DEMAND和ON COMMIT。ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。ON COMMIT指出物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种:FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCE ON DEMAND。 在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。 10.5、物化视图日志 如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。 可以指明ON PREBUILD TABLE语句将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。 10.6、物化视图分区 物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪(PCT)。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。 对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。 10.7、物化视图系统参数 , job_queue_processes 修改系统参数,在init.ora里面修改则每次都有效 alter system set job_queue_processes=20; 如果这个参数为0,物化视图是不会刷新的 , query_rewrite_enabled 需要调整初始参数 query_rewrite_enabled，该参数可以动态调整，不需要重启 , query_rewrite_integrity query_rewrite_integrity 该参数值有三个enforced、trusted、stale_tolerated 调整查询重写级别 10.8、物化视图例子 在oracle10g 下创建一个简单的物化视图(materialized view)的例子 --********************************************************************** --物化视图 --0.cmd 修改系统参数,在init.ora里面修改则每次都有效 alter system set job_queue_processes=20; 如果这个参数为0,物化视图是不会刷新的 --1.建表,必须有索引 create table stu ( id varchar2(10) primary key,// name varchar2(20) ) //不是主键就需要建索引CREATE INDEX INDEX_STU ON STU(ID) --2.建日志 CREATE MATERIALIZED VIEW LOG ON STU; --3.建视图 create MATERIALIZED VIEW STU_VIEW REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/1440 WITH PRIMARY KEY AS SELECT * FROM STU --4.插入数据 INSERT INTO STU(ID,NAME) VALUES('56','555555555555'); COMMIT; --5.查看视图数据变化 SELECT * FROM oemcyd.STU_VIEW; --6.查询物化视图刷新时间 SELECT MM.mview_name,MM.last_refresh_date FROM DBA_MVIEWS MM 11、数据导入导出工具 本章的学习目标: , 熟悉SQL*Loader的使用方法 , 熟悉数据导出导入工具Export/Import 11.1 SQL*Loader 使用SQL*Loader可以把外部的文本文件导入到数据库的表中。当用户进行大量导入时，可以选择此工具。它可以加载多个表，且文件格式不必规则，还可以指定任何分隔符结束。 d:\sqlldr help=y 用SQL*Loader加载数据通常有两种方法，即一般装入法和直接装入法，一般装入法(Conventional Path)为Oracle默认方式。它适用于较小数据的导入。直接装入法(Direct-Path)加载数据时，系统将绕过数据库高速缓存，直接写数据块到数据文件，设置参数Direct可以使用此项功能。加载速度要快于一般装入法，它适合于大量数据快速装入。直接装入法可以通过指定参数Direct=Y来实现并行操作。 sqlload test/test control=cdrfee.ctl log=cdrfee.log bad=cdrfee.bad rows=100000 errors=9999999 direct=true 以下为控制文件cdrfee.ctl内容: load data infile load_tollfee_93.txt append into table tollfee_93 fields terminated by ',' trailing nullc ols ( ticket_id, tariff_id, time_span_id, ccount, fee ) 11.2 数据导入与导出 在日常管理过程中，经常要把数据从一个数据库移到另一个数据库，或者从一个用户移到另一个，这种情况下将考虑使用导入与导出工具。 11.2.1 用EXPORT导出数据 本小节中EXPORT工具将导出Oracle表中的数据到外部文件，此文件为二进制格式，默认名为EXPDAT.DMP，并且只有IMPORT工具可以读取。在一个工作平台上导出的文件可以在不同的平台上进行数据导入。 数据导出可以有以下几种方式: , 表模式 , 用户模式 , 数据库模式 , 表空间模式 DIRECT=Y参数用于指定直接输出法，此方法之所以快速是因为它读取数据文件到数据库高速缓存而不经过SQL语句处理，用户也可以指定其他参数。 11.2.2 用IMPORT导入数据 IMPORT工具将读取EXPORT工具导出的文件，并执行插入操作。可以选择导入模式为数据库，表或用户操作大致与EXPORT工具相同。IMP HELP=Y可以看到在线帮助。 另外有一点需要介绍的是IMPORT工具对数据的重组功能，使用EXPORT和IMPORT工具对表进行重组以消除行迁移，主要是使用了COMPRESS选项对导出导入扩展区进行了压缩。下面将分步加以介绍: (1) 制定导出计划文件。 FILE=EMPLOYEES.DMP TABLES=system.t_emp COMPRESS=Y DIRECR=Y (2) 执行数据导出 EXP syetem/manager PARFILE=EMPLOYEES.PAR (3) 将原表删除。 DROP TABLE t_emp; (4) 生成导入参数文件。 FILE=EMPLOYEES.DMP FROMUSER=system TOUSER=manager (5) 导入数据。 IMP system/manager PARFILE=EMPLOYEES.JAR 12、数据库优化及使用技巧 12.1、oracle性能调整的十大要点 参见《oracle性能调整的十大要点》 12.2、几个简单的步骤大幅提高Oracle性能 参见《几个简单的步骤大幅提高Oracle性能》 13、数据仓库 参见《数据仓库》目录

本文档为【农信银行培训教材】，请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑， 图片更改请在作品中右键图片并更换，文字修改请直接点击文字进行修改，也可以新增和删除文档中的内容。
该文档来自用户分享，如有侵权行为请发邮件ishare@vip.sina.com联系网站客服，我们会及时删除。
[版权声明] 本站所有资料为用户分享产生，若发现您的权利被侵害，请联系客服邮件isharekefu@iask.cn，我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权，请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传，仅限个人学习分享使用，禁止用于任何广告和商用目的。

下载需要： 免费 已有0 人下载

立即下载

你可能还喜欢

• 《我们生活的世界》教学反思
• 葵花宝典之塑胶心得
• 小古文阅读训练题(附答案)汇编
• 2021年Tekla门式钢架建模主要节点选用及设置
• 设计跟踪检查记录单格式
• 法发〔2010〕60号 关于处理自首和立功若干具体问题的意见
• 寺院鼓在寺院中的作用
• 《缇萦救父》阅读答案及原文翻译
• 新乡市延津县大平调历史沿革与传承发展.doc
• 全国文明城市测评体系 版
• 高铁站雨棚养护和维修技术研究
• atos比例阀手册
• 积极发展中国旅游保险
• 新飞飞任务大全
• 冷链物流公司广告语
• 2018年南京农业大学信息科学技术学院853计算机专业基础综合之计算机网络考研基础五套测试题
• 科室护理人员培训工作计划
• 科室护理人员培训工作计划
• 科室护理人员培训工作计划
• 科室护理人员培训工作计划

最新资料

资料动态

专题动态

• 语言教学的流派-中文翻译
• 货币时间价值与财政计算器
• 质量跟踪管理制度
• 机关、单位车辆维修申请单-样表
• 2022年庆祝建国73周年PPT10月1日国庆节活动策划主题班会PPT课件（带内容）
• 计算机应用基础课件
• 做自己作文
• 《人民防空工程伪装防护技术规范》地方标准
• 人教版高中英语全部单词表word版
• 中外钢材牌号对照表
• 高中录取通知书格式
• 汽轮机、发电机吊装方案
• 曲靖高考研讨会心得体会
• 职业发展启示录——人生职业生涯规划，职业行业选择，专业选择，跳槽必看资料
• 语言教学的流派-中文翻译
• 货币时间价值与财政计算器
• 质量跟踪管理制度
• 机关、单位车辆维修申请单-样表
• 2022年庆祝建国73周年PPT10月1日国庆节活动策划主题班会PPT课件（带内容）
• 计算机应用基础课件
• 做自己作文
• 《人民防空工程伪装防护技术规范》地方标准
• 人教版高中英语全部单词表word版
• 中外钢材牌号对照表
• 高中录取通知书格式
• 汽轮机、发电机吊装方案
• 曲靖高考研讨会心得体会
• 职业发展启示录——人生职业生涯规划，职业行业选择，专业选择，跳槽必看资料

• [推荐]决赛辩词——未来社会，女性比男性-男性比女性更具有发展潜力 默认稍大普通较大很大最大,
• 论安提戈涅与克瑞翁之争
• 这个男孩以前活过
• 交管12123学法减分考试试题及答案
• 孙犁《父亲的记忆》阅读练习及答案
• 沈阳建筑大学土木专升本材料力学
• [知识]h 毛笔字钢笔字书写方法
• 某含铅金精矿中回收铅的试验研究_陈晓芳
• [曹轩宾][爱情回来了]歌词
• 爱的教育读后感44
• 年产xxx辆新能源汽车项目立项报告
• 作文指导《我也是一束阳光》ppt课件
• 转学保证书(范本)
• 2006工资套改年限计算
• 江苏省南京鼓楼区金陵汇文重点中学2023届初三3月第一次综合练习（一模）数学试题含解析
• 《8简易连杆装置》小学劳动技术沪科教版四年级上册课件
• 原问题与对偶问题PPT演示文稿
• 第三章勾股定理
• 论授权第三人综合规范
• 高压蒸汽灭菌效果监测记录表格模板
• 纪检监督责任落实情况报告

• 西方文明史01
• 变压器铁芯及夹件接地电流测试报告
• 为什么社工不能和服务对象做朋友？
• 血清白蛋白溴甲酚绿法测定
• 麻醉记录单范例 (13页)
• 梁孟伟《故乡》中考阅读练习及答案（2017年四川省绵阳市中考题）
• 剪力钉施工
• 部编版六年级上册语文教案-语文园地八
• 劳动合同变更协议范本
• 物流职业规划
• 浅析成人学习理论在企业培训中的重要性
• 选举公司董事、监事的股东会决议模板
• 市场BD各级KPI考核表
• JBT 7261-1994 铝制板翅式换热器 技术条件
• 西方文明史01
• 变压器铁芯及夹件接地电流测试报告
• 为什么社工不能和服务对象做朋友？
• 血清白蛋白溴甲酚绿法测定
• 麻醉记录单范例 (13页)
• 梁孟伟《故乡》中考阅读练习及答案（2017年四川省绵阳市中考题）
• 剪力钉施工
• 部编版六年级上册语文教案-语文园地八
• 劳动合同变更协议范本
• 物流职业规划
• 浅析成人学习理论在企业培训中的重要性
• 选举公司董事、监事的股东会决议模板
• 市场BD各级KPI考核表
• JBT 7261-1994 铝制板翅式换热器 技术条件

is_841159

暂无简介~

格式：doc

大小：248KB

软件：Word

页数：0

分类：

上传时间：2018-08-16

浏览量：8

相关资料

• 桃红四物汤加减治疗子宫切口假腔30例临床观察
• 国防科技重点实验室综合评估研究
• 自谐振频率
• 大华专用存储计算工具
• 遗传学名词解释(2)
• CPK与PPM换算对照表
• 中医病历
• 监理人员组织形式及监理人员进场计划表
• 固体废物回收处理服务方案(纸箱、金属、塑料)
• 工会成立申请报告模版
• Unit 10 You're supposed to shake hands.教案
• 小学生课外阅读计划执行表
• Mesh 医学主题词表
• 案例,刑事模拟法庭
• 汽车陪练注意事项[整理]
• 会计档案管理制度
• 资料长编
• 关于促进规模化畜禽养殖有关用地政策的通知(国土资发[2007] 220号)
• 驯服劣性老公彪悍蛇王好腹黑
• 5S管理5S纠正及预防措施

热点搜索

中国制造业民营企业平均寿命11.1年 .doc 天津市一年级下册数学期末测试题及答案精选文档 工会委员会职责 参观大邑县建川抗战博物馆活动方案 蔬菜农药残留检测登记表 财务管理的精髓 活塞式压缩机曲柄连杆机构受力计算软件的开发 32×32点阵LED模块显示屏设计_(软件加硬件原理图) 希望杯五年级历届试题与答案 铁路通信部分技术规格书 2022最新营业线施工安全考试试题库（附答案） 办公室消耗品一览表 六年级作文我真幸福作文600字 不同厂家磁芯材质对照表 中国制造业民营企业平均寿命11.1年 .doc 天津市一年级下册数学期末测试题及答案精选文档 工会委员会职责 参观大邑县建川抗战博物馆活动方案 蔬菜农药残留检测登记表 财务管理的精髓 活塞式压缩机曲柄连杆机构受力计算软件的开发 32×32点阵LED模块显示屏设计_(软件加硬件原理图) 希望杯五年级历届试题与答案 铁路通信部分技术规格书 2022最新营业线施工安全考试试题库（附答案） 办公室消耗品一览表 六年级作文我真幸福作文600字 不同厂家磁芯材质对照表

资料大全 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0-9

专题大全 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0-9

网站声明 | 侵权处理 | 投诉反馈 | 帮助中心 | 网站地图 | 爱问办公

京ICP证000007-6 爱问文库-Copyright © 2024 版权所有

浙公网安备 33021202002483

客服热线：0755-26904047

工作日：9:00-18:00

在线客服

• 

  关注爱问文库服务号