sql 少用系列+推荐系列（oracle）【持续积累】

少用系列

1.From子句中写在最后的表（driving table基础表（就是谁写在前面））将被最先处理，所以选个记录条数最少的表作为基础表。

Eg: tab1有N条数据

   tab2有10条数据

select count(*) from tab1,tab2 ………….//不佳

select count(*) from tab2,tab1 …………//最佳

2.不要把交叉表放后面。交叉表放前面，即from的第一个表最好是交叉表

3.Where条件中不要把表间的连接放后面

表之间的连接必须写在其他where条件之前。

可以过滤掉最大数据记录的条件必须写在where子句的末尾

4.如果表A和B存在 多对一 或者 一对一 的关系如下：

//高效

Select A.* from A,B where A.city=B.city

//低效

Select * from A where A.city in (select B.city from B)

5.全表删除用truncate代替delete

6.不用*代替所有列名，因为oracle中会查询数据字典把*转变为表的所有列名

7.少用动态语句、like、commit、order by、视图嵌套（理论上可嵌套16层，但是3层就会使性能下降的非常严重）

8.定义trunc(sysdate)时尽量在声明的时候用，同时不推荐使用sysdate

9.Mod的开销很大

10.使用不等于操作符（<> 、 != ）

下面的查询即使在cust_rating 列有一个索引，查询语句仍然执行一次全表扫描。

select cust_Id,cust_name from customers where  cust_rating <> 'aa';

把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa'; 

  特别注意：通过把不等于操作符改成 OR 条件，就可以使用索引，以避免全表扫描。 

11.使用 IS NULL  或 IS NOT NULL 

使用 IS NULL  或 IS NOT NULL 同样会限制索引的使用 。因为 NULL 值并没有被定义。在 SQL 语句中使用 NULL 会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成  NOT NULL 。如果被索引的列在某些行中存在 NULL 值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。 

12.使用函数 

   如果不使用基于函数的索引，那么在SQL 语句的 WHERE 子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 

  下面的查询不会使用索引（只要它不是基于函数的索引） 

 select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81'; 

   把上面的语句改成下面的语句，这样就可以通过索引进行查找。 

select empno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);

13.比较不匹配的数据类型        

注意下面查询的例子，account_number 是一个 VARCHAR2 类型 , 在 account_number 字段上有索引。

下面的语句将执行全表扫描 ：

 select bank_name,address,city,state,zip from banks where account_number = 990354; 

  Oracle可以自动把 where 子句变成 to_number(account_number)=990354 ，这样就限制了索引 的使用 , 改成下面的查询就可以使用索引： 

 select bank_name,address,city,state,zip from banks where account_number ='990354';

特别注意：不匹配的数据类型之间比较会让Oracle 自动限制索引的使用 , 即便对这个查询执行 Explain Plan 也不能让您明白为什么做了一次 “ 全表扫描 ” 。

14.union操作符

union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表union。如：

select * from gc_dfys

union

select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用union ALL操作符替代union，因为union ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys

union all

select * from ls_jg_dfys

15.采用函数处理的字段不能利用索引，如：

substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’ 

trunc(sk_rq)=trunc(sysdate)，优化处理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)

进行了显式或隐式的运算的字段不能进行索引，如：

   ss_df+20>50，优化处理：ss_df>30

   ‘X’||hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’ 

   sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5

   hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’，注：此条件对hbs_bh 进行隐式的

   to_number转换，因为hbs_bh字段是字符型。 

   条件内包括了多个本表的字段运算时不能进行索引，如：

   ys_df>cx_df，无法进行优化 

   qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’

16.

 

推荐使用

1.推荐使用decode函数，oracle所有版本都支持，同时decode比if和then高效

Eg：select DECODE(command, 0, ‘none’, 2, ‘insert’, 3, ‘select’, 6, ‘update’, 7, ‘delete’, 8, ‘drop’, ‘other’)command from …………………..

2.Oracle里的rowid可立即确定行当位置， ROWID为该表行的唯一标识，是一个伪列，可以用在SELECT中，但不可以用INSERT, UPDATE来修改该值。如Rowid可以作为判断条件与用户id相等

3.避免定义的变量类型与表中列的类型不一样，造成转换问题。同时不要定义了变量又不去使用。新变量类型：用BINARY_DOUBLE/BINARY_FLOAT代替Number型

4.自制临时表。可能因为关联表的数据分散冗杂致使效率低下，所以将关联的东东创建到一个新的表中存储在新表空间里，用这个临时表进行关联查询。

5.中间结果中会话中使用时，不要采用永久表，自己搞个临时表就行了

6.推荐结果不大的时候，用exits而不用in，in返回的是结果集，而exists返回的是布尔值。

7.当子查询结果较大时，用minus代替not in/ NOT exists 

//见笔记（点击打开）

8.用instr代替like           Eg: instr(字段名/列名, ‘查找字段’)

有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要否则不要在关键词前加%

Eg: Select * From employee Where instr(last_name,'linton')>0;

9.在进行多表关联时，多用 Where 语句把单个表的结果集最小化，多用聚合函数汇总结果集后再与其它表做关联，以使结果集数据量最小化

10.在两张表进行关联时，应考虑可否使用左/右连接。以提高查询速度

11.使用 where 而不是 having ，where是用于过滤行的，而having是用来过滤组的，因为行被分组后，having 才能过滤组，所以尽量用户 WHERE 过滤