新致讲坛oracle

《新致讲坛oracle》由会员分享，可在线阅读，更多相关《新致讲坛oracle（154页珍藏版）》请在装配图网上搜索。

1、讲课题目 Oracle 精简教程部门:FBD3姓名:李俊华1讲师个人介绍2S.T.A.N.D.A.R.D.课程目标n 3S.T.A.N.D.A.R.D.议程n1 两大开发阵营及两大开发阵营及Oracle技术和认证考试简介技术和认证考试简介n2 Oracle10g体系结构体系结构(基础版基础版)n3 Oracle SQL编码规范编码规范n4 SQL调优基础（基于索引）调优基础（基于索引）n5 Oracle常用函数常用函数n/*6 Linux上的上的oracle安装安装*/4S.T.A.N.D.A.R.D.一、两大开发阵营及一、两大开发阵营及Oracle认证考试简介认证考试简介1.1 两大开发阵营

2、简述两大开发阵营简述 5S.T.A.N.D.A.R.D.6S.T.A.N.D.A.R.D.1.2 各主流数据库市场份额各主流数据库市场份额 Oracle:53%DB2:17%Microsoft SQL Server:13%MySql,Sybase,informax:27%7S.T.A.N.D.A.R.D.1.3 Oracle 认证考试简介认证考试简介:Oracle9i认证课程认证课程:Oracle 9I 007(Introduction to Oracle9i SQL)Oracle 9I 031(Oracle9i DBA Fundamentals 1)Oracle 9I 032(Oracle9

3、i DBA Fundamentals 2)Oracle 9I 033(Oracle9i DBA Performance Tunning)Oracle10g 认证课程认证课程:Oracle 10g(047):Introduction to SQL Oracle 10g(042):Oracle10g DBA Administrator 1 Oracle 10g(043):Oracle10g DBA Administrator 28S.T.A.N.D.A.R.D.Oracle DBA认证考试级别有认证考试级别有3种：种：Oracle Certified Associate(OCA):Oracle认证

4、技术员认证技术员 9i(007,031),10g(047,042)Oracle Certified Professional(OCP):Oracle认证专业人员认证专业人员 9i OCA+(032,033),10g OCA+(043)+一门原厂培训一门原厂培训 Oracle Certified Master(OCM):Oracle认证专家认证专家 OCP+二门高级原厂培训二门高级原厂培训+二天二天OCM实践考试通过实践考试通过9S.T.A.N.D.A.R.D.OCM考试内容：考试内容：1.Creaet database by manual 2.Backup and RecoveryRman,F

5、lashback 3.Dataware House(patition,paraller,sql loader,Materialized View,bitmap)4.Performace Mangement 5.Real Application Clusters（RAC）6.Data Guard 7.Enterprise Manager Grid Control 10S.T.A.N.D.A.R.D.二、二、Oracle 10g 体系结构体系结构 11S.T.A.N.D.A.R.D.12S.T.A.N.D.A.R.D.数据库（数据库（database）：）：物理操作系统文件或磁盘（disk）的集合

6、。实例（实例（instance）：）：一组Oracle后台进程/线程以及一个共享内存区，这些内存由同一个计算机上运行的线程/进程所 共享。这两个词有时可互换使用，不过二者的概念完全不同。实例和数据库之间的关系是：实例和数据库之间的关系是：13S.T.A.N.D.A.R.D.。实例实例就是一组操作系统进程（或者是一个多线程的进程）以及一些内存。这些进程可以操作数据库；而数据库数据库只是一个文件集合（包括数据文件、临时文件、重做日志文件和控制文件）。在任何时刻，一个实例只能有一组相关的文件（与一个数据库关 联）。大多数情况下，反过来也成立：一个数据库上只有一个实例对其进行操作。不过，Oracle的

7、真正应用集群（Real Application Clusters，RAC）是一个例外，这是Oracle提供的一个选项，允许在集群环境中的多台计算机上操作，这样就可以有多台实例同时装载并打开一个数据库（位于一组共享物理磁盘上）。由此，我们可以同时从多台不同的计算机访问这个数据库。Oracle RAC能支持高度可用的系统，可用于构建可扩缩性极好的解决方案。14S.T.A.N.D.A.R.D.实例是一组后台进程和共享内存。数据库是磁盘上存储的数据集合。实例“一生”只能装载并打开一个数据库。数据库可以由一个或多个实例（使用RAC）装载和打开。前面提到过，大多数情况下，实例和数据库之间存在一种一对一的关

8、系。如果有人谈到实例，他指的就是Oracle的进程和内存。提到数据库时，则是说保存数据的物理文件。可以从多个实例访问一个数据库，但是一个实例一次只能访问一个数据库。15S.T.A.N.D.A.R.D.16S.T.A.N.D.A.R.D.17S.T.A.N.D.A.R.D.18S.T.A.N.D.A.R.D.1.数据文件数据文件：用来记录用户数据的文件，也是消耗存储最大的部分。一个数据文件只能属于一个数据库，并且一个数据文件只能属于一个表空间，一个或者多个数据文件构成一个逻辑存储结构。2.控制文件控制文件：用来管理数据库的状态，是数据库中最重要的辅助文件。每个数据库至少有一个控制文件。一般记录着

