- 浏览: 105401 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
小小诺:
博主实例2中的代码博主难道没有测试吗?“工资总额不能超过6万元 ...
PLSQL -
emilyzhanghong:
我一般都用工具导入导出,语句记不住.
ORACLE 导入导出
ORACLE-SQL优化
访问Table的方式
ORACLE 采用两种访问表中记录的方式:
a. 全表扫描
全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.
b. 通过ROWID访问表
你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.
清空共享池的语法:
alter system flush buffer_cache
1. 选择最有效率的表名顺序(只在基于规则的优化器中有效)
设置基于规则的优化器
alter system set optimizer_mode=RULE
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并
例如:
表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from ids_emp,ids_dept
选择TAB2作为基础表 (不佳的方法)
select count(*) from ids_dept,ids_emp
2 WHERE子句中的连接顺序.
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
效率高
select count(*) from ids_emp e,ids_dept d
where e.deptno=d.deptno and e.sal>5000 and job='总经理' and rownum<10
效率低
select count(*) from ids_emp e,ids_dept d
where rownum<10 and e.deptno=d.deptno and e.sal>5000 and job='总经理'
3 SELECT子句中避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法.
实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
4. 计算记录条数
和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
(在论坛中曾经对此有过相当热烈的讨论, 这个观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)
设置基于规则的优化器
alter system set optimizer_mode=RULE
select count(empno) from ids_emp --7.25 ---效率高
select count(*) from ids_emp --17.469
select count(ename) from ids_emp --17.219
5. 用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
例如
低效:
select deptno,avg(sal) from ids_emp group by deptno having deptno>20
高效
select deptno,avg(sal) from ids_emp where deptno>20 group by deptno
(HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)
6. 使用表的别名(Alias)
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的 归属)
7. 用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
设置基于基于成本的优化器
下面连接语句设置基于成本的优化器执行快
alter system set optimizer_mode=ALL_ROWS 效率差不多,采用exists
设置基于规则的优化器 运行结果如下:
alter system set optimizer_mode=RULE
效率低(增加索引) 72秒
select * from ids_emp
where deptno in(select deptno from ids_dept where loc='a'
效率高(增加索引) 18秒
select * from ids_emp e
where exists(select * from ids_dept d where e.deptno=d.deptno
and loc='a')
8. 用NOT EXISTS替代NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
效率低(增加索引)
select * from ids_emp
where deptno not in(select deptno from ids_dept where loc='a')
效率高(增加索引)
select * from ids_emp e
where not exists(select * from ids_dept d where e.deptno=d.deptno
and loc='a')
9. 用表连接替换EXISTS
通常来说 , 采用表连接的方式比EXISTS更有效率
select * from ids_empno e
where exists(select * from ids_deptno d where e.deptno=d.deptno
and loc='a')
更高效
select ename from ids_emp e,ids_dept d where e.deptno=d.deptno and loc='a'
10.. 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用 DISTINCT. 一般可以考虑用EXIST替换
select distinct d.deptno,d.dname from ids_emp e,ids_deptno d
where e.deptno=d.deptno
高效:
select d.deptno,d.dname from ids_deptno d
where exists( select * from ids_emp e where e.deptno=d.deptno)
11 常量的计算是在语句被优化时一次性完成,而不是在每次执行时。
下面是检索月薪大于2000的表达式:
sal > 24000/12
sal > 2000
sal*12 > 24000
如果SQL语句包括第一种情况,优化器会简单地把它转变成第二种。
优化器不会简化跨越比较符的表达式,例如第三条语句,鉴于此,应尽量写用常量跟字段比较检索的表达式,而不要将字段置于表达式当中。否则没有办法优化,比如如果sal上有索引,第一和第二就可以使用(执行索引),第三就难以使用(sal*12 不识别索引)。
12 IN、OR子句常会使用工作表,使索引失效:
如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。
13 消除对大型表行数据的顺序存取:
在嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询 10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如,两个表:学生表(学号、姓名、年龄??)和选课表(学号、课程号、成绩)。如果两个表要做连接,就要在“学号”这个连接字段上建立索引。
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引,但某些形式的WHERE子句强迫优化器使用顺序存取。
使用设置基于基于成本的优化器来测试
alter system set optimizer_mode=ALL_ROWS scope=both
下面的查询将强迫对ide_emp表执行顺序操作:
select * from ids_emp where(deptno=10 and deptno=20) or empno=3000
时间 17秒左右
虽然在empno上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:
SELECT * FROM ids_emp WHERE deptno=10 and deptno=20
UNION
SELECT * FROM ids_emp WHERE empno=3000
这样就能利用索引路径处理查询。
时间 0.078秒左右
注:UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
14 避免相关子查询:
一个列的标签同时在主查询和WHERE子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。
访问Table的方式
ORACLE 采用两种访问表中记录的方式:
a. 全表扫描
全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.
b. 通过ROWID访问表
你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.
清空共享池的语法:
alter system flush buffer_cache
1. 选择最有效率的表名顺序(只在基于规则的优化器中有效)
设置基于规则的优化器
alter system set optimizer_mode=RULE
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并
例如:
表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from ids_emp,ids_dept
选择TAB2作为基础表 (不佳的方法)
select count(*) from ids_dept,ids_emp
2 WHERE子句中的连接顺序.
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
效率高
select count(*) from ids_emp e,ids_dept d
where e.deptno=d.deptno and e.sal>5000 and job='总经理' and rownum<10
效率低
select count(*) from ids_emp e,ids_dept d
where rownum<10 and e.deptno=d.deptno and e.sal>5000 and job='总经理'
3 SELECT子句中避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法.
实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
4. 计算记录条数
和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
(在论坛中曾经对此有过相当热烈的讨论, 这个观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)
设置基于规则的优化器
alter system set optimizer_mode=RULE
select count(empno) from ids_emp --7.25 ---效率高
select count(*) from ids_emp --17.469
select count(ename) from ids_emp --17.219
5. 用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
例如
低效:
select deptno,avg(sal) from ids_emp group by deptno having deptno>20
高效
select deptno,avg(sal) from ids_emp where deptno>20 group by deptno
(HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)
6. 使用表的别名(Alias)
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的 归属)
7. 用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
设置基于基于成本的优化器
下面连接语句设置基于成本的优化器执行快
alter system set optimizer_mode=ALL_ROWS 效率差不多,采用exists
设置基于规则的优化器 运行结果如下:
alter system set optimizer_mode=RULE
效率低(增加索引) 72秒
select * from ids_emp
where deptno in(select deptno from ids_dept where loc='a'
效率高(增加索引) 18秒
select * from ids_emp e
where exists(select * from ids_dept d where e.deptno=d.deptno
and loc='a')
8. 用NOT EXISTS替代NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
效率低(增加索引)
select * from ids_emp
where deptno not in(select deptno from ids_dept where loc='a')
效率高(增加索引)
select * from ids_emp e
where not exists(select * from ids_dept d where e.deptno=d.deptno
and loc='a')
9. 用表连接替换EXISTS
通常来说 , 采用表连接的方式比EXISTS更有效率
select * from ids_empno e
where exists(select * from ids_deptno d where e.deptno=d.deptno
and loc='a')
更高效
select ename from ids_emp e,ids_dept d where e.deptno=d.deptno and loc='a'
10.. 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用 DISTINCT. 一般可以考虑用EXIST替换
select distinct d.deptno,d.dname from ids_emp e,ids_deptno d
where e.deptno=d.deptno
高效:
select d.deptno,d.dname from ids_deptno d
where exists( select * from ids_emp e where e.deptno=d.deptno)
11 常量的计算是在语句被优化时一次性完成,而不是在每次执行时。
下面是检索月薪大于2000的表达式:
sal > 24000/12
sal > 2000
sal*12 > 24000
如果SQL语句包括第一种情况,优化器会简单地把它转变成第二种。
优化器不会简化跨越比较符的表达式,例如第三条语句,鉴于此,应尽量写用常量跟字段比较检索的表达式,而不要将字段置于表达式当中。否则没有办法优化,比如如果sal上有索引,第一和第二就可以使用(执行索引),第三就难以使用(sal*12 不识别索引)。
12 IN、OR子句常会使用工作表,使索引失效:
如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。
13 消除对大型表行数据的顺序存取:
在嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询 10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如,两个表:学生表(学号、姓名、年龄??)和选课表(学号、课程号、成绩)。如果两个表要做连接,就要在“学号”这个连接字段上建立索引。
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引,但某些形式的WHERE子句强迫优化器使用顺序存取。
使用设置基于基于成本的优化器来测试
alter system set optimizer_mode=ALL_ROWS scope=both
下面的查询将强迫对ide_emp表执行顺序操作:
select * from ids_emp where(deptno=10 and deptno=20) or empno=3000
时间 17秒左右
虽然在empno上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:
SELECT * FROM ids_emp WHERE deptno=10 and deptno=20
UNION
SELECT * FROM ids_emp WHERE empno=3000
这样就能利用索引路径处理查询。
时间 0.078秒左右
注:UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
14 避免相关子查询:
一个列的标签同时在主查询和WHERE子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。
发表评论
-
实现数据的列转入到行中的过程语言
2013-05-07 14:16 669drop PROCEDURE RSPDB.GET_C ... -
db2工作笔记
2012-02-15 11:03 949// 生成32位随机数 CREATE OR REPLAC ... -
db2 基本命令整理
2012-01-13 13:34 948安装好数据库 进行系统环境变量的设置 可以在doc命令行上面 ... -
db2 数据备份和还原
2012-01-12 17:53 1101connect to odsdb user reportdb ... -
db2命令
2011-12-27 10:57 8901.关闭db2 db2stop 或db2 ... -
cognos 连不上业务库
2011-12-10 17:39 1898Cognos Error 'QE-DEF-0285 The l ... -
编目远程数据库到本地
2011-11-22 20:52 892db2 catalog tcpip node reportdb ... -
PLSQL
2011-09-21 20:27 4846--变量声明 declare -- 程序体 b ... -
快速生成测试数据
2011-09-21 11:34 556select rownum as id, to_char(sy ... -
window下 ORACL卸载
2011-07-28 21:23 1014用Oracle自带的卸载程序不能从根本上卸载Oracle,从而 ... -
SQL优化
2011-07-17 00:24 695SQL优化的一些资料 -
ORACLE 问题
2011-07-04 23:55 759表 张三 英语 张三 语文 张三 英语 李四 英语 李四 语文 ... -
ORACLE 导入导出
2011-07-04 23:54 891Oracle数据导入导出imp/exp就相当于oracle数据 ... -
SQl游标的使用
2011-05-27 14:13 769use testtest-- 定义游标MyTestCursor ... -
序列自增长的问题
2011-05-25 11:16 769用存储过程为多表生成序列: 为每张表生成对应的序列 --创建 ... -
Oracle(SQL查询测试题)
2011-05-20 14:13 1302--客户表a(id name address) 登 ... -
Oracle(SQL 之视图)
2011-05-20 11:55 864-- 查询部门号为10所有的员工,所有得到提成的员工,以及20 ... -
Oracle(SQL之查询)
2011-05-20 11:47 897--查询语句 select * from emp; -- 查询 ... -
Oracle(SQL)
2011-05-20 11:31 769--插入一条数据 select * from emp; --修 ... -
Oracle(procedure)
2011-05-20 11:07 2140--存储过程 -- 列1: 如果员工工资小于2000的涨百分之 ...
相关推荐
Oracle SQL优化是数据库管理员和开发人员提升系统性能的关键技能之一。这个"Oracle_SQL优化脚本_完整实用资源"压缩包包含了一系列工具和方法,旨在帮助你优化在Oracle数据库上运行的SQL查询,从而提高数据库的响应...
Oracle_SQL优化规则 Oracle_SQL优化规则 完整的给力文档,请下载 。
Oracle SQL优化是数据库管理员和开发人员提升系统性能的关键技能。Oracle数据库系统因其高效、稳定和功能强大而被广泛使用,但随着数据量的增长,SQL查询的优化变得至关重要。本资源"Oracle_SQL优化.PDF"虽然内容...
"SQL-Optimization.rar_oracle_sql优化"这个压缩包显然聚焦于如何提升Oracle数据库中的SQL语句性能。以下是对SQL优化及其在Oracle环境中的实践进行的详细解释。 一、SQL优化的重要性 SQL语句优化是数据库管理的核心...
oracle_sql优化讲义.doc
Oracle SQL 优化是提升数据库性能的关键环节,涉及多个方面,包括选择合适的优化器、优化数据访问方式、以及充分利用SQL语句的共享机制。以下是对这些知识点的详细解释: 1. 选择适合的ORACLE优化器: ORACLE提供...
这是因为 WebLogic 服务器为了更好地管理和操作数据库连接,会使用自己的包装类 `weblogic.jdbc.wrapper.Clob_oracle_sql_CLOB` 来表示 CLOB 类型的数据,而不是直接使用 Oracle 提供的标准 `oracle.sql.CLOB` 类。...
### Oracle SQL优化详解 #### 一、Oracle优化器概述 Oracle数据库中的SQL执行计划是由优化器根据查询条件、数据分布等因素动态生成的。优化器的主要作用在于决定如何最有效地执行SQL语句,以达到最小化资源消耗...
Oracle_Sql_Pl_Sql_性能优化.doc Oracle_Sql_Pl_Oracle_Sql_Pl_Sql_性能优化.docSql_性能优化.doc Oracle_Sql_Pl_Sql_性能优化.doc
Oracle_SQL性能优化.
在Oracle数据库系统中,SQL的性能优化是至关重要的,因为它直接影响到系统的响应速度和整体效率。本资料"ORACLE_SQL性能优化(全)"提供了全面的指南,旨在帮助数据库管理员和开发人员提升SQL查询的性能。 1. **SQL...
Oracle SQL 优化是数据库性能提升的关键,涉及到多个层面的技术策略。在这个系列中,我们将深入探讨如何最...通过了解和应用这些SQL优化技术,可以显著提升Oracle数据库的性能,从而提高整体系统的响应速度和用户体验。
SQL优化衡量指标** 性能通常通过响应时间和并发性来衡量。性能不佳可能是由于开发人员对SQL效率的忽视或对SQL执行原理的不了解。优化不仅限于SQL语法和内嵌函数的掌握,还需理解SQL解析和成本基优化器(CBO)的工作...
《SQL优化手册_v1.0.doc》是一份专门针对Oracle数据库SQL优化的详细指南,旨在帮助用户提升SQL语句的执行效率,进而提高整体数据库系统的性能。在Oracle数据库管理中,SQL优化是至关重要的,因为它直接影响到数据的...
ORACLE_SQL语句优化资料
ORACLE_SQL性能优化(全),掌握性能优化的基础层面,让oracle变得更易控
在"oracle常用命令集.sql"中,可能包含了如DML(INSERT、UPDATE、DELETE)语句,DDL(CREATE、ALTER、DROP)语句,以及查询优化技巧,例如使用索引、子查询、联接操作等。 4. 数据库操作实例 描述中的"oracle常用...