NVL(eExpression1, eExpression2)
如果 eExpression1 的计算结果为 null 值,则 NVL( ) 返回 eExpression2。
如果 eExpression1 的计算结果不是 null 值,则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。
如果 eExpression1 与 eExpression2 的结果皆为 null 值,则 NVL( ) 返回 .NULL.。
DECODE(DEPT_NO,0020,’X’,NULL):如果dept_no为0020,则返回'X',否则返回NULL
ORACLE SQL性能优化:
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理.
表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
EMP表描述了LOCATION表和CATEGORY表的交集.()
SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN
将比下列SQL更有效率
SELECT * FROM EMP E ,LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
(低效,执行时间156.3秒)
SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
(高效,执行时间10.6秒)
SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) and SAL > 50000 AND JOB = ‘MANAGER’;
SELECT子句中避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,
这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名,
这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
减少访问数据库的次数
当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等.
由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.
例如: 检索出雇员号等于0342或0291的职员.
(最低效)
SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291;
(高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342 AND B.EMP_NO = 291;
使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
例如:
SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%’;
可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
DECODE(DEPT_NO,0020,’X’,NULL):如果dept_no为0020,则返回'X',否则返回NULL
最高效的删除重复记录方法
DELETE FROM EMP E WHERE E.ROWID >
(SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
用TRUNCATE替代DELETE
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息.
如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态
而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短
要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费
计算记录条数
count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(主键)
用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤.
这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销
减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.
低效:
SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
高效:
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
Update 多个Column 例子:
UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;
使用表的别名(Alias)
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.
这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率
低效:
SELECT * FROM EMP (基础表)WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
高效:
SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
NOT EXISTS替代NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的
(因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
SELECT … FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’);
SELECT …. FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’(left outer join)
SELECT …. FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
用表连接替换EXISTS
通常来说 , 采用表连接的方式比EXISTS更有效率
SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
(更高效)
SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = ‘A’
用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换
低效:
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);
如一个SQL在A程序员写的为
Select * from zl_yhjbqk
B程序员写的为
Select * from dlyx.zl_yhjbqk(带表所有者的前缀)
C程序员写的为
Select * from DLYX.ZLYHJBQK(大写表名)
D程序员写的为
Select * from DLYX.ZLYHJBQK(中间多了空格)
四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,
可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次,
共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。
进行了显式或隐式的运算的字段不能进行索引
分享到:
相关推荐
SQLServer和Oracle常用函数对比SQLServer和Oracle常用函数对比
Oracle_Sql语句资料oracle+110个常用函数经典SQL语句大全,可供学习参考。
Oracle基本函数,Oracle常用经典SQL查询,有利于入门学习及供日常的使用。
SQLServer和Oracle常用函数对比.txtSQLServer和Oracle常用函数对比.txtSQLServer和Oracle常用函数对比.txtSQLServer和Oracle常用函数对比.txt
sql,mysql,oracle常用的函数
Oracle SQL 内置函数大全 SQL中的单记录函数 给出整数,返回对应的字符 连接两个字符串 增加或减去月份 用于对查询到的结果进行排序输出
hive和oracle常用函数对照,包含常用的函数分类 字符函数 数值函数 日期函数 聚合函数 转换函数 其他 增加的hive函数对比,只需要2个积分喔
sqlserver常用函数 SQLServer和Oracle常用函数对比和常用函数的帮助说.
SQL SEVER与ORACLE常用函数比较
oracle数据库常用函数大全,oracle数据库常用函数大全
ORACLE常用函数总结,与SQL SERVER对比,防止混淆,更容易记忆。
本文档详细介绍了SQL Server和Oracle常用函数区别,其中S与O分别为SQL Server与Oracle的缩写。对于初学者很有用,经典。
下面是 Oracle Sql 中常用的字符串处理函数: 1. 大小写转换函数 Oracle Sql 提供了两个大小写转换函数:UPPER() 和 LOWER()。UPPER() 函数将字符串转换为大写,而 LOWER() 函数将字符串转换为小写。 示例:...
非常全面的ORACLE SQL性能优化介绍及实例:优化器的选择、共享SQL语句、访问Table的方式、减少访问数据库的次数、使用DECODE函数、减少对表的查询、通过内部函数提高SQL效率、用NOT EXISTS替代NOT IN、使用EXPLAIN ...
Oracle数据库常用dos命令,sql语句分类介绍,常用sql语句,oracle函数大全。oracle创建表空间,创建用户并授权。
主要为sql server和oracle中常用函数的比较
SQLServer和Oracle的常用函数对比