SQL语句优化方案(二)
ESC,REGIONFROM LOCATION WHERE LOC_ID =10
UNIONSELECT LOC_ID , LOC_DESC , REGIONFROM LOCATION WHERE REGION ='MELBOURNE'
(低效):
SELECT LOC_ID,LOC_DESC,REGIONFROM LOCATION WHERE LOC_ID= 10OR REGION = 'MELBOURNE'
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
8、用IN来替换OR
这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．
低效:
SELECT….FROM LOCATION WHERE LOC_ID =10OR LOC_ID=20OR LOC_ID=30
高效:
SELECT…FROM LOCATION WHERE LOC_IN IN (10,20,30);
二、SQL语句结构优化 www.2cto.com
1、SELECT子句中避免使用‘ * ‘：
2、用TRUNCATE替代DELETE ：
用TRUNCATE替代DELETE删除全表记录：（大数据量的表用次方法）
当删除表中的记录时,在通常情况下,回滚段(rollback segments )用来存放可以被恢复的信息. 如果你没有COMMIT 事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE 时, 回滚段不再存放任何可被恢复的信息.
3、用Where子句替换HAVING 子句：
避免使用HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE 子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where 次之，having 最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的， where 也应该比having快点的
4、sql语句用大写
因为oracle 总是先解析sql语句，把小写的字母转换成大写的再执行。
5、在java代码中尽量少用连接符“＋”连接字符串！
6、避免改变索引列的类型.:
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换. 假设EMPNO 是一个数值类型的索引列.
SELECT … FROM EMP WHERE EMPNO = ‘123'实际上,经过ORACLE类型转换, 语句转化为:
SELECT …FROM EMP WHERE EMPNO = TO_NUMBER(‘123')
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.现在,假设EMP_TYPE是一个字符类型的索引列.
SELECT …FROM EMP WHERE EMP_TYPE =123
这个语句被ORACLE转换为:
SELECT …FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL 进行隐式的类型转换,最好把类型转换用显式表现出来.注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型
7、优化GROUP BY: www.2cto.com
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY之前过滤掉.下面两个
查询返回相同结果但第二个明显就快了许多.
低效:
1SELECT JOB,AVG(SAL)FROM EMP GROUPby JOBHAVING JOB= 'PRESIDENT' OR JOB ='MANAGER'
高效:
1SELECT JOB,AVG(SAL)FROM EMP WHERE JOB ='PRESIDENT'OR JOB='MANAGER'GROUPby JOB
数据库优化方案
1. 利用表分区
分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
2. 别名的使用
别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快1.5倍。
3. 索引Index的优化设计
索引可以大大加快数据库的查询速度，索引把表中的逻辑值映射到安全的RowID，因此索引能进行快速定位数据的物理地址。对一个建有索引的大型表的查询时，索引数据可能会用完所有的数据块缓存空间，ORACLE不得不频繁地进行磁盘读写来获取数据，因此在对一个大型表进行分区之后，可以根据相应的分区建立分区索引。但是个人觉得不是所有的表都需要建立索引，只针对大数据量的表建立索引。
缺点： 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
www.2cto.com
索引需要维护：为了维护
系统性能，索引在创建之后，由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块，因此，必须对索引进行维护。
4. 调整硬盘I/O
这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。在磁盘比较富裕的情况下还应该遵循以下原则：
将表和索引分开；
创造用户表空间，与系统表空间（system）分开磁盘；
创建表和索引时指定不同的表空间；
创建回滚段专用的表空间，防止空间竞争影响事务的完成；
创建临时表空间用于排序操作，尽可能的防止数据库碎片存在于多个表空间中。
我们在使用物化视图的过程中基本可以“把它当作一个实际的数据表来看待”，不用再担心视图本身的基础表的效率、优化等
物化视图
1.对于复杂而高消耗的查询，如果使用频繁，应建成物化视图
2.物化视图是一种典型的以空间换时间的性能优化方式
3.对于更新频繁的表慎用物化视图
4.选择合适的刷新方式
一般的视图是虚拟的，而物化视图是实实在在的数据区域，是要占据存储空间的。
当然，物化视图在创建和管理上和一般的视图有不同的地方。相比来讲，物化视图占用了一定的存储空间，另外系统刷新物化视图也需要耗费一定的资源，但是它却换来了效率和灵活性。
减少IO与网络传输次数
1.尽