9、数据库的名称，数据库的物理布局：包括数据文件的位置、联机日志文件的位置、备份文件，以及数据库当前的SCN等重要信息。3.日志文件日志文件：就像一笔“流水账”，它是按照时间顺序记录数据库内发生的所有改变，这些“流水账”对于数据恢复非常重要。包括联机日志文件和归档日志文件，前者是所有数据库都必须的，后者是“归档模式”的数据库才会产生。19S.T.A.N.D.A.R.D.4.联机日志联机日志(组组)：是以组为单位进行管理的，每一个组就叫做一个联机日志组。每个联机日志组可以包含多个成员，同一组里的每个成员的内容是相同的，其实就是多个成员互为镜像。每个数据库必须要有两个联机日志组，联机日志是以一种循环的

10、方式写入的。5.归档日志：归档日志：Oracle 根据是否启用了“归档”而把数据库分成两种模式:归档模式和非归档模式。所谓“归档”就是一组联机日志文件被写满后，在触发日志切换的同时，还要触发ARCn进程的工作，这个进程的工作就是把之前写满的联机日志做一份拷贝保存到另外一个位置，这份拷贝就叫做“归档日志”。20S.T.A.N.D.A.R.D.6.日志线程：日志线程：一个数据库实例使用的联机日志就叫做一个日志线程。Oracle实例和数据库之间可以有两种对应关系，如果“实例：数据库=1：1”，这种环境就是所谓的“单实例”。如果“实例：数据库=n:1(n=2)”,这种就是所谓的RAC。即便是在RAC环

11、境下，对每个日志线程的要求也是一样的。即每个实例或每个线程至少两个“联机日志组”，每个联机日志组又至少要有一个“日志成员”。7.参数文件参数文件(spfile和和pfile)：spfile是二进制文件，不能手工编辑。pfile是文本文件，可以手工编辑。用来存放数据库初始参数配置，比如SGA的大小，控制文件的位置等，每个数据库启动时都要读取两个文件中的一个，根据内容对实例进行初始化。21S.T.A.N.D.A.R.D.8.Alert日志文件日志文件:每一个数据库都需要一个Alert日志文件，位于参数文件BACKGROUP_DUMP_DEST指定的目录下，文件名的格式为alert_dbname.l

