我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为笔者学习、摘录、并汇总部分资料与大家分享!
(1)选择最有效率的表名顺序(只在基于规则的优化器中有效):
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
(2)WHERE子句中的连接顺序.:
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
(3)SELECT子句中避免使用 ‘ * ‘:
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
(4)减少访问数据库的次数:
ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等;
(5)在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200
(6)使用DECODE函数来减少处理时间:
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
(7)整合简单,无关联的数据库访问:
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
(8)删除重复记录:
最高效的删除重复记录方法 ( 因为使用了ROWID)例子:
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
(9)用TRUNCATE替代DELETE:
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
(10)尽量多使用COMMIT:
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费
(11)用Where子句替换HAVING子句:
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里
(12)减少对表的查询:
在含有子查询的SQL语句中,要特别注意减少对表的查询.例子:
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
(13)通过内部函数提高SQL效率.:
复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
(14)使用表的别名(Alias):
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
(15)用EXISTS替代IN、用NOT EXISTS替代NOT IN:
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例子:
(高效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')
(16)识别'低效执行'的SQL语句:
虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
(17) 用索引提高效率:
索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.:
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
(18)用EXISTS替换DISTINCT:
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子:
(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
(高效):
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X'
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
(19)sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行
(20)在java代码中尽量少用连接符“+”连接字符串!
分享到:
相关推荐
### ORACLE中SQL查询优化技术 #### 一、引言 在现代企业级数据库应用中,Oracle数据库因其高性能、高可靠性和丰富的功能...在实际工作中,还需要不断积累经验并通过监控工具分析查询执行情况,持续改进查询优化策略。
9. **分区**:Oracle的分区功能允许大型表按特定键值划分为多个部分,从而提高查询性能和管理效率。 10. **分布式数据库**:Oracle支持分布式数据库系统,允许数据在多个地理位置的数据库之间透明地分布和操作。 ...
Oracle查询优化改写是数据库管理中的关键技能,它涉及到如何高效地从大量数据中提取所需信息,以提高系统性能和响应速度。...在实践中,深入学习Oracle的查询优化案例,能够积累宝贵的经验,提升数据库管理的专业水平。
### Oracle经典教程:Oracle基本知识 #### 一、Oracle简介 Oracle是一款由美国甲骨文公司(Oracle Corporation)开发的大型关系...希望读者能够在后续的学习中不断积累经验,逐步成为一名熟练的Oracle数据库管理员。
在实际工作中,熟练掌握DB2和Oracle的使用技巧,包括但不限于性能调优、故障排查、备份恢复、安全性管理等,是提升数据库管理效率和系统稳定性的关键。同时,不断学习和积累新的特性和最佳实践,能够帮助我们应对日...
3. **精妙SQL语句介绍**:SQL是与Oracle交互的主要语言,高效且复杂的SQL查询能显著提升数据库操作效率。这可能涉及到子查询、联接操作、窗口函数、集合操作以及自定义SQL函数等高级技术,掌握这些能优化数据处理...
本文针对Oracle关系数据库的运行效率优化技术进行了深入分析,并提出了优化SQL语句查询功能的理论与方法。 首先,文章提出了针对Oracle数据库中SQL查询语句的性能问题的分析方法。Oracle数据库提供了多种工具和方法...
Oracle数据库是全球广泛使用的大型关系型数据库管理系统,尤其在企业级应用中占据着重要的地位。本教程将带你深入了解Oracle的基础知识,帮助你踏入...学习Oracle不仅是技术的积累,也是解决问题和提升职业能力的过程。
3. 表分区:对大表进行分区,以提升查询速度和管理效率。 4. 资源管理:利用SQL*Loader快速导入大量数据,设置数据库参数优化系统资源使用。 六、Oracle数据库备份与恢复 1. 数据泵导出导入:EXPDP和IMPDP工具...
本指南将深入浅出地讲解Oracle的核心概念、安装与配置、数据操作、SQL查询、表结构设计、备份与恢复等关键点,帮助你快速上手。 首先,Oracle的安装与配置是学习的第一步。你需要下载适合你操作系统的Oracle版本,...
Oracle是世界上最广泛使用的数据库管理系统之一,它在企业级数据存储、管理和处理方面有着深厚的技术积累。这篇“Oracle常识-常用的知识”将带你深入了解Oracle的基本概念、功能和常见操作。 1. Oracle数据库概述:...
《我的读书笔记(Oracle)个人版》是我个人在深入学习Oracle数据库系统过程中积累的心得体会,这份文档旨在为初学者提供一个清晰、易懂的Oracle入门教程。Oracle数据库是全球广泛应用的关系型数据库管理系统,尤其在...
- 分页(LIMIT和OFFSET):理解如何分页显示数据,以提高数据检索效率和用户体验。 3. **第4章 单行函数** - 数学函数:如加减乘除、幂运算等,以及日期和时间处理函数。 - 字符串函数:如CONCAT、SUBSTR、UPPER...
总之,Oracle数据库的学习不仅需要理论知识的积累,更需要大量的实践操作来加深理解和掌握。通过系统的学习,可以成为一名合格的Oracle数据库管理员或开发人员,为个人职业发展和企业的信息化建设做出贡献。同时,...
12. **分区技术**:Oracle 11g的分区功能可将大型表划分为较小的、更易管理的部分,提高查询效率和维护性能。 以上是我在Oracle 11g学习过程中积累的主要知识点,每个点都值得深入研究和实践。随着对Oracle的不断...
3. **Oracle数据库优化与性能调整**:数据库性能优化是提升系统效率的关键。书中会讲解如何通过调整SQL语句、索引策略、表空间管理、内存分配等方式来提升查询速度和整体系统性能。这部分还会涉及Oracle的自动性能...
- 表分区:将大表分割成多个较小部分,便于管理和提高查询效率。 - 索引组织表(IOT):采用索引存储方式组织数据,特别适合于频繁插入和删除操作。 - **并行处理**: - 并行查询(PQ):利用多处理器或多节点...
在企业级应用开发领域,Oracle Forms作为一款功能强大且灵活的应用开发工具,在多个版本迭代过程中积累了大量的用户基础与应用场景。随着技术的发展与需求的变化,从早期版本Oracle Forms 4.5升级到更先进的6i版本...
1. **高性能与高可用性**:Oracle采用了多种技术确保系统的高性能和高可用性,比如通过分区技术提高查询效率、通过GoldenGate实现数据复制等。 2. **安全性**:Oracle提供了强大的安全机制,包括用户认证、访问控制...