常用sql函数及高效性能比较

1. truncate table 表A：平时我们都是用的delete来删除表数据，其实并没有删除干净，因为还有表空间及磁盘保留的数据，很占内存，也是为什么你查数据越来越慢的原因之一，所以用 truncate  table 能够干净的删除。

 

2.decode性能比case when 要高些

decode(bill_state,0,'暂存',1,'保存',2,'审批中',3,'审批通过',4,'驳回','已关闭') bill_state, 

 

3.可直接编译成中文：查询结果是星期四

select * from to_char(to_data('2017-10-21',yyyy-mm-dd)) from dual

 

4.union 查询结果进行拼接（除重），union all效率比union要高

 

5.尽量少用in,因为in会进行全表扫描效率低

 

6.查询去重select distinct,name　from  tableA

 

7.instr(a,b) a中是否包含了b

NVL(a,b)a为空的时候为b，否则a

 

8.nvl(e.EMPSTATUS,'xx')<> 'leave'//能够查出满足条件及为空的数据：

e.EMPSTATUS：字段；'xx'：自定义；'leave'：需要查询的条件

 

9.start with connect by prior:递归查询

start with：遍历起始条件

connect by prior：连接条件

 

例子：可用于查询部门所在的上级机构

select t.orgid,t.orgcode,t.orgname,t.parentorgid

 

from org_organization t
start with t.orgid = 1019

 

connect by prior t.parentorgid = t.orgid

10.模糊查询效率很低：

原因：like本身效率就比较低，应该尽量避免查询条件使用like;对于like ‘%…%’(全模糊)这样的条件，是无法使用索引的，全表扫描自然效率很低;另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。

解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的;左模糊like 
‘%…’无法直接使用索引，但可以利用reverse + function index 的形式，变化成 like ‘…%’;全模糊是无法优化的，一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。
查询条件中含有is null的select语句执行慢

原因：Oracle 9i中，查询字段is null时单索引失效，引起全表扫描。

解决方法：SQL语法中使用NULL会有很多麻烦，最好索引列都是NOT NULL的;对于is null，可以建立组合索引，nvl(字段,0),对表和索引analyse后，is null查询时可以重新启用索引查找,但是效率还不是值得肯定;is not null 时永远不会使用索引。一般数据量大的表不要用is null查询。
select count(*) from table;这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

11查询条件中使用了不等于操作符(<>、!=)的select语句执行慢

原因：SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描。例如，把column<>’aaa’，改成column<’aaa’ or column>’aaa’，就可以使用索引了。