12、og。Oracle使用这个文件来记录数据库运行过程中发生的重大事件，比如启动、关闭、日志切换。22S.T.A.N.D.A.R.D.23S.T.A.N.D.A.R.D.24S.T.A.N.D.A.R.D.25S.T.A.N.D.A.R.D.26S.T.A.N.D.A.R.D.27S.T.A.N.D.A.R.D.28S.T.A.N.D.A.R.D.29S.T.A.N.D.A.R.D.30S.T.A.N.D.A.R.D.31S.T.A.N.D.A.R.D.三三、Oracle SQL编码规范编码规范 1 注释注释 1.1 注释方式可采用单行注释方式可采用单行/多行注释多行注释-或或/*/方式，对较为复

13、方式，对较为复 杂的杂的SQL语句加上注释；对主要的实现算法加注释，说明语句加上注释；对主要的实现算法加注释，说明算法功能；应对不易理解的分支条件表达式加注释；算法功能；应对不易理解的分支条件表达式加注释；1.2 注释风格注释单独成行放在注释风格注释单独成行放在SQL语句前面语句前面。2 命名和格式约定命名和格式约定 所有表名、字段名遵照数据字典的定义，系统保留字、内所有表名、字段名遵照数据字典的定义，系统保留字、内置函数名、置函数名、PL/SQL保留字大写。保留字大写。32S.T.A.N.D.A.R.D.2.1 变量名小写变量名小写;普通变量名使用普通变量名使用“v_”开头；开头；游标标变量

14、以游标标变量以“cur_”开头；开头；输入参数以输入参数以“i_”开头；开头；输出参数以输出参数以“o_”开头；开头；输入输出参数以输入输出参数以“io_”2.2 PL/SQL中的子程序命名，使用小写中的子程序命名，使用小写;函数以函数以“fn_”开始；开始；过程以过程以“sp_“开始；开始；触发器以触发器以“tr_badmltype_”；-b:before a:after dmltype:insert,update,delete 类型声明以类型声明以“tp_”开始；开始；包以包以“pkg_”开始；开始；33S.T.A.N.D.A.R.D.3 缩进与断行风格缩进与断行风格 3.1、低级别语句在

15、高级别语句后的，缩进一个制表符、低级别语句在高级别语句后的，缩进一个制表符 3.2、同一语句不同部分的缩进，如果为、同一语句不同部分的缩进，如果为sub statement，则为则为2个空格，如果与上一句某部分有密切联系的，个空格，如果与上一句某部分有密切联系的，则缩至与其对齐则缩至与其对齐 3.3、一行有多列，超过、一行有多列，超过80个字符时，基于列对齐原则，个字符时，基于列对齐原则，采用下行缩进采用下行缩进。3.4、WHERE子句书写时，每个条件占一行，子句书写时，每个条件占一行，语句令起一行时，语句令起一行时，以保留字或者连接符开始，以保留字或者连接符开始，连接符右连接符右 对齐。对齐

16、。34S.T.A.N.D.A.R.D.例如：语句另起一行时以保留字开始例如：语句另起一行时以保留字开始(如如and)左对齐可以左对齐可以 和和where 对齐。对齐。where f1=1 andf2=2 or f3=3 3.5 连接符连接符OR、IN、AND、以及、以及、=等前后加上一个空格。等前后加上一个空格。3.6 INSERT语句，尽量书写字段，字段可语句，尽量书写字段，字段可5个或个或6个一组。个一组。例如例如:35S.T.A.N.D.A.R.D.INSERT INTO T_MemberCapital(memberID,accountType,available,lastBalance

17、,lastOccupied,todayBalance,todayOccupied,profit,todayIn,todayOut,payment,received,miscFee,frozen,basefund)SELECT memberID,accountType,available,todayBalance,todayOccupied,0,0,0,0,0,0,0,0,paltFrozen+Frozen,basefund FROM T_HisMemberCapital WHERE effectDate=i_lastday;4 编写规范编写规范 4.1、SQL语句中除字符串中必须大写的内容外，

18、语句中除字符串中必须大写的内容外，全部小写，包括关键字，因为大小写结合全部小写，包括关键字，因为大小写结合 36S.T.A.N.D.A.R.D.需要需要 输入大写字母速度慢。（相同的输入大写字母速度慢。（相同的SQL语句是在语句是在SQL缓缓冲区中读取，冲区中读取，SQL分析器不用对分析器不用对SQL语句重新分析产生执行计语句重新分析产生执行计划，系统响应时间大大减少）。划，系统响应时间大大减少）。4.2 一行不要超过一行不要超过80个字符，在个字符，在1024*768分辨率下只能显示这分辨率下只能显示这 么么110字符。字符。4.3 多表连接时要使用表的别名来引用，并把别名前缀于每个多表连接

19、时要使用表的别名来引用，并把别名前缀于每个 Column上上.4.4 不要用不要用SELECT*,当你想在当你想在SELECT子句中列出所有的子句中列出所有的COLUMN时时,使用动态使用动态SQL列引用列引用*是一个方便的方法。不幸的是是一个方便的方法。不幸的是,这是一个非常低效的方法这是一个非常低效的方法.实际上实际上,ORACLE在解析的过程中在解析的过程中,会将会将*依次转换成所有的列名依次转换成所有的列名,这个工作是通过查询数据字典完成的这个工作是通过查询数据字典完成的,这意味这意味着将耗费更多的时间。着将耗费更多的时间。37S.T.A.N.D.A.R.D.4.5 不要依赖任何隐式的

20、数据类型转换。不要依赖任何隐式的数据类型转换。例如，不能为数字变量赋予字符值，而假定例如，不能为数字变量赋予字符值，而假定 SQL 会进行必要会进行必要的转换。相反，在为变量赋值或比较值之前，应使用适当的的转换。相反，在为变量赋值或比较值之前，应使用适当的 CONVERT 函数使数据类型相匹配。函数使数据类型相匹配。隐式转换可能带来的问隐式转换可能带来的问题：性能和版本升级的问题题：性能和版本升级的问题.隐式转换的例子：隐式转换的例子：declarec char(1);beginc:=24;-隐式转换隐式转换c:=23;-没有隐式转换没有隐式转换end;38S.T.A.N.D.A.R.D.4.

21、6 不要将空的变量值直接与比较运算符（符号）比较。如果变不要将空的变量值直接与比较运算符（符号）比较。如果变量可能为空，应使用量可能为空，应使用 IS NULL 或或 IS NOT NULL 进行比较，或者进行比较，或者使用使用 ISNULL 函数。函数。4.7 当删除全表记录时，用当删除全表记录时，用TRUNCATE替代替代DELETE，在通常情，在通常情况下，回滚段况下，回滚段(rollback segments)用来存放可以被恢复的信息，用来存放可以被恢复的信息，但占用大量的系统资源。而当运用但占用大量的系统资源。而当运用TRUNCATE时时,回滚段不再存回滚段不再存放任何可被恢复的信息

22、放任何可被恢复的信息.当命令运行后当命令运行后,数据不能被恢复数据不能被恢复.因此很少因此很少的资源被调用的资源被调用,执行时间也会很短。执行时间也会很短。4.8 避免在索引列上使用计算，避免改变索引列的类型，避免在索引列上使用计算，避免改变索引列的类型，WHERE子句中，如果索引列是函数的一部分优化器将不使用索引而使子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫描。用全表扫描。4.9 严格使用严格使用ORDER BY，ORDER BY 子句只在两种严格的条子句只在两种严格的条件下使用索引，件下使用索引，ORDER BY中所有的列必须包含在相同的索引中中所有的列必须包含在相同的

23、索引中并保持在索引中的排列顺序并保持在索引中的排列顺序.ORDER BY中所有的列必须定义为非中所有的列必须定义为非空。空。39S.T.A.N.D.A.R.D.4.10 总是使用索引的第一个列总是使用索引的第一个列，如果索引是建立在多个列上如果索引是建立在多个列上,只只有在它的第一个列有在它的第一个列(leading column)被被where子句引用时，优化器才子句引用时，优化器才会选择使用该索引。会选择使用该索引。4.11 如系统不需要唯一的记录，用如系统不需要唯一的记录，用UNION-ALL 替换替换UNION，当，当SQL语句需要语句需要UNION两个查询结果集合时两个查询结果集合时

24、,这两个结果集合会以这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序的方式被合并，然后在输出最终结果前进行排序.。如果用如果用UNION ALL替代替代UNION，这样排序就不是必要了，这样排序就不是必要了，效率效率就会因此得到提高。就会因此得到提高。用如下的用如下的SELECT.FOR UPDATE语句获得：语句获得：SELECT,.FROM WHERE FOR UPDATE OF,.NOWAIT 40S.T.A.N.D.A.R.D.5、INDEX使用使用 5.1 将索引所在表空间和数据所在表空间分别设于不同将索引所在表空间和数据所在表空间分别设于不同 的磁盘的

25、磁盘 上，有助于提高索引查询的效率。上，有助于提高索引查询的效率。5.2 对于多字段索引，只有当查询条件中存在从前往后对于多字段索引，只有当查询条件中存在从前往后 的部分字段时，才可能使用该索引。例如：有索引：的部分字段时，才可能使用该索引。例如：有索引：(A,B,C)，只有当，只有当 查询条件包含查询条件包含 A，或者，或者 A,B，或者或者A,B,C 时，方可能使用该索引，只包含时，方可能使用该索引，只包含 B 或者或者 C或者或者 B,C的查询字句，均不能使用该索引。因此，创建多字段的查询字句，均不能使用该索引。因此，创建多字段 索引时，一定要特别注意索引字段的次序。索引时，一定要特别注

26、意索引字段的次序。5.3 Oracle默认使用的基于代价的默认使用的基于代价的SQL优化器（优化器（CBO）非常依赖于统计信息，一旦统计信息不正常，会导致数据库非常依赖于统计信息，一旦统计信息不正常，会导致数据库 查询时不使用索引或使用错误的索引。一般来说，查询时不使用索引或使用错误的索引。一般来说，Oracle的的 自动任务里面会包含更新统计信息的语句，但如果表数据自动任务里面会包含更新统计信息的语句，但如果表数据 41S.T.A.N.D.A.R.D.发生了比较大的变化（超过发生了比较大的变化（超过30%）,可以考虑立即手动更新统计信息可以考虑立即手动更新统计信息 例如：例如：analyze

27、 table abc compute statistics，但注意，更新统计信息，但注意，更新统计信息 比较耗费系统资源，建议在系统空闲时执行。比较耗费系统资源，建议在系统空闲时执行。5.4 Oracle在进行一次查询时，一般对一个表只会使用一个索引，在进行一次查询时，一般对一个表只会使用一个索引，因此，有时候过多的索引可能导致因此，有时候过多的索引可能导致Oracle使用错误的索引，降低使用错误的索引，降低查询效率。例如某表有索引查询效率。例如某表有索引1（Policyno）和索引）和索引2（classcode），），如果查询条件为如果查询条件为 policyno=xx and classc

28、ode=xx，则系统有可，则系统有可能会使用索引能会使用索引2，相较于使用索引，相较于使用索引1，查询效率明显降低。，查询效率明显降低。5.5 大表（大表（10万条以上记录）关联的关联字段，一定要建索引，否万条以上记录）关联的关联字段，一定要建索引，否则可能导致查询效率大幅下降则可能导致查询效率大幅下降。5.6 “”和和”!=”操作符不会使用索引，可以用操作符不会使用索引，可以用“”来替代。来替代。5.7 当通配符当通配符“%”或者或者“_”作为查询字符串的第一个字符时，索作为查询字符串的第一个字符时，索引引 不会被使用。不会被使用。42S.T.A.N.D.A.R.D.5.8 尽量避免在尽量避

29、免在sql语句的查询条件的表字段上用语句的查询条件的表字段上用Function进行处进行处理，尤其是一些关键的数据过滤条件，这样会导致该字段的索引理，尤其是一些关键的数据过滤条件，这样会导致该字段的索引无法使用，实在无法避免的，可以考虑建立无法使用，实在无法避免的，可以考虑建立function的索引，但是的索引，但是这样的索引其他这样的索引其他sql语句一般无法使用，利用效率比较低，尽量不语句一般无法使用，利用效率比较低，尽量不要采取这种处理办法。要采取这种处理办法。6、SQL优化优化 6.1、Oracle在执行在执行IN子查询时，首先执行子查询，将查询结果放子查询时，首先执行子查询，将查询结

30、果放入临时表再执行主查询。而入临时表再执行主查询。而EXIST则是首先检查主查询，然后运则是首先检查主查询，然后运行子查询直到找到第一个匹配项。行子查询直到找到第一个匹配项。NOT EXISTS比比NOT IN效率稍效率稍高。但具体在选择高。但具体在选择IN或或EXIST操作时，要根据主子表数据量大小操作时，要根据主子表数据量大小来具体考虑。来具体考虑。eg:43S.T.A.N.D.A.R.D.Select*from dept where deptno in(select distinct deptno from emp);=Select*from dept where exists(sele

31、ct 1 from emp where emp.deptno=dept.deptno);6.2 对于一些大表，特别是大表关联的操作，书写对于一些大表，特别是大表关联的操作，书写SQL语句一定要语句一定要特别注意，建议先使用特别注意，建议先使用Toad，PL/SQL developer等工具先查看等工具先查看sql语语句执行计划，优化后再写进代码句执行计划，优化后再写进代码 6.3 建议在小表时就设计成用索引扫描。读数据有全表扫描方式和建议在小表时就设计成用索引扫描。读数据有全表扫描方式和索引方式。全表扫描就是顺序地访问表中每条记录索引方式。全表扫描就是顺序地访问表中每条记录.ORACLE采用一

32、采用一次读入多个数据块次读入多个数据块(database block)的方式优化全表扫描，索引方式的方式优化全表扫描，索引方式通过通过ROWID访问表。你可以采用基于访问表。你可以采用基于ROWID的访问方式情况的访问方式情况,提高提高访问表的效率访问表的效率,ROWID包含了表中记录的物理位置信息。包含了表中记录的物理位置信息。44S.T.A.N.D.A.R.D.6.4 不必要的排序操作不必要的排序操作 当在当在SQL STATEMENT中包含下面的语句中，会引起系统进行中包含下面的语句中，会引起系统进行排序操作。排序是非常昂贵的操作，如果能够避免就避免了吧。排序操作。排序是非常昂贵的操作，

33、如果能够避免就避免了吧。ORACLE Clause 内部操作内部操作 ORDER BYSORT ORDER DISTINCT,MINUS,INTERSECT,SORT AGGREGATE UNION或者其他需要唯一值的情况或者其他需要唯一值的情况 MIN,MAX,COUNT,SUM SORT AGGREGATE GROUP BY SORT GROUP BY45S.T.A.N.D.A.R.D.四、四、SQL调优基础调优基础(基于索引的基于索引的)1.常用索引的几大类型常用索引的几大类型:1.1 B*树索引：我们所说的树索引：我们所说的“传统传统”索引，这是索引，这是Oracle和大和大 多数其他

34、数据库的最常用的索引。多数其他数据库的最常用的索引。B*数的数的 构造类似与二叉树，能根据键提供一行或构造类似与二叉树，能根据键提供一行或 一个行集的快速访问。一个行集的快速访问。1.2 函数索引：将一个函数计算得到的结果存储在行的函数索引：将一个函数计算得到的结果存储在行的 列中，而不是存储数据本身。可以把基于列中，而不是存储数据本身。可以把基于 函数的索引看作一个虚拟列上的索引。函数的索引看作一个虚拟列上的索引。可以加快形如可以加快形如 SELECT*FROM T WHEN FUNCTION（TABLE_COLUMN）=SAME_VALUE46S.T.A.N.D.A.R.D.这样的查询，因

35、为值这样的查询，因为值FUNCTION（TABLE_COLUMN）已经提前计算并存储在索引中。已经提前计算并存储在索引中。1.3 位图索引位图索引:适用于高度重复而且通常只读的数据。适用于高度重复而且通常只读的数据。(高度重负是指相对于表中的总行数，数据只有很少高度重负是指相对于表中的总行数，数据只有很少 的几个不同值的几个不同值)。1.4 反向键索引反向键索引:利用反向键索引，如果索引中填充的是递增利用反向键索引，如果索引中填充的是递增 的值，索引条目在索引中可以得到的值，索引条目在索引中可以得到 更均匀的分布。更均匀的分布。1.5分区索引：分区索引：用于支持数据仓库中分区表的索引。用于支持

36、数据仓库中分区表的索引。2.基于索引的基于索引的SQL语句优化方法语句优化方法 2.1前言前言 客服业务受到客服业务受到SQL语句的影响非常大，在规模比较大的局点，语句的影响非常大，在规模比较大的局点，往往因为一个小的往往因为一个小的SQL语句不够优化，导致数据库性能急剧语句不够优化，导致数据库性能急剧47S.T.A.N.D.A.R.D.下降，小型机下降，小型机idle所剩无几，应用服务器断连、超时，严重所剩无几，应用服务器断连、超时，严重 影响业的正常运行。数据库的优化方法有很多种，在应用影响业的正常运行。数据库的优化方法有很多种，在应用 层说一般是基于索引的优化。层说一般是基于索引的优化。

37、如何判断哪些索引是必要的，哪些又是不必要的。判断的最终标如何判断哪些索引是必要的，哪些又是不必要的。判断的最终标准是看这些索引是否对我们的数据库性能有所帮助。具体到方法准是看这些索引是否对我们的数据库性能有所帮助。具体到方法上，就必须熟悉数据库应用程序中的所有上，就必须熟悉数据库应用程序中的所有SQL语句，从中统计出语句，从中统计出常用的可能对性能有影响的部分常用的可能对性能有影响的部分SQL，分析、归纳出作为，分析、归纳出作为Where条件子句的字段及其组合方式；在这一基础上可以初步判断出哪条件子句的字段及其组合方式；在这一基础上可以初步判断出哪些表的哪些字段应该建立索引。其次，必须熟悉应用

38、程序。必须些表的哪些字段应该建立索引。其次，必须熟悉应用程序。必须了解哪些表是数据操作频繁的表；哪些表经常与其他表进行连接；了解哪些表是数据操作频繁的表；哪些表经常与其他表进行连接；哪些表中的数据量可能很大；对于数据量大的表，其中各个字段哪些表中的数据量可能很大；对于数据量大的表，其中各个字段的数据分布情况如何；等等。对于满足以上条件的这些表，必须的数据分布情况如何；等等。对于满足以上条件的这些表，必须重点关注，因为在这些表上的索引，将对重点关注，因为在这些表上的索引，将对SQL语句的性能产生举语句的性能产生举足轻重的影响。足轻重的影响。48S.T.A.N.D.A.R.D.2.2建立索引常用的

39、规则如下：建立索引常用的规则如下：1、表的主键、外键必须有索引；、表的主键、外键必须有索引；2、数据量超过、数据量超过300的表应该有索引；的表应该有索引；3、经常与其他表进行连接的表，在连接字段上应该建立索引；、经常与其他表进行连接的表，在连接字段上应该建立索引；4、经常出现在、经常出现在Where子句中的字段，特别是大表的字段，应该建子句中的字段，特别是大表的字段，应该建立索引；立索引；5、索引应该建在选择性高的字段上；、索引应该建在选择性高的字段上；6、索引应该建在小字段上，对于大的文本字段甚至超长字段，、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；不要建索引；7、复

40、合索引的建立需要进行仔细分析；、复合索引的建立需要进行仔细分析；A、正确选择复合索引中的主列字段，一般是选择性较好的、正确选择复合索引中的主列字段，一般是选择性较好的字段；字段；49S.T.A.N.D.A.R.D.B、复合索引的几个字段是否经常同时以、复合索引的几个字段是否经常同时以AND方式出现在方式出现在Where 子句中？单字段查询是否极少甚至没有？如果是，则可以建立子句中？单字段查询是否极少甚至没有？如果是，则可以建立 复合索引；否则考虑单字段索引；复合索引；否则考虑单字段索引；C、如果复合索引中包含的字段经常单独出现在、如果复合索引中包含的字段经常单独出现在Where子句中，子句中，

41、则分解为多个单字段索引则分解为多个单字段索引;D、如果复合索引所包含的字段超过、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑个，那么仔细考虑其必要性，考虑减少复合的字段；减少复合的字段；8、频繁进行数据操作的表，不要建立太多的索引；、频繁进行数据操作的表，不要建立太多的索引；9、删除无用的索引，避免对执行计划造成负面影响；、删除无用的索引，避免对执行计划造成负面影响；以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立 必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立必须慎重，对每个索引的必要性都应该经

42、过仔细分析，要有建立 的依据因为太多的索引与不充分、不正确的索引对性能都毫无益处：的依据因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。操作也会增加处理上的开销。50S.T.A.N.D.A.R.D.2.3 应用实例：应用实例：1.避免对列的操作避免对列的操作 任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至

43、去掉函计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。数。例例1：下列：下列SQL条件语句中的列都建有恰当的索引，但条件语句中的列都建有恰当的索引，但30万行数据情万行数据情况下执行速度却非常慢：况下执行速度却非常慢：select*from record where substrb(CardNo,1,4)=5378(13秒)select*from record where amount/30 1000（11秒）select*from record where to_char(ActionTime,yyyymmdd)=19991201（10秒）51S.T.A.N.D.A.R.D

44、.由于由于where子句中对列的任何操作结果都是在子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避优化器优化，使用索引，避免表扫描，因此将免表扫描，因此将SQL重写如下：重写如下：select*from record where CardNo like 5378%（1秒）秒）select*from record where amount 100

45、0*30（1秒）秒）select*from record where ActionTime=to_date(19991201,yyyymmdd)（10，应该写为：应该写为：select col1,col2 from tab1 where col110。3.增加查询的范围限制增加查询的范围限制 以下查询表以下查询表record 中时间中时间ActionTime小于小于2001年年3月月1日的数据：日的数据：select*from record where ActionTime to_date(20010301,yyyymm)查询计划表明，上面的查询对表进行全表扫描，如果我们知道查询计划表明，上面

46、的查询对表进行全表扫描，如果我们知道 表中早的数据为表中早的数据为2001年年1月月1日，那么，可以增加一个最小时间，使查询日，那么，可以增加一个最小时间，使查询在一个完整的范围之内。在一个完整的范围之内。修改如下：修改如下：select*from record where ActionTime to_date(20010101,yyyymm)后一种后一种SQL语句将利用上语句将利用上ActionTime字段上的索引，从而提高查询效率。字段上的索引，从而提高查询效率。把把20010301换成一个变量，根据取值的机率，可以有一半以上的机会换成一个变量，根据取值的机率，可以有一半以上的机会提高效率

47、。提高效率。同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在Where子句中加上子句中加上“AND 列名列名 MAX(最大值最大值)”。53S.T.A.N.D.A.R.D.4.尽量去掉尽量去掉IN、OR 含有含有“IN”、“OR”的的Where子句常会使用工作表，使索引失效；子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。该包含索引。例例4：select count(*)from stuff where id_n

48、o in(0,1)（23秒）秒）可以考虑将可以考虑将or子句分开：子句分开：select count(*)from stuff where id_no=0 select count(*)from stuff where id_no=1然后再做一个简单的加法，与原来的然后再做一个简单的加法，与原来的SQL语句相比，查询速度更语句相比，查询速度更快。快。54S.T.A.N.D.A.R.D.5.尽量去掉尽量去掉 尽量去掉尽量去掉，避免全表扫描，如果数据是枚举值，且取值范围，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为固定，则修改为OR方式。方式。例例5：UPDATE SERVICEINF

49、O SET STATE=0 WHERE STATE0;以上语句由于其中包含了以上语句由于其中包含了，执行计划中用了全表扫描，执行计划中用了全表扫描（TABLE ACCESS FULL），没有用到），没有用到state字段上的索引。实际字段上的索引。实际应用中，由于业务逻辑的限制，字段应用中，由于业务逻辑的限制，字段state为枚举值，只能等于为枚举值，只能等于0，1或或2，而且，值等于，而且，值等于=1，2的很少，因此可以去掉的很少，因此可以去掉，利用索，利用索引来提高效率。引来提高效率。修改为：修改为：UPDATE SERVICEINFO SET STATE=0 WHERE STATE=1

50、OR STATE=2。6.去掉去掉Where子句中的子句中的IS NULL和和IS NOT NULLWhere字句中的字句中的IS NULL和和IS NOT NULL将不会使用索引而是进将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉去掉Where子句中的子句中的IS NULL和和IS NOT NULL。55S.T.A.N.D.A.R.D.7.分解复杂查询，用常量代替变量分解复杂查询，用常量代替变量对于复杂的对于复杂的Where条件组合，条件组合，Where中含有多个带索引的字段，中含有多个带索引的字段，考

51、虑用考虑用IF语句分情况进行讨论；同时，去掉不必要的外来参数条语句分情况进行讨论；同时，去掉不必要的外来参数条件，减低复杂度，以便在不同情况下用不同字段上的索引。件，减低复杂度，以便在不同情况下用不同字段上的索引。Where(DisposalCourseFlag v_DisPosalCourseFlag)or(v_DisPosalCourseFlag is null)and.的查询，的查询，(这里这里v_DisPosalCourseFlag为一个输入变量，取值范围可能为为一个输入变量，取值范围可能为NULL，0，1，2，3，4，5，6，7)，可以考虑分情况用，可以考虑分情况用IF语句进行讨论，

52、语句进行讨论，类似：类似：IF v_DisPosalCourseFlag=1 THENWhere DisposalCourseFlag=1 and.ELSIF v_DisPosalCourseFlag=2 THENWhere DisposalCourseFlag=2 and.。56S.T.A.N.D.A.R.D.8.like子句尽量前端匹配子句尽量前端匹配因为因为like参数使用的非常频繁，因此如果能够对参数使用的非常频繁，因此如果能够对like子句使用索引，子句使用索引，将很高的提高查询的效率。将很高的提高查询的效率。select*from city where name like%S%以上

53、查询的执行计划用了全表扫描（以上查询的执行计划用了全表扫描（TABLE ACCESS FULL），），如果能够修改为：如果能够修改为：select*from city where name like S%那么查询的执行计划将会变成（那么查询的执行计划将会变成（INDEX RANGE SCAN），成功），成功的利用了的利用了name字段的索引。这意味着字段的索引。这意味着Oracle SQL优化器会识别优化器会识别出用于索引的出用于索引的like子句，只要该查询的匹配端是具体值。因此我子句，只要该查询的匹配端是具体值。因此我们在做们在做like查询时，应该尽量使查询的匹配端是具体值，即使用查询时

54、，应该尽量使查询的匹配端是具体值，即使用like S%。9.用用Case语句合并多重扫描语句合并多重扫描57S.T.A.N.D.A.R.D.我们常常必须基于多组数据表计算不同的聚集。我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询：例如下例通过三个独立查询：例例8：1）select count(*)from emp where sal5000;这样我们需要进行三次全表查询，但是如果我们使用这样我们需要进行三次全表查询，但是如果我们使用case语句：语句：select count(sale when sal 5000then 1 else null end)count_poor

55、from emp;这样查询的结果一样，但是执行计划只进行了一次全表查询。这样查询的结果一样，但是执行计划只进行了一次全表查询。58S.T.A.N.D.A.R.D.10.使用使用nls_date_formatselect*from record where to_char(ActionTime,mm)=12这个查询的执行计划将是全表查询，如果我们改变这个查询的执行计划将是全表查询，如果我们改变nls_date_format，SQLalert session set nls_date_formate=MM;现在重新修改上面的查询：现在重新修改上面的查询：select*from record whe

56、re ActionTime=12这样就能使用这样就能使用actiontime上的索引了，它的执行计划将是上的索引了，它的执行计划将是（INDEX RANGE SCAN）。）。11.使用基于函数的索引使用基于函数的索引 select*from emp where substr(ename,1,2)=SM;但是这种查询在客服系统又经常使用，我们可以创建一个带有但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的基于函数的索引，函数的基于函数的索引，create index emp_ename_substr on eemp(substr(ename,1,2);59S.T.A.N.

57、D.A.R.D.12.使用分区索引使用分区索引 在用分析命令对分区索引进行分析时，每一个分区的数据值的在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入范围信息会放入Oracle的数据字典中。的数据字典中。Oracle可以利用这个信息可以利用这个信息来提取出那些只与来提取出那些只与SQL查询相关的数据分区。查询相关的数据分区。例如，假设你已经定义了一个分区索引，并且某个例如，假设你已经定义了一个分区索引，并且某个SQL语句需要语句需要在一个索引分区中进行一次索引扫描。在一个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索会仅仅访问这个索引分区，而且会在这个分区上调用一

58、个此索引范围的快速全扫描。引分区，而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引，所以提高了查询的速度。因为不需要访问整个索引，所以提高了查询的速度。13.使用位图索引使用位图索引 位图索引可以从本质上提高使用了小于位图索引可以从本质上提高使用了小于1000个唯一数据值的数据个唯一数据值的数据列的查询速度，因为在位图索引中进行的检索是在列的查询速度，因为在位图索引中进行的检索是在RAM中完成的，中完成的，而且也总是比传统的而且也总是比传统的B树索引的速度要快。对于那些少于树索引的速度要快。对于那些少于1000个个唯一数据值的数据列建立位图索引，可以使执行效率更快。唯一

59、数据值的数据列建立位图索引，可以使执行效率更快。60S.T.A.N.D.A.R.D.14.决定使用全表扫描还是使用索引决定使用全表扫描还是使用索引 和所有的秘笈一样，最后一招都会又回到起点，最后我们来和所有的秘笈一样，最后一招都会又回到起点，最后我们来讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全

60、没有顺序，那么一个要返回记录数小于查询的表完全没有顺序，那么一个要返回记录数小于10的查询的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于高很多。但是如果表非常有顺序，那么如果查询的记录数大于40时，可能使用全表扫描更快。因此，有一个索引范围扫描的总时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：体原则是：1）对于原始排序的表）对于原始排序的表 仅读取少于表记录数仅读取少于表记录数40的查询应该使用的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数

61、的索引范围扫描。反之，读取记录数目多于表记录数的40的查询的查询应该使用全表扫描。应该使用全表扫描。61S.T.A.N.D.A.R.D.2）对于未排序的表）对于未排序的表 仅读取少于表记录数仅读取少于表记录数7的查询应该使用索引的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的范围扫描。反之，读取记录数目多于表记录数的7的查询应该的查询应该使用全表扫描使用全表扫描。62S.T.A.N.D.A.R.D.五五、Oracle常用函数常用函数 5.1 数字函数数字函数 1.CEIL(n)：该函数用于返回大于等于数字：该函数用于返回大于等于数字n的最小整数的最小整数 eg:SQLSELECT

62、ceil(15),ceil(15.1),ceil(-15.1)FROM dual;ceil(15)ceil(15.1)ceil(-15.1)-15 16 -15 2.ABS(n):该函数用于返回数字该函数用于返回数字n的绝对值的绝对值 63S.T.A.N.D.A.R.D.SQL DECLARE 2 v_abs NUMBER(6,2);3 BEGIN 4 v_abs:=abs(&no);5 dbms_output.put_line(绝对值绝对值:|v_abs);6 END;7/输入输入no的值：的值：-12.3 绝对值：绝对值：12.364S.T.A.N.D.A.R.D.3.EXP(n):该函数

63、用于返回该函数用于返回e的的n次幂次幂(e=2.71828183.)eg:SQL DECLARE 2 v_exp NUMBER(6,2);3 BEGIN 4 v_exp:=exp(4);5 dbms_output.put_line(e的的4次幂次幂:|v_exp);6 END;7 /e的的4次幂次幂:54.64.FLOOR(n):该函数用于返回小于等于数字该函数用于返回小于等于数字n的最大整数的最大整数 SQLSELECT floor(15),floor(15.1)FROM dual;65S.T.A.N.D.A.R.D.FLOOR(15)FLOOR(15.1)-15 155.POWER(m,n

64、):该函数用于返回数字该函数用于返回数字m的的n次幂，底数次幂，底数m和指和指数数n可以是任意数字。可以是任意数字。但如果数字但如果数字m为负数，则数字为负数，则数字n必须是正数。必须是正数。SQLSELECT power(-2,3),power(2,-1)FROM dual;POWER(-2,3)POWER(2,-1)-8 0.566S.T.A.N.D.A.R.D.6.SORT(n):用于返回数字的平方根，而且数字用于返回数字的平方根，而且数字n必须大于等于必须大于等于0 eg:SQLDECLARE 2 v_sqrt NUMNBER(6,2);3 BEGIN 4 v_sqrt:=sqrt(1

65、0);5 dbms_output.put_line(10的平方根的平方根:|v_sqrt);6 END;7/10的平方根的平方根:3.16 7.LN(n):该函数用于返回数字该函数用于返回数字n的自然对数的自然对数,其中数字其中数字n必须大于必须大于0 67S.T.A.N.D.A.R.D.SQL DECLARE 2 v_ln NUMBER(6,2);3 BEGIN 4 v_ln:=ln(4);5 dbms_output.put_line(4的自然对数的自然对数:|v_ln);6 END;7 /4的自然对数的自然对数:1.39 8.LOG(m,n):该函数用于返回以数字该函数用于返回以数字m为底

66、的数字为底的数字n的对数的对数,数字数字m可以是除可以是除0和和1以外的任何正整数以外的任何正整数,数字数字n可以是任何正整数。可以是任何正整数。SQLSELECT log(2,8),log(10,100)FROM dual;68S.T.A.N.D.A.R.D.LOG(2,8)LOG(10,100)-3 3 9、TRUNC(n,m)：该函数用于截取数字。如果省略数字：该函数用于截取数字。如果省略数字m，则将，则将数字数字n的小数部分截去；如果数字的小数部分截去；如果数字m是正数，则将数字是正数，则将数字n截取至小截取至小数点后的第数点后的第m位位;如果数字如果数字m是负数是负数,则将数字则将数字n截取至小数点的前截取至小数点的前 m位。位。SQLSELECT trunc(45.926),trunc(45.926,1),trunc(45.926,-1)2 FROM dual;TRUNC(45.936)TRUNC(45.926,1)TRUNC(45.926,-1)-45 45.9 4069S.T.A.N.D.A.R.D.10、MOD(m,n):该函数用于取得两个相除后的余数。如果数字该函数